?
Solved

OUTPUT parameters for a stored procedure

Posted on 2005-05-16
16
Medium Priority
?
272 Views
Last Modified: 2010-03-19
I have written the following stored procedure, it is working fine, now I want to capture the results of the stored procedure in output parameters so that they can be passed to my web page.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 
CREATE PROCEDURE sp_search_6
@receipt_id as varchar(20) = NULL,
@wh_id as varchar(20) = NULL,
@po_number as varchar(20) = NULL,
@rcptDtStart as DATETIME = NULL,
@rcptDtEnd as DATETIME = NULL,
@status as varchar(20) = NULL
AS

DECLARE @SELECT AS VARCHAR(2000)
DECLARE @WHERE AS VARCHAR(8000)
DECLARE @SORT AS VARCHAR(100)
SET @WHERE = NULL
SET @SELECT =      'SELECT A.receipt_id, A.wh_id, B.status, A.po_number, A.receipt_date  
            FROM HJ_DEV1.AAD.dbo.t_receipt_master as B RIGHT OUTER JOIN HJ_DEV1.AAD.dbo.t_receipt_detail as A
            ON B.receipt_id = A.receipt_id'

IF @receipt_id IS NOT NULL
  SET @WHERE = ' A.receipt_id=' + CAST(@receipt_id as varchar(20))

IF @wh_id IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.wh_id=' + CAST(@wh_id as varchar(20)) /*+ ''''*/
      ELSE
      SET @WHERE = @WHERE + ' AND A.wh_id=' + CAST(@wh_id as varchar(20)) /*+ ''''*/
  END
IF @status IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' B.status=''' + CAST(@status as varchar(20)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND B.status=''' + CAST(@status as varchar(20)) + ''''
  END
IF @po_number IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.po_number=' + CAST(@po_number as varchar(20)) /*+ ''''*/
  ELSE
    SET @WHERE = @WHERE + ' AND A.po_number=' + CAST(@po_number as varchar(20)) /*+ ''''*/
  END
IF @rcptDtStart IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.receipt_date>=''' + CAST(@rcptDtStart as varchar(30)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND A.receipt_date>=''' + CAST(@rcptDtStart as varchar(30)) + ''''
  END
IF @rcptDtEnd IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.receipt_date<=''' + CAST(@rcptDtEnd as varchar(40)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND receipt_date<=''' + CAST(@rcptDtEnd as varchar(40)) + ''''
  END
IF @WHERE IS NOT NULL
  SET @WHERE = ' WHERE ' + @WHERE
SET @SORT = ' ORDER BY CAST(A.receipt_id as int(4)) DESC'
EXECUTE (@SELECT + @WHERE + @SORT)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I need to store the results of A.receipt_id, A.wh_id, B.status, A.po_number, A.receipt_date as output parameters. I tried declaring 5 output parameters assigned them to the columns in the select statement, but when I execute the stored procedure by supplying the values for the input parameters and the output parameter variable I get an error message asking me to declare the output variable.
0
Comment
Question by:srafi78
  • 6
  • 6
  • 4
16 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 14011818
Why not return them as a SELECT?  You would then use ADO to retrieve a recordset based on the procedure and display the values on your web page.

Leon
0
 
LVL 18

Expert Comment

by:mdougan
ID: 14011846
Hi srafi78,

This is how you'd declare the parameter's in the proc you are trying to call:

ALTER  PROC dbo.process_table_delete_row_msg
    @message_id                int,            -- id in edge table
    @message_queue_id          int,
    @message_type              varchar(30),
    @mod_pgm                   smallint,
    @mod_time                  datetime = NULL,
    @loan_no                   int            OUTPUT,
    @error_cnt                 int            OUTPUT,
    @warning_cnt               int            OUTPUT,
    @result_message            varchar(100)   OUTPUT,
    @result_status             int            OUTPUT
AS BEGIN
    DECLARE @idoc              etc......

This is how you'd declare your variables in another bit of SQL

    DECLARE @result_message            varchar(100)  
    DECLARE @result_status             int


And this is how you'd call the stored proc from that bit of SQL

            EXEC @return_value = process_table_delete_row_msg
                @lims_message_id,
                @message_queue_id,
                @message_type,
                @mod_pgm,
                @mod_time,
                @loan_no         OUTPUT,
                @error_cnt       OUTPUT,
                @warning_cnt     OUTPUT,
                @result_message  OUTPUT,
                @result_status   OUTPUT

Cheers!
0
 
LVL 8

Author Comment

by:srafi78
ID: 14011931
This is how I modified my SP

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 
CREATE PROCEDURE sp_search_7
@receipt_id as varchar(20) = NULL,
@wh_id as varchar(20) = NULL,
@po_number as varchar(20) = NULL,
@rcptDtStart as DATETIME = NULL,
@rcptDtEnd as DATETIME = NULL,
@status as varchar(20) = NULL,
@out_receipt_id varchar(20) = NULL OUT,
@out_wh_id varchar(20) = NULL OUT,
@out_po_number varchar(20) = NULL OUT,
@out_receipt_date DATETIME = NULL OUT,
@out_status varchar(20) = NULL OUT
AS

DECLARE @SELECT AS VARCHAR(2000)
DECLARE @WHERE AS VARCHAR(8000)
DECLARE @SORT AS VARCHAR(100)
SET @WHERE = NULL
SET @SELECT =      'SELECT @out_receipt_id = A.receipt_id, @out_wh_id = A.wh_id, @out_status = B.status,
            @out_po_number = A.po_number, @out_receipt_date = A.receipt_date FROM
            HJ_DEV1.AAD.dbo.t_receipt_master as B RIGHT OUTER JOIN HJ_DEV1.AAD.dbo.t_receipt_detail as A
            ON B.receipt_id = A.receipt_id'

IF @receipt_id IS NOT NULL
  SET @WHERE = ' A.receipt_id=' + CAST(@receipt_id as varchar(20))

IF @wh_id IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.wh_id=' + CAST(@wh_id as varchar(20)) /*+ ''''*/
      ELSE
      SET @WHERE = @WHERE + ' AND A.wh_id=' + CAST(@wh_id as varchar(20)) /*+ ''''*/
  END
IF @status IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' B.status=''' + CAST(@status as varchar(20)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND B.status=''' + CAST(@status as varchar(20)) + ''''
  END
IF @po_number IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.po_number=' + CAST(@po_number as varchar(20)) /*+ ''''*/
  ELSE
    SET @WHERE = @WHERE + ' AND A.po_number=' + CAST(@po_number as varchar(20)) /*+ ''''*/
  END
IF @rcptDtStart IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.receipt_date>=''' + CAST(@rcptDtStart as varchar(30)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND A.receipt_date>=''' + CAST(@rcptDtStart as varchar(30)) + ''''
  END

IF @rcptDtEnd IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.receipt_date<=''' + CAST(@rcptDtEnd as varchar(40)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND receipt_date<=''' + CAST(@rcptDtEnd as varchar(40)) + ''''
  END
IF @WHERE IS NOT NULL
  SET @WHERE = ' WHERE ' + @WHERE
SET @SORT = ' ORDER BY CAST(A.receipt_id as int(4)) DESC'
EXECUTE (@SELECT + @WHERE + @SORT)
RETURN
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Declare @out_receipt_id_out as varchar(20), @out_wh_id_out as varchar(20),
@out_po_number_out as varchar(20), @out_receipt_date_out as DateTime,
@out_status_out as varchar(20)

EXEC sp_search_7 NULL,NULL,NULL,'2003-07-17','2003-08-17',C,
@out_receipt_id_out = @out_receipt_id OUT,
@out_wh_id_out = @out_wh_id OUT,
@out_po_number_out = @out_po_number OUT,
@out_receipt_date_out = @out_receipt_date OUT,
@out_status_out = @out_status OUT
 
I am still getting the error message

Server: Msg 137, Level 15, State 2, Line 6
Must declare the variable '@out_receipt_id'.

Am I doing something wrong here
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:leonstryker
ID: 14011952
You can not mix the way you pass parameters in the call.

Try this:

EXEC sp_search_7 NULL,NULL,NULL,'2003-07-17','2003-08-17', 'C'
0
 
LVL 8

Author Comment

by:srafi78
ID: 14012009
I get the same error message..........

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@out_receipt_id'.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 14012136
Could you list your procedure as it is now.
0
 
LVL 8

Author Comment

by:srafi78
ID: 14012430
The SP is as follows, how can I execute the sp so that the values returned are stored in the output parameters

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 
CREATE PROCEDURE sp_search_7
@receipt_id as varchar(20) = NULL,
@wh_id as varchar(20) = NULL,
@po_number as varchar(20) = NULL,
@rcptDtStart as DATETIME = NULL,
@rcptDtEnd as DATETIME = NULL,
@status as varchar(20) = NULL,
@out_receipt_id varchar(20) = NULL OUT,
@out_wh_id varchar(20) = NULL OUT,
@out_po_number varchar(20) = NULL OUT,
@out_receipt_date DATETIME = NULL OUT,
@out_status varchar(20) = NULL OUT
AS

DECLARE @SELECT AS VARCHAR(2000)
DECLARE @WHERE AS VARCHAR(8000)
DECLARE @SORT AS VARCHAR(100)
SET @WHERE = NULL
SET @SELECT =     'SELECT @out_receipt_id = A.receipt_id, @out_wh_id = A.wh_id, @out_status = B.status,
          @out_po_number = A.po_number, @out_receipt_date = A.receipt_date FROM
          HJ_DEV1.AAD.dbo.t_receipt_master as B RIGHT OUTER JOIN HJ_DEV1.AAD.dbo.t_receipt_detail as A
          ON B.receipt_id = A.receipt_id'

IF @receipt_id IS NOT NULL
  SET @WHERE = ' A.receipt_id=' + CAST(@receipt_id as varchar(20))

IF @wh_id IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.wh_id=' + CAST(@wh_id as varchar(20)) /*+ ''''*/
     ELSE
      SET @WHERE = @WHERE + ' AND A.wh_id=' + CAST(@wh_id as varchar(20)) /*+ ''''*/
  END
IF @status IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' B.status=''' + CAST(@status as varchar(20)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND B.status=''' + CAST(@status as varchar(20)) + ''''
  END
IF @po_number IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.po_number=' + CAST(@po_number as varchar(20)) /*+ ''''*/
  ELSE
    SET @WHERE = @WHERE + ' AND A.po_number=' + CAST(@po_number as varchar(20)) /*+ ''''*/
  END
IF @rcptDtStart IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.receipt_date>=''' + CAST(@rcptDtStart as varchar(30)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND A.receipt_date>=''' + CAST(@rcptDtStart as varchar(30)) + ''''
  END

IF @rcptDtEnd IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.receipt_date<=''' + CAST(@rcptDtEnd as varchar(40)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND receipt_date<=''' + CAST(@rcptDtEnd as varchar(40)) + ''''
  END
IF @WHERE IS NOT NULL
  SET @WHERE = ' WHERE ' + @WHERE
SET @SORT = ' ORDER BY CAST(A.receipt_id as int(4)) DESC'
EXECUTE (@SELECT + @WHERE + @SORT)
RETURN
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


THANKS!!!
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 150 total points
ID: 14012489
Change:

@out_receipt_id varchar(20) = NULL OUT,
@out_wh_id varchar(20) = NULL OUT,
@out_po_number varchar(20) = NULL OUT,
@out_receipt_date DATETIME = NULL OUT,
@out_status varchar(20) = NULL OUT

To:

@out_receipt_id varchar(20)  OUT,
@out_wh_id varchar(20) OUT,
@out_po_number varchar(20)  OUT,
@out_receipt_date DATETIME OUT,
@out_status varchar(20)  OUT

BTW, I still say you do not need to use OUTPUT parameters Why not leave it as a plain SELECT?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 14012503
srafi78,

Oh, I see the problem...


Instead of doing this:

EXEC sp_search_7 NULL,NULL,NULL,'2003-07-17','2003-08-17',C,
@out_receipt_id_out = @out_receipt_id OUT,
@out_wh_id_out = @out_wh_id OUT,
@out_po_number_out = @out_po_number OUT,
@out_receipt_date_out = @out_receipt_date OUT,
@out_status_out = @out_status OUT

Do

EXEC sp_search_7 NULL,NULL,NULL,'2003-07-17','2003-08-17',C,
@out_receipt_id OUT,
@out_wh_id OUT,
@out_po_number OUT,
@out_receipt_date OUT,
@out_status OUT

SET @out_receipt_id_out = @out_receipt_id
SET @out_wh_id_out = @out_wh_id
SET @out_po_number_out = @out_po_number
SET @out_receipt_date_out = @out_receipt_date
SET @out_status_out = @out_status
0
 
LVL 18

Expert Comment

by:mdougan
ID: 14012560
srafi78,
OK, going back and looking at your code more closely, in the EXEC you're refering to the parameter names as defined in your proc.... what you really need to do is this:

In your SQL calling the stored proc:

Declare @out_receipt_id_out as varchar(20), @out_wh_id_out as varchar(20),
@out_po_number_out as varchar(20), @out_receipt_date_out as DateTime,
@out_status_out as varchar(20)

EXEC sp_search_7 NULL,NULL,NULL,'2003-07-17','2003-08-17',C,
@out_receipt_id_out OUTPUT,
@out_wh_id_out OUTPUT,
@out_po_number_out OUTPUT,
@out_receipt_date_out OUTPUT,
@out_status_out OUTPUT

Just referencing the names of the variables you declared... it's their position in the EXEC call that will associate them with the parameters in your stored procedure.  After this call, if you wanted to do something with the variables, just reference them as defined:

IF @out_status_out = 'A'
   BEGIN
     ...
   END
0
 
LVL 8

Author Comment

by:srafi78
ID: 14012676
mdougan,

I tried executing the proc in the order you described but still get the same error message

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@out_receipt_id'.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 14012726
I think you are getting the error because the you are assigning value to the variable inside dynamic SQL call  I think you need to take that out because the variables are not declared in that scope.

Leon






0
 
LVL 8

Author Comment

by:srafi78
ID: 14013673
Thanks All

Although I did not get to the solution, I will be using the result of the select statement from the SP and use it in my code as adviced by Leon.

Thanks!
SR
0
 
LVL 18

Expert Comment

by:mdougan
ID: 14014085
srafi78,
Hi SR,

I'm happy to help you try to work out the other solution for future reference if you'd like... I probably just need to see the current state of your code now, as I'm not sure what is the latest.... show me the declaration of your output variables in the SQL that is calling your stored proc, the EXEC statement calling your stored proc, and then the complete contents of your stored proc and I should be able to sort it out..... don't worry about the points or anything
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 14014105
DITTO, to mdougan's post.  I do think it is as I stated in the 14012726.  I am wondering if as a test you can remove all to dynamic SQL from the store procedure and run it straight with just in and out parameters.

Leon
0
 
LVL 8

Author Comment

by:srafi78
ID: 14032223
Sorry for the delayed response

The latest version of my SP is

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 
CREATE PROCEDURE sp_search_6
@receipt_id as varchar(20) = NULL,
@wh_id as varchar(20) = NULL,
@po_number as varchar(20) = NULL,
@rcptDtStart as DATETIME = NULL,
@rcptDtEnd as DATETIME = NULL,
@status as varchar(20) = NULL
AS

DECLARE @SELECT AS VARCHAR(2000)
DECLARE @WHERE AS VARCHAR(8000)
DECLARE @SORT AS VARCHAR(100)
SET @WHERE = NULL
SET @SELECT =      'SELECT A.receipt_id, A.wh_id, B.status, A.po_number, CONVERT(VARCHAR(20),A.receipt_date,101) as receipt_date  
            FROM HJ_DEV1.AAD.dbo.t_receipt_master as B RIGHT OUTER JOIN HJ_DEV1.AAD.dbo.t_receipt_detail as A
            ON B.receipt_id = A.receipt_id'

IF @receipt_id IS NOT NULL
  SET @WHERE = ' A.receipt_id=' + CAST(@receipt_id as varchar(20))

IF @wh_id IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.wh_id=' + CAST(@wh_id as varchar(20)) /*+ ''''*/
      ELSE
      SET @WHERE = @WHERE + ' AND A.wh_id=' + CAST(@wh_id as varchar(20)) /*+ ''''*/
  END
IF @status IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' B.status=''' + CAST(@status as varchar(20)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND B.status=''' + CAST(@status as varchar(20)) + ''''
  END
IF @po_number IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.po_number=' + CAST(@po_number as varchar(20)) /*+ ''''*/
  ELSE
    SET @WHERE = @WHERE + ' AND A.po_number=' + CAST(@po_number as varchar(20)) /*+ ''''*/
  END
IF @rcptDtStart IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.receipt_date>=''' + CAST(@rcptDtStart as varchar(30)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND A.receipt_date>=''' + CAST(@rcptDtStart as varchar(30)) + ''''
  END
IF @rcptDtEnd IS NOT NULL
  BEGIN
    IF @WHERE IS NULL
      SET @WHERE = ' A.receipt_date<=''' + CAST(@rcptDtEnd as varchar(40)) + ''''
    ELSE
      SET @WHERE = @WHERE + ' AND receipt_date<=''' + CAST(@rcptDtEnd as varchar(40)) + ''''
  END
IF @WHERE IS NOT NULL
  SET @WHERE = ' WHERE ' + @WHERE
SET @SORT = ' ORDER BY CAST(A.receipt_id as int(4)) DESC'
EXECUTE (@SELECT + @WHERE + @SORT)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Currently I am passing the result set of this query to the datareader in my web application and binding the data to the datagrid. I donot know if you can help me with this but the QA returns a particular set of rows when the SP is executed but when the SP is executed via the vb.net code the total rows displayed in the datagrid is one less than the rows returned in QA.

I was adviced by my friends not to use output parameters as it would take things more complicated, but still would like to know how to deal with output params

Thanks!
SR
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question