PRINT RETURN

Bob Bedell · January 18th, 2008, 02:37 PM

Hello,

I can print output parameters in SQL with code like:

Code:

DECLARE @pageCount int
EXEC Test 10, 2, @pageCount OUTPUT
PRINT 'PageCount: ' + cast(@pageCount as varchar)

How can I print a RETURN value to the messages window?

Thanks,

Bob

urtrivedi · January 18th, 2008, 03:32 PM

You can use
raiseerror('PageCount: ' + cast(@pageCount as varchar),1,15)

urt

planoie · January 18th, 2008, 04:20 PM

Bob,

I would imagine that you can't. By the nature of it, the RETURN value isn't available until after you have RETURNed it. The PRINT command isn't available after you RETURN from the sproc so you're in a catch 22.

-Peter

Bob Bedell · January 18th, 2008, 09:25 PM

Thanks both for the replys. -Bob

Bob Bedell · February 1st, 2008, 01:20 AM

Well, after a bunch a fiddling around I finally stumbled on a solution. Doesn't appear that you can 'print' the return value from a sproc when working in SQL Server, but you can 'select' it, like:

DECLARE @return_value int, @rows_affected int
EXEC @return_value = Test_InsertTest 'input1', 'input2', @rows_affected output
SELECT 'Return value' = @return_value;
PRINT 'Rows affected = ' + cast(@rows_affected as varchar)

Here's the sproc I was palying with:

CREATE PROCEDURE [dbo].[Test_InsertTest]
(
        @column1 int,
        @column2 varchar(50),
        @rows_affected int output
)
AS
    INSERT INTO Test
        (column1, column2)
    VALUES
        (@column1, @column2)

    SET @rows_affected = @@ROWCOUNT

    IF (@@ERROR <> 0)
        RETURN -1
    ELSE
        RETURN SCOPE_IDENTITY()

- Bob

Bob Bedell · February 1st, 2008, 02:06 AM

Just noticed '@column1 int' should be a varchar or 'input1' should be an integer value. Anyway, the result set and message are simply:

Return value
-------------
1

Rows affected = 1

robprell · February 7th, 2008, 07:26 PM

If you use stored proc 1 to call up stored proc 2 and stored proc 2 returns a value. You can print it in stored proc 1. But I believe you need to do this with two stored procs. You capture the "return" value into a variable and then print it in proc 1.

It is possible but its a MAJOR PAIN in the behind.