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

Variable assignment using SET and SELECT in SQL Server.

Apr 13 2011 12:00AM by Paresh Prajapati   

I experienced in issue with SELECT while used for variable assignment with in the stored procedures. I have captured that moment and had post for the same. Here i want to give you some demo for the SELECT and SET using with variable assignment. Below are for the same.

Create one table and inserting the values.
CREATE TABLE SampleTable
(
[Id] INT,
[Name] VARCHAR(50)
)
GO

INSERT INTO SampleTable
VALUES(3,'Test3')
GO
...
...
Let's See the table data 

#1
DECLARE @Name VARCHAR(50)
SET @Name = (SELECT [NAME] FROM SampleTable WHERE id=1)
SELECT @Name
GO
While running above query raise error as following . To resolve the issue for above error, we need to use TOP 1.

 
 (Click on image to enlarge)
DECLARE @Name VARCHAR(50)
SET @Name = (SELECT TOP 1 [NAME] FROM SampleTable WHERE [Id]=1 
order by [Id] DESC)
SELECT @Name as [Name]
GO
Above query will return the first row for particular Id. We can also use SELECT clause to get resolve the sane issue Below is the example using Select clause
DECLARE @Name VARCHAR(50)
SELECT @Name = [NAME] FROM SampleTable WHERE id=1 order by [Id] DESC
SELECT @Name as [Name
GO
Above query will not raise any error and apply last one value from among to variable.

#2
Declare @Id int, @Name varchar(50)
Select @Id =1, @Name = 'Using Select'
GO
We can assign values to multiple variable in single statement And can not do it with SET, we must assign value to variable using individual statement
Declare @Id int, @Name varchar(50)
SET @Id =2 
SET @Name = 'Using SET'
GO
#3

Variable assignment using SELECT will retain the old value if next statement does not have value.
DELETE FROM SampleTable -- Deleting records from table
Declare @Id int, @Name varchar(50)
Select @Id =1, @Name = 'Old Value'
SELECT @Id = [Id] ,@Name = [Name] FROM SampleTable
SELECT @Id as [NewIdVal], @Name as [NewNameVal]
GO
Result for the above query
 

Variable assignment using SET will not retain the old value if next statement does not have value , assign NULL value to variable from next statement even it does not have value.
DELETE FROM SampleTable -- Deleting records from table
Declare @Id int, @Name varchar(50)
Select @Id =1, @Name = 'Old Value'
set @Id = (SELECT TOP 1 [ID] FROM SampleTable)
set @Name = (SELECT TOP 1 [Name] FROM SampleTable)
select @Id as [NewIdVal], @Name as [NewNameVal]
GO
Result for the above query 

 

Lastly i suffered from issues when i have used SELECT for the variable assignment and used that variable  with while loop. So when there are no any values in tables to be assigned for variables. At that time execution was gone infinitely and  never end. After I have used SET and it solved my issue. Let me share your experience if you ran into issue with SET or SELECT.

Tags: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, t-sql, variable, SET, mssql,


Paresh Prajapati
6 · 23% · 7444
5 Readers Liked this
Paresh Prajapati Liked this on 8/11/2011 5:08:00 AM
Profile · Blog · Facebook · Twitter
Sandeep Prajapati Liked this on 8/29/2011 1:13:00 AM
Profile · Blog · Facebook · Twitter
Nirav Liked this on 3/28/2012 2:04:00 AM
Profile · Blog · Facebook · Twitter
kavan dhruv Liked this on 11/12/2012 1:26:00 AM
Profile · Facebook · Twitter
Alpesh Patel Liked this on 11/25/2012 10:30:00 PM
Profile
5
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • can you post the bigger image for query error you got with the first query sample, its hardly visible , apart from that clear and concise tutorial.

    Javin

    commented on May 20 2011 10:06AM
    Javin Paul
    1769 · 0% · 11
  • Hello Javin,

    Thanks for your reply.

    I have reuploaded new image for that error. If you are unable to see the image then click on image , it will enlarge.

    commented on May 20 2011 10:45AM
    Paresh Prajapati
    6 · 23% · 7444
  • HI Paresh,

    Its very interesting and can u please share your exact code where u got the error(u said with in the while loop)

    Thanks and advance.

    regards, Bala.

    commented on Nov 26 2012 4:25AM
    Bala Krishna
    83 · 2% · 676

Your Comment


Sign Up or Login to post a comment.

"Variable assignment using SET and SELECT in SQL Server." rated 5 out of 5 by 5 readers
Variable assignment using SET and SELECT 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]