Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Update Table using JOIN in SQL Server

Update a table value from another Table using JOIN Query in SQL Server

Code :
    UPDATE
        A
    SET
        A.Colx = B.Coly
    FROM
        Table1 A
    JOIN
        Table2 B ON A.col1 = B.col5
    WHERE
        A.Col3 = 'xyz'

Convert Text to XML Data in SQL Server.

Convert Text to XML Data in SQL Server.

In the following Stored Procedure we can Use XML Data as Follows.
Send XML Data to Stored Procedure as "Text" Data Type.
In SP Convert this "Text" (XML Text)  to "XML" Data
Read and Fetch the XML Data into Table.
Use the Cursor to Traverse through Data.

Code :
/**************************************************************************
 
-----------------------  
Developers Note   
-----------------------  
Object          : StoredProcedure [dbo].[canon_UpdateValidUserFromXML]      
Script Date     : 19/11/2012   
Developed By    : Rohit Shram  
Used For        : Update Valid  Users 
Implementaion   :   
    SELECT *  FROM [USER] where ID in ( 80309 ,80310)
    GO
    UpdateValidUserFromXML
    '<UsersList>
        <Users>
            <ID>80309</ID>
            <UserName>KARL TYLER</UserName>
            <Email>CRAIG.WASHINGTON80309@testmail.com</Email>
        </Users>
        <Users>
            <ID>80310</ID>
            <UserName>CHAD MARTIN</UserName>
            <Email>LEWIS.CUNNINGHAM80310@testmail.com</Email>
        </Users>
    </UsersList>',
    'File1234',
    80311
    GO
    SELECT *  FROM [USER] where ID in ( 80309 ,80310)

*************************************************************************************/  

CREATE PROCEDURE [dbo].[UpdateValidUserFromXML] 

    @UsersXml TEXT  
--'<UsersList><Users><ID>12</ID><UserName>KAYR</UserName><Email>CRAI@test.com</Email></Users></UsersList>' 
AS  
BEGIN  
    SET NOCOUNT ON;  
    
    BEGIN TRANSACTION Update
    
    BEGIN TRY
    
    DECLARE @idoc INT
    DECLARE @ID INT                  
    DECLARE @UserName NVARCHAR(500)                
    DECLARE @Email NVARCHAR(500)                
 
    -- Used to Keep ErrorList for all Users Data as Table
    DECLARE @UsersErrorList TABLE                  
    (                  
        ID INT,                 
        Name NVARCHAR(500),                
        Email NVARCHAR(500),                 
        Error NVARCHAR(MAX)       
    ) 
 
    -- Used to Keep XML Data as Table
    DECLARE @UsersList TABLE                  
    (                  
        ID INT,                 
        UserName NVARCHAR(500),                
        Email NVARCHAR(500)                
    ) 

    -- Prepare XML Doc
    EXEC sp_xml_preparedocument @idoc OUTPUT, @UsersXml                  
          
    -- Insert into Table Variable From XML
    INSERT @UsersList                
    (                 
        ID,                
        UserName,                 
        Email                
    )                  
    SELECT                   
        ID,                
        UserName,                 
        Email               
       
    FROM                  
    OPENXML (@idoc, '/UsersList/Users')                  
    WITH                 
    (                  
        ID INT 'ID',                 
        UserName NVARCHAR(500) 'UserName',                
        Email NVARCHAR(500) 'Email'               
    )                  
           
    -- Remove XML Do after getting Data
    EXEC sp_xml_removedocument @idoc           
   
    -- Declare Cursor
    DECLARE Cursor CURSOR FOR                  
    SELECT                  
    ID,                
    UserName,                 
    Email                
    FROM @UsersList                   
         
    -- Open Cursor
    OPEN Cursor                  
         
    -- Fetch Cursor
    FETCH NEXT FROM Cursor INTO                   
    @ID,                  
    @UserName,                  
    @Email       
         
    -- Traverse through Data                
    WHILE @@FETCH_STATUS = 0                
    BEGIN  
  
        -- Update User's Name and Email    
        UPDATE [USER] 
        SET Email = @Email, UserName = @UserName 
        WHERE ID = @ID 
  
        -- Fetch Cursor
        FETCH NEXT FROM Cursor INTO                   
        @ID,                  
        @UserName,                  
        @Email                 
                  
    END  -- WHILE @@FETCH_STATUS = 0                
                 
        CLOSE Cursor                  
        DEALLOCATE Cursor 
 
    END TRY
    BEGIN CATCH

        -- Rollback Transaction If any Transaction Occured
        IF @@ROWCOUNT > 0
            ROLLBACK TRANSACTION Update
  
        -- Insert Into Temp table to Keep Error Details for all Users
        INSERT INTO @UsersErrorList
        VALUES ( @ID , @UserName , @Email , ERROR_MESSAGE())

    END CATCH
 
    -- Returns Error ResultSet
    SELECT * FROM @UsersErrorList;  

    -- Commit Transaction If any Transaction Occured
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION Update
    
    SET NOCOUNT OFF;  

END  
   

Use Try...Catch Block and Transactions in SQL Server

Use Try...Catch Block and Transactions in Stored Procedure as Followes :

