Why does the stored procedure behave differently?

28 Jul 2005 4:01 PM

Amil

Hi all,
Following is a code snippet that can be run directly from the Query
Analyzer. I am just trying to get my feet wet and I don't know why the
stored procedure: sp_test does not correctly assign the correct value to
@DataDesc.

DROP TABLE #test_table
CREATE TABLE #test_table (
    guidcolumn uniqueidentifier default (newid()),
    datadesc varchar(15) null)
GO
INSERT INTO #test_table (datadesc) VALUES ('Hello')
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE name = N'sp_test'
       AND     type = 'P')
    DROP PROCEDURE sp_test
GO
CREATE PROCEDURE sp_test
    @guidcolumn uniqueidentifier,
    @datadesc varchar(15) OUTPUT
AS
    SELECT
    @datadesc = datadesc
    FROM #test_table
    WHERE guidcolumn = @guidcolumn
GO
-- Test goes here.
DECLARE @guidcolumn uniqueidentifier
DECLARE @datadesc varchar(15)
SELECT @guidcolumn=guidcolumn FROM #test_table
EXEC sp_test @guidcolumn, @datadesc
SELECT @datadesc -- has null value
SELECT @datadesc=datadesc FROM #test_table WHERE guidcolumn=@guidcolumn
SELECT @datadesc -- has the correct value
GO

28 Jul 2005 4:23 PM

Barry

Amil,

If you run the following:

DECLARE @guidcolumn uniqueidentifier
DECLARE @datadesc varchar(15)
SELECT @guidcolumn=guidcolumn FROM #test_table
EXEC sp_test @guidcolumn, @datadesc

Then the return value is displayed.

The results you are getting is because you are selecting an empty
variable.

Hope this helps

Barry

28 Jul 2005 4:35 PM

Amil

"Barry" wrote:

> Amil,
>
> If you run the following:
>
> DECLARE @guidcolumn uniqueidentifier
> DECLARE @datadesc varchar(15)
> SELECT @guidcolumn=guidcolumn FROM #test_table
> EXEC sp_test @guidcolumn, @datadesc

I don't quite understand what you mean. I was expecting that after the EXEC
command, @datadesc would now have a value since the second parameter is an
OUTPUT parameter. Am I missing something here?

Show quote

>
> Then the return value is displayed.
>
> The results you are getting is because you are selecting an empty
> variable.
>
> Hope this helps
>
> Barry
>
>

28 Jul 2005 5:11 PM

Aaron Bertrand [SQL Server MVP]

> command, @datadesc would now have a value since the
> second parameter is an OUTPUT parameter.

But you didn't specify it as such.

28 Jul 2005 5:30 PM

Amil

"Aaron Bertrand [SQL Server MVP]" wrote:

> > command, @datadesc would now have a value since the
> > second parameter is an OUTPUT parameter.
>
> But you didn't specify it as such.
>

If you read my original post, you may notice that I am still trying to
learn. And I learned from Alejandro, constructively, that I should include
the OUTPUT keyword when executing the stored proc.
Show quote

>

28 Jul 2005 5:38 PM

Aaron Bertrand [SQL Server MVP]

> If you read my original post, you may notice that I am
> still trying to learn.

Yes, I did notice that.

> And I learned from Alejandro, constructively, that I
> should include the OUTPUT keyword when executing
> the stored proc.

Telling you that you didn't specify it as such is not constructive? And
does not help you learn?

Okay, I'll be sure to avoid answering your questions from here on out. Have
you ever heard the phrases, "don't look a gift horse in the mouth," "don't
shoot the messenger," "RTFM," or "don't sh*t where you eat"?

28 Jul 2005 4:35 PM

Alejandro Mesa

> EXEC sp_test @guidcolumn, @datadesc

EXEC sp_test @guidcolumn, @datadesc OUTPUT

AMB

Show quote

"Amil" wrote:

> Hi all,
> Following is a code snippet that can be run directly from the Query
> Analyzer. I am just trying to get my feet wet and I don't know why the
> stored procedure: sp_test does not correctly assign the correct value to
> @DataDesc.
>
> DROP TABLE #test_table
> CREATE TABLE #test_table (
>     guidcolumn uniqueidentifier default (newid()),
>     datadesc varchar(15) null)
> GO
> INSERT INTO #test_table (datadesc) VALUES ('Hello')
> IF EXISTS (SELECT name
>        FROM   sysobjects
>        WHERE name = N'sp_test'
>        AND     type = 'P')
>     DROP PROCEDURE sp_test
> GO
> CREATE PROCEDURE sp_test
>     @guidcolumn uniqueidentifier,
>     @datadesc varchar(15) OUTPUT
> AS
>     SELECT
>     @datadesc = datadesc
>     FROM #test_table
>     WHERE guidcolumn = @guidcolumn
> GO
> -- Test goes here.
> DECLARE @guidcolumn uniqueidentifier
> DECLARE @datadesc varchar(15)
> SELECT @guidcolumn=guidcolumn FROM #test_table
> EXEC sp_test @guidcolumn, @datadesc
> SELECT @datadesc -- has null value
> SELECT @datadesc=datadesc FROM #test_table WHERE guidcolumn=@guidcolumn
> SELECT @datadesc -- has the correct value
> GO

28 Jul 2005 4:41 PM

Amil

works wonders. thanks.

Show quote

"Alejandro Mesa" wrote:

> > EXEC sp_test @guidcolumn, @datadesc
>
> EXEC sp_test @guidcolumn, @datadesc OUTPUT

3 Aug 2005 6:26 AM

Roque.Francis

just replace
EXEC sp_test @guidcolumn, @datadesc
with
EXEC sp_test @guidcolumn, @datadesc out

and your code will work