• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Converting VBA to TSQL help required!

Hi Experts Im converting some VBA code to TSQL, nearly there just having a problem with using functions.

Although the VBA I want to use is a bit more complex then what I want to use below in essence its what I want to achieve.

I know I can use insert/update statements in functions in TSQL, so I guess it must be a procedure with (multiple) return parameters.

Please some one help!
Sub psMy_Procedure()
Dim strName As String
strName = "Dave"
pfMy_ReuturnProcedure strName
MsgBox strName
End Sub

Function pfMy_ReuturnProcedure(strSirName As String)
strSirName = "Thorn"
'Then do some funky insert SQL statement here

End Function

Open in new window

1 Solution
MrDavidThornAuthor Commented:
Im thinking I need to use output parmeters in stored procdures. How do I use stored procduers with multple outputs

the question here is: What is your question?...:-)

 - you can use parameters declared with OUTPUT in a stored procedure which you can fill inside the procedure and then you will find the values after execution in the "Parameters" collection of the command object with which you filled and sent the command to execute the stored procedure.
 - you can use multiple result sets with adding some SELECT commands in your stored procedure which produces the output. Then you can read that with i.e. ADO and "NextRecordset" like demonstrated here: ADO NextRecordset Method Demonstration
 - you can use a XML parameter which you assemble in the stored procedure and then parse it in VBA


Kelvin SparksCommented:
You can create User Defined functions ( that have one or more input parameters) and return a value in exactly the same manner as you would create a VBA Function. These can be called in views, stored procedures etc..


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now