Question on variable

Question

Hello!

My textbook says there are two ways to assign a value to a variable: the SET and the SELECT:

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

Answer 1

Hi @Mikhail Firsov ,

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.

Answer 2

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.)

Answer 3

Hi @Mikhail Firsov ,

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.

Answer 4

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.

Answer 5

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