SQL syntax

I have a few variables declared, what I need is to set the variables to some values from a single select from a temp table.

example
declare
@Action varchar(30)
, @CurrentUserID varchar(40)
,@ID int
,@VersionNumber
 
I would like to do something like this
 SELECT @Action , @CurrentUserID = OpsAction, OpsUser from #tmpGroup where ID= @ID and VersionNumber = @CurrentVersionNo

Is there a way to set multiple values from a single select
I know this works
SELECT @Action = OpsAction from #tmpGroup where ID= @ID and VersionNumber = @CurrentVersionNo

but want to avoid repeating the select
LVL 1
countrymeisterAsked:
Who is Participating?
 
Sreedhar VengalaConnect With a Mentor Sr. Consultant - Business IntelligenceCommented:
Try something like:
DECLARE @Var1 int
DECLARE @Var1 varchar(20)

-- This line assigns column values to variables
Select @Var1=Col1, @Var2=Col2, from <TableName> where <your condition>)

Select @Var1, @Var2  -- This line prints the values of your variables.
0
 
Pratima PharandeCommented:
Yes it is possible
you can set multiple values from a single select like below
change this as per your requirment @CurrentUserID = OpsAction

SELECT @Action= OpsAction , @CurrentUserID = OpsAction, OpsUser from #tmpGroup where ID= @ID and VersionNumber = @CurrentVersionNo
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Example:

 
 

Declare @TID int 
Declare @SID int 
Select @TID = TransactionID, @SID = StockpileID from Transactions where TransactionID = 41285 
 
Select @TID, @SID 

Open in new window

0
 
Pratima PharandeCommented:
sorry sree_ven , I havn't refresh the page before posting
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
No worries :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.