How Stored Procedure determine to return a value?

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

  • 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

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

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

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

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

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