We help IT Professionals succeed at work.

Returning Value from Stored Procedure

Kurt Bergman
Kurt Bergman asked
on
197 Views
Last Modified: 2014-11-26
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2013

Commented:
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

Author

Commented:
hi chaau,
it's still returning null
CERTIFIED EXPERT
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
YAY!!!
you rock Chauu
Can I go home now?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.