Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Return a value AND a resultset in an SP from VB

Author  Topic  y!2k Starting Member 1 Post Posted - 2001-03-06 : 09:47:39 There was a topic posted on this forum a few days ago: http://www.sqlteam.com/Forums/topic.asp?topic_id=1992&forum_id=5&cat_id=3 which sort of explains what I am attempting to do, but the replies did not try to explain the actual problem. When you return a value from an SP and return a resultset, the return value is empty. As a workaround I've been testing the properties of the returned resultset to determine what to do (recordset.state, recordset.EOF, etc.) but I cannot retrieve a return value. I have also attempted to use output parameters instead of a return value, but I receive the same problem. The output parameters are empty. Given an SP: create proc dbo.sp_test   @inparam int as begin   select * from categories   return @inparam End and the VB code: Dim cmd As ADODB.Command, rs As ADODB.Recordset Dim lRecs As Long, n As Long, ar As Variant Set cmd = New ADODB.Command With cmd   .ActiveConnection = SQL_CONNECT ' your connection string   .CommandText = "sp_test"   .Parameters.Append .CreateParameter("return", adInteger, adParamReturnValue)   .Parameters.Append .CreateParameter("@inparam", adInteger, adParamInput, , 99)   Set rs = .Execute(lRecs, , adCmdStoredProc)   Debug.Print "[" & .Parameters("return") & "]"   If rs.State = adStateOpen Then     ar = rs.GetRows()     For n = 0 To UBound(ar, 2)       Debug.Print ar(0, n) ' print the first field     Next ' n   End If End With Set cmd = Nothing