Converting VBA to TSQL help required!

Posted on 2011-10-17
Last Modified: 2012-05-12
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

Question by:MrDavidThorn

    Author Comment

    Im thinking I need to use output parmeters in stored procdures. How do I use stored procduers with multple outputs
    LVL 24

    Accepted Solution


    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


    LVL 22

    Expert Comment

    by:Kelvin Sparks
    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..


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now