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 124
sql 123
ms sql server 118
ms sql 117
database 108
tsql 80
#SQL Server 78
t-sql 74
#sql 71
sql server general 66

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. 

Continue Reading...

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% · 7533
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
    2256 · 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
    570 · 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]