OK, here's a simple procedure:
create procedure OutputTest (
@out int output) AS
BEGIN
SET NOCOUNT ON
SET @out = 33
exec sp_who2
END
In ASP, you would do this (I'm using the ADO constants for readability. You
should #include the adovbs.inc file, or use one of the methods shown here to
define the constants -
http://www.aspfaq.com/show.asp?id=2112):
<%
dim cn, cmd, rs, params
set cn=createobject("adodb.connection")
cn.open "<valid connection string>"
set cmd=createobject("adodb.command")
cmd.CommandText="OutputTest"
cmd.ActiveConnection= cn
cmd.CommandType = adCmdStoredProc
set params=cmd.parameters
params.append cmd.createparameter("@RETURN_VALUE", _
adInteger, adParamReturnValue)
params.append cmd.createparameter("@out", adInteger, _
adParamOutput)
set rs=cmd.Execute
'the recordset has to be "consumed" before the output
'value is available, so:
response.write rs.GetString
rs.close
response.write "output parameter: " & params(1).value
cn.close:set cn=nothing
set rs=nothing
%>
But again, GetRows is a better solution for your situation.
Bob Barrows
scott wrote:
> 1st off, thanks for educating me on the NextRecordset prop, didn't
> know such exists. However, for learning purposes could you either
> supply a few lines of code similating retrieving a recordset followed
> by an output parameter? A link to an example would be fine.
>
> I didn't realize I could do 2 things at once with ASP.
>
>
>
> "Bob Barrows [MVP]" <> wrote in message
> news:...
>> scott wrote:
>>> In LISTING 2, I have a SPROC that returns a recordset and a
>>> recordcount in SQL QA. I can access the Recordset with no problem.
>>> How can I grab the Recordcount with ASP code at the same time I'm
>>> getting the Recordset? In QA, the RecordCount displays below the
>>> Recordset.
>>>
>>> The below USAGE code will display my SPROC results.
>>>
>>> USAGE:
>>> EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID',
>>> 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'
>>> ************************ LISTING 1: ASP CODE
>>> Set objConn = CreateObject("ADODB.Connection")
>>> objConn.Open myDSN
>>> Set objRS = Server.CreateObject("ADODB.Recordset")
>>>
>>> strSQL = "SELECT_WITH_PAGING " & SParms
>>>
>>> objRS.Open strSQL, objConn
>>>
>>>
>>>
>>>
>>>
>>> ************************
>>> LISTING 2: SPROC
>>>
>>> CREATE PROCEDURE SELECT_WITH_PAGING (
>>> @strFields VARCHAR(4000) ,
>>> @strPK VARCHAR(100),
>>> @strTables VARCHAR(4000),
>>> @intPageNo INT = 1,
>>> @intPageSize INT = NULL,
>>> @blnGetRecordCount BIT = 0,
>>> @strFilter VARCHAR(8000) = NULL,
>>> @strSort VARCHAR(8000) = NULL,
>>> @strGroup VARCHAR(8000) = NULL
>>> )
>>>
>>> AS
>> <snip>
>>>
>>> --IF WE NEED TO RETURN THE RECORDCOUNT
>>> IF @blnGetRecordCount = 1
>>> IF @strGroupCriteria != ''
>>> EXEC (
>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
>>> @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl
>>> (id)' )
>>> ELSE
>>> EXEC (
>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
>>> @strFilterCriteria + @strGroupCriteria
>>> )
>>> GO
>>
>> I will leave it to others to comment on this use of dynamic sql
>> (think "sql
>> injection") ...
>>
>> You're returning a second resultset containing the count, so you
>> would use the recordset's NextRecordset method to access it:
>>
>> set objRS = objRS.NextRecordset
>> reccount=objRS("recordcount")
>>
>> I would be more inclined to use an output parameter, using a Command
>> object
>> to retrieve the value.
>>
>> Bob Barrows
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Similar Threads
