Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 18th, 2008, 02:37 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default PRINT RETURN

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

 
Old January 18th, 2008, 03:32 PM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

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


urt
 
Old January 18th, 2008, 04:20 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 
Old January 18th, 2008, 09:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Thanks both for the replys. -Bob

 
Old February 1st, 2008, 01:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old February 1st, 2008, 02:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old February 7th, 2008, 07:26 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Textbox problem print preview vs. print jenisageek Access 5 May 2nd, 2008 12:54 PM
print the hidden page without the print dialog box kayzem Classic ASP Basics 0 April 21st, 2005 11:31 PM
Print and print preview file on the website withou appleLover General .NET 0 February 19th, 2005 02:24 AM
Macro to print to different print objects mikericc Access 1 April 21st, 2004 10:57 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.