Kurt Bergman
asked on
Returning Value from Stored Procedure
I've found MANY articles but have yet to solve this one:
SP:
Running the SP:
Result is -1 which what it should be
MS Access Function:
Immediate 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
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
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
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
Immediate Window:
?runsp("ElementInRange",Array("Element","Amount","Material"),Array(10,3,18),Array("Cr",22,"2205"))
Stored Procedure works as expected and returns '-1' as the result as expected
Function returns Null
Stumped on how to get the return value
ASKER
hi chaau,
it's still returning null
it's still returning null
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YAY!!!
you rock Chauu
Can I go home now?
you rock Chauu
Can I go home now?
Open in new window