Why does the output of this simple proc change datatypes?

Question

In testing output values from procs, why does the final select @TestValOut return 0 instead of null or an empty string?

I understand the correct way to do this is by using OUTPUT parameters, so the question really becomes: Why is the datatype of the set value of @TestValOut, at execution, an integer?

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Custom.test') AND type in (N'P', N'PC'))
    DROP PROCEDURE Custom.test
GO

CREATE PROCEDURE Custom.test (
    @CurrentUserID INT = 1
    )
As
    Declare @TestValIn varchar(max)
    select @TestValIn='asdf'

GO

BEGIN TRAN

    Declare @TestValOut varchar(max)
    set @TestValOut='ffff'
    Exec @TestValOut=Custom.test @CurrentUserID=1
    select @TestValOut
ROLLBACK

score 4 · Accepted Answer · edited May 23 '17 at 11:55

A return value in a stored procedure is always an integer, as a matter of fact you can only use an integer with a return value. The fact that you see 0 means the proc executed correctly, this is the return value that SQL Server returns telling you what the result of the proc execution is

For fun do a select 1/0 in the proc and you will see it won't be 0 anymore

See here

Is a return value of 0 always a success in stored procedures?

here are the examples from that answer

CREATE PROC pr_test AS 
SELECT 1/0

RETURN 0
GO

Now run it

DECLARE @i INT
exec @i = pr_test

SELECT @i  -- will be 0

DROP PROC pr_test

Now let's do it again without the return statement

CREATE PROC pr_test2 AS 
SELECT 1/0

GO

DECLARE @i INT
exec @i = pr_test2

SELECT @i  -- will be - 6

Better to use an output parameter to pass back statuses and or messages

Nonsense, `@TestValOut` gets the return value not the result set — SQLMenace, Jun 09 '11 at 18:19
@ScottV: wrong. @TestValOut is assigned the value from RETURN. There is no resultset from the stored procedure — gbn, Jun 09 '11 at 18:19

score 2 · Answer 2 · answered Jun 09 '11 at 18:20

2

I think what you're trying to do is use an output parameter, which should be done like this.

CREATE PROCEDURE Custom.test (
    @CurrentUserID INT = 1,
    @TestValOut varchar(max) OUTPUT
    )
As
    select @TestValOut='asdf'

GO

BEGIN TRAN    
    Declare @TestValOut varchar(max)
    Exec Custom.test @CurrentUserID=1, @TestValOut OUTPUT
    select @TestValOut
ROLLBACK

answered Jun 09 '11 at 18:20

Joe Stefanelli

132,803
19
237
235

Yes, this is true. However the question acknowledges that this is the case -- but looks for a reasoning for the case of a(n implicit) return. – Jun 09 '11 at 18:52

score 1 · Answer 3 · answered Jun 09 '11 at 18:17

@TestValOut is assigned the value that would be returned by an "RETURN" like this:

CREATE PROCEDURE Custom.test (
    @CurrentUserID INT = 1
    )
As
    Declare @TestValIn varchar(max)
    select @TestValIn='asdf'

RETURN --defaults to zero, this is the value
GO

or

CREATE PROCEDURE Custom.test (
    @CurrentUserID INT = 1
    )
As
    Declare @TestValIn varchar(max)
    select @TestValIn='asdf'

RETURN 0 --this is the value
GO

Your stored procedure doesn't actually do anything at all: there is no resultset. To see the difference...

CREATE PROCEDURE Custom.test (
    @CurrentUserID INT = 1
    )
As

    select * from sys.objects

    RETURN 42 --random value
GO

DECLARE @rtn int
EXEC @rtn = Custom.test
--you have the output of sys.objects now
--and the scalar RETURN value
SELECT @rtn

Akhil · Answer 4 · 2011-06-09T18:29:35.977

0

You need to explicity say what value to return. Default is 0

CREATE PROCEDURE Custom.test (
    @CurrentUserID INT = 1
    )
As
    Declare @TestValIn varchar(max)
    select @TestValIn='asdf'

    RETURN 0

GO

edited Jun 09 '11 at 18:29

answered Jun 09 '11 at 18:19

Akhil

7,570
1
24
23

Will give a warning: can't RETURN NULL – gbn Jun 09 '11 at 18:22
Thanks for letting me know. Updated to return 0! – Akhil Jun 09 '11 at 18:29

Why does the output of this simple proc change datatypes?

4 Answers4