T-SQL Snippets

  1. Insert Statement
  2. Update Statement
  3. Select with Join and rowcount
  4. Select with Join and Case Statement
  5. Simple Stored Procedure
  6. EXISTS Keyword - If Select True - Do Update/Insert
  7. Cursor
  8. While Loop
  9. Transactions
  10. Difference between Inner and Outer Join
  11. Database Normalisation
    1. Insert Statement 

      insert into tblNAME (id, name, description, size) values (201, 'a name','a description', 69)
    2. Update Statement 

      UPDATE     tblNAME 
SET        Name         = 'another name'           ,
           description  = 'another description' 
WHERE      ID = 201
    3. Select With Inner Join and rowcount

      DECLARE @rc int

SELECT    {fields}
FROM      tblMessages 
          INNER JOIN tblForums ON tblForums.ForumID = tblMessages.ForumID 
WHERE     tblForums.Visible = 1 AND
         (tblMessages.Subject Like '%searchText%' OR tblMessages.Message Like '%searchText%') AND
         CDate(tblMessages.DateOfPost) < '#" & Date() - 31 & "#'
ORDER BY tblMessages.DateOfPost DESC  

SELECT @rc=@@rowcount
SELECT @rc
    4. Simple Stored Procedure 

      
/*  SET these server options before compiling any sproc */

SET ANSI_WARNINGS OFF
SET ANSI_NULLS ON
SET ANSI_PADDING OFF
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULL_DFLT_ON OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON

IF EXISTS (SELECT Name FROM sysobjects WHERE Name="simpleStoredProc" AND Type="P")
    DROP PROCEDURE simpleStoredProc
GO

CREATE PROCEDURE simpleStoredProc
@New int,
@ID int,
@Name varchar(50),
AS

-- Do we want to create or update
IF (@New = 1)
BEGIN
   Insert into tblName Values(@ID,@Name)
   GOTO Cleanup
END

Update tblName
Set 	Name=@Name,
Where ID= @ID

Cleanup:
RETURN 0
 
GO
GRANT EXECUTE ON simpleStoredProc TO PUBLIC
GO
    5. EXISTS Keyword - If Select is True do update/insert

      Dim db, rs, sSQL  username = "Steve"  password = "1234"    sSQL = "IF EXISTS(SELECT 'True' FROM MyTable WHERE username = '" & username & "') "  sSQL = sSQL & "BEGIN "  sSQL = sSQL & "SELECT 'This record already exists!' "  sSQL = sSQL & "END ELSE BEGIN "  sSQL = sSQL & "SELECT 'Record Added' "  sSQL = sSQL & "INSERT INTO MyTable(username, userpassword) VALUES('" & _    username & "','" & password & "') " sSQL = sSQL & "END" Set db = Server.CreateObject("ADODB.Connection") db.Open myTest 'use your connection here 'And execute our statement Set rs = db.Execute(sSQL) If rs(0) = "This record already exists!" Then 'We can either redirect back to the page and 'tell the user to try again or write something out to the page Else    Response.Write "Your user name and password has been accepted." End If
    6. Cursor 

      DECLARE @iPid int
DECLARE @iSid int
DECLARE @iLid int

DECLARE SNCursor Cursor STATIC FOR
    OR
DECLARE SNCursor Cursor FOR					-- this would be dynamic and you cant get cursor_rows
SELECT     x,y,z   
FROM       tblXX      

OPEN SNCursor
FETCH NEXT FROM SNCursor INTO @iPid,@iSid,@iLid
SELECT @cc=@@CURSOR_ROWS

WHILE @@fetch_status = 0
BEGIN

    -- -------------------------
    -- Place Calculations here
    -- -------------------------

    UPDATE  tblXX    
    SET    z=1
    WHERE  x=@iPid AND y = @iSId AND z = @iLId
    -- -------------------------
    -- End Calculations 
    -- -------------------------


    FETCH NEXT FROM SNCursor INTO @iPid,@iSid,@iLid

END

CLOSE SNCursor
DEALLOCATE SNCursor
    7. While Loop 

      declare @vchString  varchar(255)
declare @vchOwnerid varchar(255)
declare @iLength    int
declare @iComma     int
declare @iOwnerid   int

select @vchString = "1,2,3,4,5,6,7,8,9,10"


while ltrim(rtrim(@vchString)) <> ""
begin

    select @iLength    = Len(@vchString)
    select @iComma     = CHARINDEX(",", @vchString)

    if @iComma <> 0
        begin
            select @iOwnerId   = Convert(int,Left(@vchString, @iComma - 1))
            select @vchString  = Right(@vchString, (@iLength - @iComma)) 
         end
    else
        begin
            select @iOwnerId   = Convert(int,@vchString)
            select @vchString  = ""
        end

    select @iOwnerId


continue
end
    8. Transactions 

      
BEGIN TRANSACTION

UPDATE     tblNAME 
SET        Name         = 'another name'           ,
           description  = 'another description' 
WHERE      ID = 201

IF @@error = 0
BEGIN

    COMMIT TRANSACTION
    
END
ELSE
BEGIN

    SELECT @iReturnCode = 1
    ROLLBACK TRANSACTION
    
END
    9. Select with Join and Case Statement 

      SELECT    I.iIncidentId                              ,
          --ED.iSystemId                             ,
          I.vchProductID                             ,
          I.iStatusID                                ,
          --RP.iParameterID                          ,
          RP.vchParameterDesc as 'vchStatus'         ,
          I.iIncidentTypeId                          ,
          --RP2.iParameterID                         ,
          RP2.vchParameterDesc as 'vchIncidentType'  ,  
          ED.chFieldName                             ,         
          vchDataValue = ( Case IsNumeric(ED.vchDataValue) 
                                   WHEN 1 THEN 
                                       ( SELECT vchParameterDesc
                                         FROM   ReferenceParameters 
                                         WHERE  iParameterID = ED.vchDataValue )
                                   ELSE 
                                       ED.vchDataValue
                                   END
                         )                                    
FROM      INCIDENT I
    JOIN ReferenceParameters RP  ON RP.iParameterId=I.iStatusID
    JOIN ReferenceParameters RP2 ON RP2.iParameterId=I.iIncidentTypeId
    JOIN ExpansionData ED ON ED.iSystemId=I.iIncidentId

    
WHERE     I.iOwnerID=100018
     AND  I.iIncidentTypeId > 400123
     AND  I.iIncidentTypeId < 400128
     AND  I.iIncidentcategory = 3
     AND  I.tiRecordStatus = 1
    10. Difference between Inner and Outer Join

      outer join will return a record set even if the join condition isn't reached inner join only returns record set if join condition is reached.

    11. Database Normalisation

      Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency. 



    12. 
    
  
  
  
    




    Hosted by www.Geocities.ws
    

    

    
        
    


    


1