[RESOLVED] -- Stored Procedure?

[crosbj]

Using MSSQL 2k vb6/classic ado. I've search everywhere for a simple answer to this question from these forums to google. I have a simple sproc on my server with syntax as follows:

CREATE PROCEDURE [stp_Stop9Stat]
 AS SELECT Val11 FROM tblConveyorB3 WHERE(Id=27)
GO

I don't want to create a recordset I just want the return value of field 'Val11' from my select statement. I can execute this in Query Analyzer and get the value for 'Val11'. How can I do this with ado? All I get is the @Return_Value which is 0 of course. I just want the stored procedure to return the value from my Select statement without creating a recordset object. Here is the code that executes the sproc from within vb.

    Dim objCmd As New ADODB.Command
    With objCmd
        .CommandText = "stp_Stop9Stat"
        .ActiveConnection = DbSQLConn
        .CommandType = adCmdStoredProc
        .Execute
    End With

I know this is simple, but I can't seem to retrieve the value unless I build a recordset and set it to the Execute statement of my sproc? Am I looking at this wrong or can't I return a value from a sproc without using a recorset?
-jon

[techgnome]

If you don't want to (or can't) change the SP.... then there's not much you can do. You're going to get a 1 col, 1 row recordset.

However, if you have the capability to change the SP, then create an OUPUT paramer and return the value that way:

CREATE PROCEDURE [stp_Stop9Stat] 
       @Val int OUTPUT
 AS 

SELECT @Val = Val11 FROM tblConveyorB3 WHERE(Id=27)

GO

Then update your code to include the parameter:

Dim objCmd As New ADODB.Command
    With objCmd
        .CommandText = "stp_Stop9Stat"
        .ActiveConnection = DbSQLConn
        .CommandType = adCmdStoredProc
.Parameters.Add .CreateParameter("@Val", adinteger,adparamoutput)
        .Execute
msgbox .Parameters("@Val").Value
    End With

-tg

[crosbj]

tg,
Thanks, that worked. I didn't realize I had to define parameters for field values. I though since the stored procedure returned a row of data I could reference it like a recordset. Adding the OUTPUT parameter worked like a charm! Thanks again.

-jon