Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How Stored Procedure determine to return a value? Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 9:08 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, November 22, 2014 5:33 AM
Points: 64, Visits: 290
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.
Post #1470786
Posted Friday, July 5, 2013 9:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 8:18 AM
Points: 1,320, Visits: 1,013
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
Post #1470791
Posted Friday, July 5, 2013 9:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, November 22, 2014 5:33 AM
Points: 64, Visits: 290
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.
Post #1470795
Posted Friday, July 5, 2013 5:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:50 PM
Points: 35,857, Visits: 32,525
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470889
Posted Friday, July 5, 2013 8:09 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, November 22, 2014 5:33 AM
Points: 64, Visits: 290
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.
Post #1470894
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse