How to handle stored procedure with OUT result

[Stevenn]

I have a stored procedure which looks like:

PROCEDURE validate_upload
  ( p_delete IN NUMBER,
    p_budget_id IN  NUMBER,
    p_upload_id IN  NUMBER,
    p_user      IN  VARCHAR2,
    p_result    OUT VARCHAR2 )

,

but how do I run the procedure and how can I get the returned message?

I know it's something like

{CALL budget.validate_upload(1, 24, upload_id, 'user_id', ?)}

I am using ADODB in Excel 2007.

 

[Kyle123]

Does this help?

Public Function AddNote(user As String, Assignee As String, NoteTitle As Long, Notes As String) As Boolean

Dim cmd As ADODB.Command
Dim retlng As ADODB.Parameter

Set cmd = New ADODB.Command
Set retlng = New ADODB.Parameter

With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "usp_InsertNoteGeneric"
    .ActiveConnection = Parent.Connection.cn
    With retlng
        .Direction = adParamReturnValue
        .Name = "@RETURN_VALUE"
        .Type = adInteger
    End With
    .Parameters.Append retlng
    .Parameters.Append .CreateParameter("@MainTagbRef", adInteger, adParamInput, , Parent.ID)
    .Parameters.Append .CreateParameter("@ForceID", adVarChar, adParamInput, 6, PArent.FORCEID)
    .Parameters.Append .CreateParameter("@UserID", adVarChar, adParamInput, 50, user)
    .Parameters.Append .CreateParameter("@AssignedTo", adVarChar, adParamInput, 50, Assignee)
    .Parameters.Append .CreateParameter("@NoteContent", adLongVarChar, adParamInput, 1073741823, Notes)
    .Parameters.Append .CreateParameter("@NoteTitle", adInteger, adParamInput, , NoteTitle)
    .Parameters.Append .CreateParameter("@DateCreated", adDate, adParamInput, 0, Format(Now(), "yyyy/MM/dd hh:mm:ss"))
End With

cmd.Execute

If retlng.value > 0 Then
    AddNote = True
End If
End Function

 

[Stevenn]

So it's something like

Set TestCommand = New ADODB.Command

With TestCommand
 .ActiveConnection = ConnectionObject
 .CommandText = "CALL budget.validate_upload(...)"
 .CommandType = adCmdText
 .Parameters.Append .CreateParameter("Name??", adVarChar, adParamOutput, ?)
 .Execute
End With

I'm not quite sure if I understand it completely. What does each thing do?

 

[Kyle123]

Ok, not quite, I've commented up

Public Function AddNote(user As String, Assignee As String, NoteTitle As Long, Notes As String) As Boolean

Dim cmd As ADODB.Command
Dim retlng As ADODB.Parameter

'Create command Object
Set cmd = New ADODB.Command
'Create parameter
Set retlng = New ADODB.Parameter

With cmd
    'Set the command type to Stored Procedure
    .CommandType = adCmdStoredProc
    ' The name of the stored proc, in sql server would look something like
    ' usp_InsertNoteGeneric(123,456789,'B7534845','B7534845','Adding a new Note',14,'2012/05/01/ 22:00')
    .CommandText = "usp_InsertNoteGeneric"
    'Set the connection
    .ActiveConnection = Mother.Connection.cn
    'Create the parameter that will be passed out
    With retlng
        .Direction = adParamReturnValue 'A return paramter
        .Name = "@RETURN_VALUE" 'The name of the paramater in SQL
        .Type = adInteger 'The return type
    End With
    'Here we add the parameters to the command object
    .Parameters.Append retlng
    'For each paramter we must set it's SQL param name, its Type, Its direction, its size, and value
    ' command.CreateParameter (Name, Type, Direction, Size, Value)
    
    .Parameters.Append .CreateParameter("@MainTagbRef", adInteger, adParamInput, , Mother.ID)
    .Parameters.Append .CreateParameter("@ForceID", adVarChar, adParamInput, 6, Mother.FORCEID)
    .Parameters.Append .CreateParameter("@UserID", adVarChar, adParamInput, 50, user)
    .Parameters.Append .CreateParameter("@AssignedTo", adVarChar, adParamInput, 50, Assignee)
    .Parameters.Append .CreateParameter("@NoteContent", adLongVarChar, adParamInput, 1073741823, Notes)
    .Parameters.Append .CreateParameter("@NoteTitle", adInteger, adParamInput, , NoteTitle)
    .Parameters.Append .CreateParameter("@DateCreated", adDate, adParamInput, 0, Format(Now(), "yyyy/MM/dd hh:mm:ss"))
End With

'Execute the command
cmd.Execute

'Read the return Value
If retlng.value > 0 Then
    AddNote = True
End If
End Function

You need to create each variable you are sending, then append it to the parameters

 

[Stevenn]

But all I know is that it works with this SQL-string

{CALL budget.validate_upload(1, 24, upload_id, 'user_id', ?)}

. How can I use it in the code you've posted?

 

[Kyle123]

Youn need to know the data types. I take it you're using oracle?

 

[Kyle123]

I think you probably want something like this, I don't know if it will work, I'm not familiar with oracle datatypes especially varChar2

Public Sub CallSP()

Dim cmd As ADODB.Command
Dim OutStr As ADODB.Parameter

Set cmd = New ADODB.Command
Set OutStr = New ADODB.Parameter

With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "usp_InsertNoteGeneric"
    .ActiveConnection = cn 'Where cn is your connection
    With OutStr
        .Direction = adParamOutput
        .Name = "p_result"
        .Type = adVariant
    End With
    .Parameters.Append OutStr
    .Parameters.Append .CreateParameter("p_delete", adNumeric, adParamInput, , "Your Value")
    .Parameters.Append .CreateParameter("p_budget_id", adNumeric, adParamInput, , "Your Value")
    .Parameters.Append .CreateParameter("p_upload_id", adVariant, adParamInput, , "Your Value")
End With

cmd.Execute

Debug.Print OutStr.value

End Sub