I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset.
I can get either but not both. (ie. if there is a select then there is no output parameter)
The stored procedure is:
ALTER PROCEDURE test
(
@Msgs nvarchar(150) OUTPUT
)
As
declare @selCount int
SELECT folderLocation, externalDocsID from externalDocs
set @selCount = @@ROWCOUNT
IF @@ROWCOUNT = 0
BEGIN
SET @Msgs = 'No folders meet this selection criteria'
RETURN
END
else
BEGIN
SET @Msgs = @selCount
END
return
----------------------------------------------------------------------------------
the asp code is
Dim cnnStoredProc ' Connection object
Dim cmdStoredProc ' Command object
Dim rstStoredProc ' Recordset object
Dim folderText
Set cnnStoredProc = Server.CreateObject("ADODB.Connection")
cnnStoredProc.Open db
' get the correct records for a page
Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Create Command object we'll use to execute the SP
cmdStoredProc.ActiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.CommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.Parameters.Refresh
' ---------- SET PARAMETERS ----GET A PAGE WORTH OF RECORD--------------------------------------------------------------------
set prop=ADODB.Parameter
cmdStoredProc.Parameters("@functionCode").Value = "L" 'functionCode
cmdStoredProc.Parameters("@customerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.Parameters("@searchcriteria").Value = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.Parameters("@excludeDefined").Value = "Y" '
end if
set rstStoredProc = cmdStoredProc.Execute
'
response.write("msgs=" & cmdStoredProc("@Msgs")) ' <<<<<<< THIS DOESN'T WORK--------------------------------------------------------------------------------------------------
I can enumerate through the recordset but the output parameter is blank.
Any Ideas
Thanks