Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

Take care while using SELECT Clause for Variable Assignment in SQL Server

Aug 10 2011 12:00AM by Paresh Prajapati   

I ran through one issue while working in one of the stored procedure and using SELECT clause while variable assignment. And i suffered this issue in live production environment and due to this it was killing the Server CPU.

Finally i got the issue and resolved it. The issue was It did not take care of variable assignment with SELECT clause. In my earlier post i have written about SET vs SELECT for variable assignment and I discussed the same issue with overview but not given details here, http://beyondrelational.com/blogs/paresh/archive/2011/04/13/variable-assignment-using-set-and-select-in-sql-server.aspx

Here i am going complete the details and share the issue with you with examples.
CREATE TABLE FromTab
  (
     Id    INT
     ,Name VARCHAR(50)
  )

GO

CREATE TABLE ToTab
  (
     Id    INT
     ,Name VARCHAR(50)
  )

GO

-- Inserting demo records 
INSERT FromTab
SELECT
  1  ,'First'
UNION ALL
SELECT
  2  ,'Second'
UNION ALL
SELECT
  3  ,'Third'
UNION ALL
SELECT
  4  ,'Fourth'
UNION ALL
SELECT
  5  ,'Fifth'

GO
As per discussed we are using SELECT clause for variable assignment here in below script.
DECLARE @Var INT
DECLARE @Startdate DATETIME

SET @Startdate = Getdate() 

SELECT TOP 1
  @Var = Id
FROM   FromTab

WHILE EXISTS(SELECT
               1
             FROM   FromTab
             WHERE  Id <= @Var)
  BEGIN
 
 PRINT CONVERT(VARCHAR,DATEDIFF(SECOND,@Startdate,GETDATE())) + ' Seconds'
    
      INSERT INTO ToTab
      SELECT
        Id
        ,Name
      FROM   FromTab
      WHERE  Id = @Var

      SELECT TOP 1
        @Var = Id
      FROM   FromTab
      WHERE  Id > @Var
  END

GO


Now toward the solution I am going to use SET for variable assignment in below revised script.
-- First truncate table before go ahead with next
TRUNCATE TABLE ToTab

DECLARE @Var INT
DECLARE @Startdate DATETIME

SET @Startdate = Getdate() 


SELECT TOP 1
  @Var = Id
FROM   FromTab

WHILE EXISTS(SELECT
               1
             FROM   FromTab
             WHERE  Id <= @Var)
  BEGIN
  
   PRINT CONVERT(VARCHAR,DATEDIFF(SECOND,@Startdate,GETDATE())) + ' Seconds'
   
      INSERT INTO ToTab
      SELECT
        Id
        ,Name
      FROM   FromTab
      WHERE  Id = @Var

-- Comment start

      --SELECT TOP 1
      --  @Var = Id
      --FROM   FromTab
      --WHERE  Id > @Var
      
-- Comment end

-- Added code and used SET
      
      SET @Var = (SELECT TOP 1
                    Id
                  FROM   FromTab
                  WHERE  Id > @Var)
                  
-- End Adding
                  
  END

GO 


What will be your comment?

Tags: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, t-sql, SET, #SQL Server, sql server 2011, database, sql server general, SQL Scripts, sql errors, SQL Server Problems,


Paresh Prajapati
6 · 23% · 7485
5 Readers Liked this
Kirti M.Darji Liked this on 8/11/2011 4:51:00 AM
Profile · Blog · Facebook · Twitter
Paresh Prajapati Liked this on 8/23/2011 1:01:00 AM
Profile · Blog · Facebook · Twitter
Sandeep Prajapati Liked this on 8/23/2011 1:13:00 AM
Profile · Blog · Facebook · Twitter
Nirav Liked this on 3/28/2012 12:46:00 AM
Profile · Blog · Facebook · Twitter
kavan dhruv Liked this on 11/12/2012 4:28:00 AM
Profile · Facebook · Twitter
5
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • Ok, IMHO, the problem here is the use of WHILE EXISTS. Using SET has more serious problems. You should always use SELECT whenever you can, instead of SET.

    If you're using SET to assign a value to a variable, and the SQL fails, the variable retains the previous value. NOT good! If you use SELECT, at least NULL will be assigned to the variable. FAR better.

    --Quick and dirty example: DECLARE @t varChar(25); SET @t = 'Test 1';

    --Some work here...

    SET @t = 'Test 2' + ' ' + SELECT TOP 1 SomeSQLThatFails FROM someServer.dbo.SomeTable;

    SELECT @t; -- THIS RETURNS 'Test 1' IF the SQL in your SET statement above fails.

    SELECT @t = 'Test 2' + ' ' + SELECT TOP 1 SomeSQLThatFails FROM someServer.dbo.SomeTable;

    SELECT @t; -- THIS returns NULL. Better NULL than some incorrect value.

    --The SQL Whisperer

    commented on Aug 10 2011 3:22PM
    SQL Whisperer
    2227 · 0% · 6
  • I would use @@ROWCOUNT, and include an order BY for the select top 1 :

    TRUNCATE TABLE ToTab;
    
    DECLARE @Var INT;
    DECLARE @Startdate DATETIME;
    
    SET @Startdate = Getdate();
    
    SELECT TOP 1 @Var = Id FROM FromTab ORDER BY Id;
    WHILE @@ROWCOUNT = 1
    BEGIN
       PRINT CAST(@Var AS VARCHAR) + ' : ' + CONVERT(VARCHAR,DATEDIFF(SECOND,@Startdate,GETDATE())) + ' Seconds';
    
       INSERT INTO ToTab (Id,Name) SELECT Id,Name FROM FromTab WHERE Id = @Var;
    
       SELECT TOP 1 @Var = Id FROM FromTab WHERE Id > @Var ORDER BY Id;
    END;
    GO
    
    commented on Aug 24 2011 2:27AM
    Bert
    57 · 3% · 1038
  • There are two very basic coding errors in your example: 1. The loop has a WHILE condition that will never be satisfied, so loops infinitely. 2. The TOP operator is used without an ORDER BY clause, so the start condition is not defined.

    Your WHILE loop has the end condition

    WHILE EXISTS (SELECT
               1
             FROM   FromTab
             WHERE  Id <= @Var
    

    The variable @Var starts by being initialised with a Id value from the FromTab table. Each iteration of the loop sets @Var to a higher value, so there will always exist an Id less than @Var

    This is just a very basic logic error. The end condition should be

    EXISTS(SELECT
               1
             FROM   FromTab
             WHERE  Id > @Var)
    

    which does complete after (usually) four iterations.

    commented on Aug 24 2011 4:13AM
    paul.ramster
    565 · 0% · 66

Your Comment


Sign Up or Login to post a comment.

"Take care while using SELECT Clause for Variable Assignment in SQL Server" rated 5 out of 5 by 5 readers
Take care while using SELECT Clause for Variable Assignment in SQL Server , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]