Avatar of Kurt Bergman
Kurt Bergman
 asked on

Returning Value from Stored Procedure

I've found MANY articles but have yet to solve this one:

SP:
ALTER PROCEDURE [dbo].[ElementInRange] 
(
	@HighLow as int OUTPUT, 
	@Element varchar(2), 
	@Amount real, 
	@Material varchar(60)
)

AS
SET NOCOUNT ON

SELECT @HighLow = COALESCE( 
	CASE WHEN ElementMin > @Amount THEN -1 END,
	CASE WHEN ElementMax < @Amount Then 1 END
	)
FROM tblElements
WHERE Material = @Material AND Element = @Element AND ElementMin <> -10000 AND ElementMax <> -10000

Open in new window


Running the SP:
DECLARE	@return_value int,
		@HighLow int

SELECT	@HighLow = -1

EXEC	@return_value = [dbo].[ElementInRange]
		@HighLow = @HighLow OUTPUT,
		@Element = N'Cr',
		@Amount = 28,
		@Material = N'2205'

SELECT	@HighLow as N'@HighLow'

SELECT	'Return Value' = @return_value

Open in new window


Result is -1 which what it should be

MS Access Function:
Public Function RunSP(StoredProcedureName As String, ParamString As Variant, DataType As Variant, ParamValue As Variant)
         Dim ADOCon  As New ADODB.Connection
         Dim ADORs  As ADODB.recordset
         Dim ADOPrm As ADODB.Parameter
         Dim strSQL As String
         Dim stMessage As String
         Dim fld As ADODB.Field
         Dim ADOCmd As ADODB.Command
         Dim sParmName As String
         Dim strReturn As String
         Dim MyOutput As String
         
         
         Dim strPWD As String
10       On Error GoTo ErrorHandler
20       strPWD = [MyPassword]  
30       AppState.ConnectionString = "driver={SQL Server};server=[MyServer];database=[MyDatabase];uid=[MyUserName];pwd=" & strPWD
         
40       Set ADOCon = New ADODB.Connection
         
50       With ADOCon
60          .Provider = "MSDASQL"
70          .CursorLocation = adUseServer
80          .ConnectionString = AppState.ConnectionString
90          .Open
100      End With
         
110      Set ADOCmd = New ADODB.Command
         
120      Set ADOCmd.ActiveConnection = ADOCon
130      With ADOCmd
140         .CommandType = adCmdStoredProc
150         .CommandText = StoredProcedureName
160      End With

170      MyOutput = "HighLow"
180      Set ADOPrm = ADOCmd.CreateParameter(MyOutput, adInteger, adParamOutput, , 0)
190      ADOCmd.Parameters.Append ADOPrm

200      Set ADOPrm = ADOCmd.CreateParameter(ParamString(0), adVarChar, adParamInput, 20)
210      ADOCmd.Parameters.Append ADOPrm
220      ADOCmd.Parameters(ParamString(0)).Value = ParamValue(0)

230      Set ADOPrm = ADOCmd.CreateParameter(ParamString(1), DataType(1), adParamInput, 255)
240      ADOCmd.Parameters.Append ADOPrm
250      ADOCmd.Parameters(ParamString(1)).Value = ParamValue(1)
         
260      Set ADOPrm = ADOCmd.CreateParameter(ParamString(2), DataType(2), adParamInput)
270      ADOCmd.Parameters.Append ADOPrm
280      ADOCmd.Parameters(ParamString(2)).Value = ParamValue(2)
        
        Dim I As Integer
        Dim rstr As String
        
290     ADOCmd.Execute

300    Debug.Print ADOCmd.Parameters(0)
       

310      On Error GoTo 0
320      Exit Function

ErrorHandler:
         Dim strErrMsg As String
330      strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
         "In procedure: RunSP of modStoredProcedure" & vbCrLf & _
         "Error Line: " & Erl
      '
340      Debug.Print strErrMsg

End Function

Open in new window


Immediate Window:
?runsp("ElementInRange",Array("Element","Amount","Material"),Array(10,3,18),Array("Cr",22,"2205"))

Open in new window


Stored Procedure works as expected and returns '-1' as the result as expected
Function returns Null


Stumped on how to get the return value
Microsoft Access

Avatar of undefined
Last Comment
Kurt Bergman

8/22/2022 - Mon
chaau

All you need to do is to assign the return value of the RunSP function to the ADOCmd.Parameters(0). Put this line to the near the end of the RunSP function:
RunSP = ADOCmd.Parameters(0).Value

Open in new window

Kurt Bergman

ASKER
hi chaau,
it's still returning null
ASKER CERTIFIED SOLUTION
chaau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Kurt Bergman

ASKER
YAY!!!
you rock Chauu
Can I go home now?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23