Return from Stored Procedure 2

[scking]

I'm trying to find the solution to using a stored procedure to return a result into a VB variable. The procedure below appears to compile and runs great using query analyzer but I'm unaware of how to get the result into VB.

' VISUAL BASIC USING AN ADODB.CONNECTION OBJECT
------------------------------------------------------
cnn.Execute "EXEC spPnExists @PN:=" & CStr(Me.txtOldPN) & ", @Cage:=" & CStr(Me.txtOldCage) & ", @Exists:=" _
& lngRecs & " OUTPUT"


' VISUAL BASIC USING AN ADODB.COMMAND OBJECT
------------------------------------------------------
cnn.Execute "EXEC " & return_status = " dbo.spPnExists '" & Me.txtOldPN & "', '" & Me.txtOldCage & "'"


ALTER PROCEDURE spPnExists
(
@PN nvarchar(32),
@Cage nvarchar(7),
@Exists integer OUTPUT
)
AS
IF EXISTS (SELECT * FROM tblPN WHERE PN=@PN And Cage=@Cage)
SET @Exists = 1
ELSE
SET @Exists = 0

RETURN @Exists

Any help would be appreciated greatly.


---------------------
scking@arinc.com
---------------------

 

[hneal98]

The easiest way would be to have your stored procedure create a table and put the value there. Then you just open a record set based on a query you run against the table. When you are done, you delete the table and there is no overhead.

 

[Dav1e]

You could try this,

Rather than using the Return statement ammend it like this:

IF EXISTS ("Your query&quot
SET @Exists = 1
ELSE
SET @Exists = 0

SELECT @Exists AS 'My Column Name'

Then use a Recordset object in VB to capture back the value returned as column 'My Column Name'

Hope this helps,

David

 

[TysonLPrice]

Here's how I return values. Sorry about the choppy way it's pasted in. Basically set up an output parm in VB and Set it in the stored procedure. Param5 is the output parameter.

In Visual Basic:

Dim cmd As New ADODB.Command
    Dim param1 As New ADODB.Parameter
    Dim param2 As New ADODB.Parameter
    Dim param3 As New ADODB.Parameter
    Dim param4 As New ADODB.Parameter
    Dim param5 As New ADODB.Parameter

    With cmd
        .CommandText = "TrackingImportClaimant"
        .CommandType = adCmdStoredProc
        .ActiveConnection = cnCMS
        Set param1 = .CreateParameter("UserID", adInteger,  adParamInput, 4, iLoginID)
        .Parameters.Append param1
        Set param2 = .CreateParameter("SaveClaimID", adInteger, adParamInput, 1, iClaimID)
        .Parameters.Append param2
        Set param3 = .CreateParameter("WebReferralID", adInteger, adParamInput, 1, iReferralID)
        .Parameters.Append param3
        Set param4 = .CreateParameter("ClaimantID", adInteger, adParamInput, 4, iClaimantID)
        .Parameters.Append param4
        Set param5 = .CreateParameter("ReturnClaimantID", adInteger, adParamOutput, 4)
        .Parameters.Append param5
        
        .Execute
        
        iClaimantID = param5.Value

In the stored procedure:

CREATE PROCEDURE TrackingImportClaimant @UserID as Integer, @SaveClaimID as Integer, @WebReferralID as Integer, @ClaimantID as integer, @ReturnClaimantID as integer output AS

 

[scking]

I successfully used the ADO parameters solution recommended by TysonLPrice to create a function. I didn't realize I needed to create and append the parameters since they already existed in the stored procedure. Works great. Thanks!

Steve King

---------------------
scking@arinc.com
---------------------

 

[cyberbiker]

Hope it is not too late to give Tyson a star.
His post cleared up some concepts I was struggling with

Terry (cyberbiker)

 

[scking]

I second the motion. It was simply an oversight on my part. Thanks again Tyson.

---------------------
scking@arinc.com
---------------------