variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

variables

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2002-07-02 : 11:43:19

Keep in mind I'm a beginner....

If I have the following select statement, how do I throw the resulting field into a variable and then use the variable in another query?

"select distinct @@IDENTITY from TestResults"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 11:48:29


declare @localvariablename int
select @localvariablename = @@identity

A couple things to keep in mind here. 1) the distinct and from clause in your query will do nothing because 2)@@identity is a global system variable that contains the last-inserted identity value. It doesn't care which table it was inserted into and there is only one value.

quote:

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
--SQL Server 7, Books OnLine

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 11:52:03

Depends actually.. if your query returns multiple values, put it into a temp table. If your just returning a single value then...

--declare your variable
declare @tempvar varchar(52)
--populate var
select @tempvar = distinct @@IDENTITY from TestResults
--and then your query with the var
select * from anothertable where id = @tempvar

Set @tempvar = distinct @@IDENTITY from TestResults
Set instead of select also works. Kinda curious if anyone knows... Whats the difference between select and set?

-----------------------
Take my advice, I dare ya

Edited by - M.e. on 07/02/2002 11:52:53

mikejohnson
Posting Yak Master

153 Posts

Posted - 2002-07-02 : 11:55:59

sweet, that worked great. thanks for the help.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 12:07:43

quote:

Set @tempvar = distinct @@IDENTITY from TestResults

This shouldn't work (and doesn't on my box). If you look at the syntax for set @local_variable, you will see there is no FROM clause defined. Everything SET can do can also be done with SELECT.

<O>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-02 : 17:18:12

For single variable assignment there is no difference in the fuctionality of set and select @variable.
Set can only be used to assign one variable at a time
e.g.

declare @a int,@b int

set @a=1
set @b=2

Select can assign multiple variables in the same statement
e.g.

declare @a int,@b int
select @a=1,@b=2

This is especialy important in the example of getting error and rowcounts after an update,delete or insert otherwise one or either will be useless if set separately.
I think its a matter of preference whether to use select for single variable assignment . My preference is to use set for single variable assignment exclusively - don't know why it just sounds right

HTH
Jasper Smith

Edited by - jasper_smith on 07/02/2002 17:19:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources