Re: Stored Procedure, Output Value with SELECT Statement
From: Ross McKay (rosko_at_zeta.NOT.THIS.BIT.org.au)
Date: 03/08/04
- Next message: Kevin Yu [MSFT]: "Re: Resync or Requiry"
- Previous message: maple: "How to show data in a datagrid without right-clicking on the "Retrieve Fields"?"
- In reply to: SK: "Stored Procedure, Output Value with SELECT Statement"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 08 Mar 2004 07:47:06 GMT
G'day SK,
See modifications inline:
>ALTER PROCEDURE OutputMyValue
>(
> @ValueToBeOutput bit OUTPUT
>)
>AS
>BEGIN
Set NoCount On
> SELECT * FROM MyTable WHERE id = '123'
>
> SET @ValueToBeOutput = 1
>
>END
>
>How to resolve this? If I want both the output value and the select statements values to be returned?
First, you are best off with the Set NoCount On in your procedure.
Next, your output parameter will only be available to your client code
after you have read all of the select cursor on your client connection.
This is because the output parameters are at the end of the data stream
coming from SQL Server.
Choices:
a) do what you need with the cursor / recordset first, then look at
parameter
b) load the recordset into an array or something so that you can get to
the parameter
c) open the cursor as a client-side recordset, not server-side:
rs.CursorLocation = adUseClient
rs.Open cmd ' cmd is a command object;
' or alternatively,
rs.Open "OutputMyValue", db_connection, , adCmdStoredProc
I'd recommend option c)
regards,
Ross.
-- "There is more to life than simply increasing its speed." - Mahatma Gandhi
- Next message: Kevin Yu [MSFT]: "Re: Resync or Requiry"
- Previous message: maple: "How to show data in a datagrid without right-clicking on the "Retrieve Fields"?"
- In reply to: SK: "Stored Procedure, Output Value with SELECT Statement"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
