question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked MelissaMa-msft commented

Question on variable

Hello!

My textbook says there are two ways to assign a value to a variable: the SET and the SELECT:
96014-q1.png


Nevertheless I see the following example (on this page) that illustrates the third way (EXEC @var...) :

 DECLARE @return_status INT;  
 EXEC @return_status = checkstate '2';  
 SELECT 'Return Status' = @return_status;  
 GO  

Could you imagine any reason why that type of assigning a value to a variable was not mentioned in a book (something like ~that's not the ordinary way to do it, it can only be used for assigning the values from the RETURN and etc...)?

Thank you in advance,
Michael


sql-server-transact-sql
q1.png (92.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MikhailFirsov-1277,

Thanks for your update.

Per my knowledge, if you would like to return the value or result of one procedure, the EXEC/Execute is necessary.

can I assign the return sp value to a variable using only the 3 methods posted above (WITHOUT using OUTPUT parameters)?

So the answer is NO.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I would guess that the author didn't think of it, or decided that it was more pedagogical to keep quiet about this option, which after all is quite a special case. Also, this is a special case: you collect a return status, as a side effect of a procedure call. The other three are exactly that: variable assignments.

There is a fifth way that a variable can be assigned a value. Can you spot it? (Hint: that also includes the EXEC statement.)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @MikhailFirsov-1277,

Thank you so much for posting here in Microsoft Q&A.

Actually, you could assign a value to a variable in the following three ways:

  • During variable declaration using DECLARE keyword.

  • Using SET

  • Using SELECT

Per my understanding, using EXEC variable is more like assigning the return value of a query to a variable (EXEC output) in a stored procedure instead of assigning a value to a variable in the ordinary way. Maybe this little difference was the reason why the author didn't mention the EXEC way in the notebook.

If you find out other ways, provide more details about it and we could discuss together.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoeCelko-6699 avatar image
0 Votes"
JoeCelko-6699 Suspended answered ErlandSommarskog commented

The use of the set syntax is ANSI/ISO standard SQL and you should use it if you want your code to be readable and portable. The use of the select is the old Sybase syntax. In terms of the language, it makes no sense. But it was a convention 50 years ago with the first Sybase compiler. I would hope is being deprecated and that's why nobody thought to mention it in a guide is supposed to be teaching people how to write decent SQL.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The use of the select is the old Sybase syntax. In terms of the language, it makes no sense.

It makes sense so far that this is the only way you can assign multiple variables in a single statement in T-SQL, since SQL Server does not support row constructors.

And as long the SELECT statement returns a single value, it works without issues. When the SELECT returns multiple rows, it's indeterministic which value that is assigned to the variable. But the really devilish is then the SELECT returns no rows at all: this leaves the variable unchanged, which can cause some surprises if you are not prepared for it.

0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered MikhailFirsov-1277 commented

Hello,

Thank you so much for your replies!!!

"I would hope is being deprecated and that's why nobody thought to mention it in a guide is supposed to be teaching people how to write decent SQL." - but the Select = @var is mentioned in the textbook, it is the 'EXEC @var = sp...' that is not !

"The use of the set syntax is ANSI/ISO standard SQL and you should use it if you want your code to be readable and portable" - I also prefer using the SET method!


"There is a fifth way that a variable can be assigned a value. Can you spot it? (Hint: that also includes the EXEC statement.)" - I can just suppose this is the using of the output parameters in the stored procedures (spname @param1, @param2 OUTPUT).

"or decided that it was more pedagogical to keep quiet about this option, which after all is quite a special case." - oh yes, that was a great pedagogical technique: to illustrate the 2 ways to assign a value (3 with the DECLARE) and post the link to the page (right above the picture I've posted above) that describes the totally different way :)))

"Maybe this little difference was the reason why the author didn't mention the EXEC way in the notebook." - yes, maybe that was the reason, but the problem is that this chapter - along with the lesson on variables - contains information about using the Control-of-Flow Language and the RETURN keyword in particular, but does not explain how to receive the value of the RETURN and if I hadn't spotted the example with the 'EXEC @return_status = checkstate '2'; ' I wouldn't have known how I could get that return value because - as MelissaMa-msft and my textbook have mentioned - only 3 methods exist and there is no EXEC @var ... method, so my question can be reformulated in the following way: can I assign the return sp value to a variable using only the 3 methods posted above (WITHOUT using OUTPUT parameters)?

Regards,
Michael


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

"There is a fifth way that a variable can be assigned a value. Can you spot it? (Hint: that also includes the EXEC statement.)" - I can just suppose this is the using of the output parameters in the stored procedures (spname @param1, @param2 OUTPUT).

Correct! Good boy!

"or decided that it was more pedagogical to keep quiet about this option, which after all is quite a special case." - oh yes, that was a great pedagogical technique: to illustrate the 2 ways to assign a value (3 with the DECLARE) and post the link to the page (right above the picture I've posted above) that describes the totally different way :)))

Oops!

0 Votes 0 ·

:)))

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Maybe the author considered prematurely to show the method based on EXEC because this statement was not described yet.

By the way, there is a sixth way. Can you spot it? (Hint: that does not include EXEC statement).


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered MikhailFirsov-1277 commented

Thank you all once again for your help!!!

... can't choose the answer that "was helpful" - all of them "are helpful"!!! - the previous forum was better as I could vote for multiple answers!!!


"By the way, there is a sixth way. Can you spot it? (Hint: that does
not include EXEC statement)."
- no more ... What is it???

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MikhailFirsov-1277,

Thanks for your confirmation.

You could consider to upvote all the helpful answers and accept the most helpful one as answer which would be helpful to other users who encounter the same issue or concern and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·

Melissa, please excuse me but how can I "... upvote all the helpful answers" - I see just the single Accept Answer button ... :(

0 Votes 0 ·

Hi @MikhailFirsov-1277,

You could upvote all helpful answers one by one and click Accept Answer button under the most helpful one.

Right now Q&A supports only one accepted answer per question.

Sorry for any misunderstanding.

Best regards
Melissa

0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered ErlandSommarskog commented

... "By the way, there is a sixth way. Can you spot it? (Hint: that does not include EXEC statement)." - Viorel-1, would you please explain what's the 6th method is?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


I think that it is ‘FETCH … INTO @variable’.


0 Votes 0 ·

Oh, thank you very much for pointing it out - it's strange that the MS textbook describes only 3 methods... :(

0 Votes 0 ·

And is that the end of it? No. I was not able myself to think of FETCH, but another command that came to mind was BEGIN DIALOG CONVERSION which also set can set a variable. This is a command that Mikhail will not find in his textbook, as this is part of Service Broker, which is a more advanced feature that many SQL developers never come in contact with.

And, who knows, if we think harder, we may be able to think of an eighth and a ninth way to set a variable. But the point of the textbook has already been proven. There are three general ways to assign variables. The other only work in specific context. The purpose of EXEC is not to assign a variable, and receiving the return value is optional. OUTPUT parameters may not be optional, but it is still a special case, as is the FETCH statement or BEGIN DIALOG.

0 Votes 0 ·