Code :
/******************************************************************************    
-----------------------  
Developers Note   
-----------------------  
Object          : StoredProcedure [dbo].[sp_SetUsersDetails]     
Script Date     : 19/11/2012   
Developed By    : Rohan Sharma
Used For        : Add/Update User
Implementaion   :   

    sp_SetUsersDetails 5, 'Rohan Sharma', 'Block-B', '87867675765'

*****************************************************************************/ 
 
CREATE PROCEDURE [dbo].[sp_SetUsersDetails] 
    -- Parameters Here
    @ID   INT, 
    @Name  NVARCHAR(500), 
    @Address NVARCHAR(500) = NULL, 
    @Phone  NVARCHAR(500) = NULL, 
AS  
BEGIN  

    SET NOCOUNT ON;  

    BEGIN TRANSACTION UpdateLMS

    BEGIN TRY

        -- Put Your Tansaction DB Codes Here

    END TRY
    BEGIN CATCH

        -- Rollback Transaction If any Transaction Occured
        IF @@ROWCOUNT > 0
            ROLLBACK TRANSACTION UpdateLMS

        -- Put Any ErrorLog Codes Here

    END CATCH

    -- Commit Transaction If any Transaction Occured

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION UpdateLMS

    SET NOCOUNT OFF;  

END  
   

Take Backup with Data of single Table in SQL Server

Create Backup table Instantly :

The Following query automatically creates a Table with name "Table_Backup" and Insert all the rows of "Table_Old" into "Table_Backup"

Example :
    SELECT *  INTO  Table_Backup FROM Table_Old

SET Vs. SELECT in SQL Server

Introduction

SET and SELECT both key words are used to Assign Variables in SQL Server.

SET and SELECT both specifies the columns to be changed and the new values for the columns.
The values in the specified columns are updated with the values specified in the SET and SELECT in all rows that match the WHERE clause search condition.
If no WHERE clause is specified, all rows are updated.

There are some difference based on the Performance, Process like Follows :

1. SET is the ANSI standard for variable assignment, SELECT is not.
2. SELECT can be used to assign values to more than one variable at a time, Whereas SET allows to assign data to only one variable at a time.

Example :
    /* Declaring variables */
    DECLARE @Var1 AS int, @Var2 AS int

    /* The same can be done using SET, but two SET statements are needed */
    SET @Var1 = 1
    SET @Var2 = 2

    /* Initializing two variables at once */
    SELECT @Var1 = 1, @Var2 = 2

But use SET instead SELECT, for variable initialization, It will throw the following error

Example :
    SET @Var1 = 1, @Var2 = 2

    Msg 102, Level 15, State 1, Line 10
    Incorrect syntax near ','.

3. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all .so the variable will not be changed from it's previous value.

Example :

Run it in master Database in SQL Server.
    /* Returns NULL */
    DECLARE @Title varchar(80)
    --SET @Title = 'Not Found'

    SET @Title =
    (
    SELECT error
    FROM SysMessages
    WHERE Description = 'Invalid Description'
    )

    SELECT @Title
    GO

    /* Returns the string literal 'Not Found' */
    DECLARE @Title varchar(80)
    SET @Title = 'Not Found'

    SELECT @Title = error
    FROM SysMessages
    WHERE Description = 'Invalid Description'

    SELECT @Title
    GO

4. Let using a query needs to populate a variable and the Query returns more than one value.
SET will fail with an error in this scenario.
But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

As a result, bugs in your the could go unnoticed with SELECT, and this type of bugs is hard to track down too.

Example :
    /* Consider the following table with two rows */
    SET NOCOUNT ON
    CREATE TABLE #Table (i int, j varchar(10))
    INSERT INTO #Table (i, j) VALUES (1, 'Sunday')
    INSERT INTO #Table (i, j) VALUES (1, 'Monday')
    GO

    /* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
    you will never know that two rows existed for the condition, WHERE i = 1 */
    DECLARE @j varchar(10)
    SELECT @j = j FROM #Table WHERE i = 1
    SELECT @j
    GO

    /* If you rewrite the same query, but use SET instead, for variable initialization, you will see the following error */
    DECLARE @j varchar(10)
    SET @j = (SELECT j FROM #Table WHERE i = 1)
    SELECT @j

    Msg 512, Level 16, State 1, Line 4
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.

Based on the above results, when using a query to populate variables, we should always use SET.
If you want to be sure that only one row is returned then only use SELECT, as shown below:

Example :
    DECLARE @j varchar(10)
    SELECT @j = (SELECT j FROM #Table WHERE i = 1)
    SELECT @j

5. This very feature of SELECT makes it a winner over SET, when assigning values to multiple variables. A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables.
In this scenario, using a SELECT is at least twice as fast, compared to SET.

So, the conclusion is, if there is a loop in th stored procedure that manipulates the values of several variables, and if you want to squeeze as much performance as possible out of this loop, then do all variable manipulations in one single SELECT statement or group the related variables into few SELECT statements as show below:

Example :
    SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 - 1, @CNT = @CNT + 1

To check a primary key exists or not in table

Use the following Query to check whether the Table does have "Primary Key" or not.

Lets assume its Employee Table here. the Query is as Follows :

Example :
    IF OBJECTPROPERTY( OBJECT_ID( '[dbo].[Employee]' ), 'TableHasPrimaryKey' ) = 1
        PRINT '[dbo].[Employee] table has a primary key.'
    ELSE
        PRINT '[dbo].[Employee] table has no primary key.'

Gadget

This content is not yet available over encrypted connections.