Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Unanswered: Stored Proc's Using OUTPUT

    How can I view the output of a stored procedure that is returning a OUTPUT variable? I've written a stored proc that uses OUTPUT but when I run it, all I see is "The command(s) completed successfully." I'm at a loss on how to debug/verify/view the output value.

    The same thing happens using this example from MS.

    CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT
    AS
    SELECT 'Title Name' = title
    FROM titles
    WHERE title LIKE @TITLE
    SELECT @SUM = SUM(price)
    FROM titles
    WHERE title LIKE @TITLE
    GO


    TIA - KB

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 @x int OUTPUT AS SELECT @x = 1
    GO
    
    DECLARE @x int
    EXEC mySproc99 @x OUTPUT
    
    SELECT @x as myOutput99
    GO
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    8

    Using Stored Procedure OUTPUT

    Thanks for the quick response - however, I guess I should have added to my original message - I need to use this from a crystal report.

    Associate the SP with a specific report field and when the report is run the output value from the stored procedure is displayed within the report. Like a function would do.

    A function won't work here since I need to raiserror and use non-deterministic fucntions.

    Can a stored procedure using a output variable be used in this fashion? If not, other suggestions?

    Thanks again.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry, don't know Crystal (and from what I hear, I'm glad).


    Did you try here?

    http://www.dbforums.com/f244
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Posts
    8

    Thanks

    Thanks - I don't do CR either but I do need to provide some SP/UDF support at times ...

    KB

  6. #6
    Join Date
    Jun 2004
    Posts
    4

    Try this

    create proc sp_test1
    as
    declare @result int
    select @result = 99
    return @result

    go

    declare @result33 int
    select @result33 = -1
    exec @result33 = sp_test1

    select 'Result : ', @result33
    go
    drop proc sp_test1
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •