Why does the output of this simple proc change datatypes?

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 T开发者_开发知识库RAN

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

---

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

---

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

---

@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

---

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