How Stored Procedure determine to return a value?

Question

Post reply

How Stored Procedure determine to return a value?

masoudk1990

SSCommitted

Points: 1651
More actions
July 5, 2013 at 9:08 am

#277086

This is my Stored Procedure:
DECLARE @MyString NVARCHAR(3);
SELECT @MyString = 'foo'
SELECT CASE
WHEN 1 = 1
THEN 'One is equal to one'
END
If I execute this Stored Procedure I have this output:
'One is equal to one'
Now I change my Stored Procedure to this:
DECLARE @MyString NVARCHAR(3);
SELECT CASE
WHEN 1 = 1
THEN 'One is equal to one'
END
SELECT @MyString = 'foo'
Now if I execute my Stored Procedure once again I have this output:
'One is equal to one'
Why? I expected this time I should have 'foo' as output.
Because the last SELECT statement is 'foo'
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nick_UK SSCommitted Points: 1909 More actions · Answer 1

Hi,

The statement

SELECT @MyString = 'foo'

is populating the variable with the value 'foo' to return this value you would then need to select the variable

SELECT @mystring

BOL reference: http://msdn.microsoft.com/en-us/library/ms187953(v=SQL.105).aspx

masoudk1990 SSCommitted Points: 1651 More actions · Answer 2

Thank you very much.

I changed my Stored Procedure to this:

DECLARE @MyString NVARCHAR(3);

SELECT CASE

WHEN 1 = 1

THEN 'One is equal to one'

END

SELECT @MyString = 'foo'

SELECT @MyString

Now I have this two lovely outputs:

'One is equal to one'

'foo'

I thought SELECT returns values and SET populating the variable with the values.

For example:

SET @MyString = 'foo'

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

masoudk1990 (7/5/2013)
Thank you very much.
I changed my Stored Procedure to this:
DECLARE @MyString NVARCHAR(3);
SELECT CASE
WHEN 1 = 1
THEN 'One is equal to one'
END
SELECT @MyString = 'foo'
SELECT @MyString
Now I have this two lovely outputs:
'One is equal to one'
'foo'
I thought SELECT returns values and SET populating the variable with the values.
For example:
SET @MyString = 'foo'

Both SET and SELECT can be used to assign values to variables. SET only works with one variable at a time. SELECT can set the values of multiple variables all with the same SELECT. In fact, it's an optimization for functions and procs that are frequently hit because the SELECT assignment of multiple variables is actually a wee bit faster than using SET for multiple single variables.

You can do all sorts of "tricks" with variables and column aliasing for returns...

DECLARE @MyString NVARCHAR(3),

@OtherString NVARCHAR(100)

;

SELECT @OtherString = CASE

WHEN 1 = 1

THEN 'One is equal to one'

END,

@MyString = 'foo'

;

SELECT SomeColumn1 = @MyString,

SomeColumn2 = @OtherString

;

By the way, what you've posted isn't a stored procedure. It's just a script.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

masoudk1990 SSCommitted Points: 1651 More actions · Answer 4

Thanks Jeff Moden, I'm your fan in this forum.

By the way, what you've posted isn't a stored procedure. It's just a script

You wrote this Stored Procedure in one of your articles:

SELECT @MyCount = @@ROWCOUNT

SELECT CASE

WHEN COUNT(hi.RowNum) + 1 = @MyCount

THEN 'Account Running Total Calculations are correct'

ELSE 'There are some errors in the Account Running Totals'

END

I was confused why first SELECT statement don't show @MyCount in output.

Because I have a programming background, and I thought SELECT is equal to RETURN.

In other programming languages if you have this code:

int x;

Return x = 10;

Of course Return don't populating x with 10 😉

But now I understand it isn't a stored procedure. It's just a script.

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.