	AllExperts > Visual Basic
	Search

About Ravindra
Expertise
visual basic application programming from design to access information, sql, engineering and commercial applications. access databases, excel.(VB6) basic questions in vb 2008. Optional: 1)a large number of people want me to do work which takes some time and effort. can do projects in vb 2008. Pl.note that i would like to be paid for such work. 2) if you want me to spend quality time and do special work, i expect to be paid a reasonable price for my time. 3) if you are pleased with my reply you could consider a donation of 1$. 4) you can visit my website http://ravindra.coolpage.biz

Experience

Education/Credentials
engineering, management degrees.

Past/Present Clients
project work for a Norway company,USA company and a Canadian company completed. Freelance Project work and Teaching. helping programmers with their work
teaching vb

You are here: Experts > Computing/Technology > Basic > Visual Basic > Calling MSSQL Stored Procedure from VB

Visual Basic - Calling MSSQL Stored Procedure from VB

Expert: Ravindra - 2/22/2005

Question
Hi Ravindra,
  How do I call a SQL stored procedure from VB6 and store the return value in a variable? This stored Procedure generates sequencial numbers that i need to pass to a variable. Thanks much.

Answer
The following Visual Basic code connects to a SQL Server, creates a stored procedure that has a return parameter, one input parameter, one input/output parameter and one output parameter, runs the stored procedure, and retrieves the output parameter values:

Start a new Project. Form1 is created by default.
Under the Tools - References menu, select the proper DAO reference.
Add a Command Button to the form.
In the Command1_Click() event, add the following code:
   Private Sub Command1_Click()

      Dim db As Database
      Dim rs As Recordset
      Dim strConnect As String
      Dim strSQL As String
      Dim strResult As String

      'Set connect string.
      strConnect = "ODBC;DSN=DSN_SP_TEST;"

      'Open the database
      Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, _
                                                   strConnect)

      'Check to see if sp exists and delete it if it does.
      strSQL = "if exists "
      strSQL = strSQL & "(SELECT * from sysobjects "
      strSQL = strSQL & "WHERE id = object_id('dbo.sp_ReturnParams') "
      strSQL = strSQL & "AND sysstat & 0xf = 4) "
      strSQL = strSQL & "DROP PROCEDURE dbo.sp_ReturnParams"
      MsgBox strSQL
      db.Execute strSQL, dbSQLPassThrough

      'Create the stored procedure. It really does not do much.
      'It simply sets the variables and returns
      strSQL = "/****** Object: Stored Procedure dbo.sp_ReturnParams */"
      strSQL = strSQL & vbCrLf & "CREATE PROCEDURE dbo.sp_ReturnParams"
      strSQL = strSQL & vbCrLf & "/* Declare Parameters */"
      strSQL = strSQL & vbCrLf & " @intInput       int = Null, "
      strSQL = strSQL & vbCrLf & " @strInputOutput varchar(20) = " _
                                                          & "Null OUTPUT,"
      strSQL = strSQL & vbCrLf & " @strOutput      varchar(20) = " _
                                                          & "Null OUTPUT"
      strSQL = strSQL & vbCrLf & "AS"
      strSQL = strSQL & vbCrLf & "BEGIN"
      strSQL = strSQL & vbCrLf & " SET NOCOUNT ON /* stops messages */"
      strSQL = strSQL & vbCrLf & " DECLARE @intReturn int " _
                                                  & "/* Declare return */"
      strSQL = strSQL & vbCrLf & " SELECT @intReturn = 43 " _
                                                  & "/* Set the return */"
      strSQL = strSQL & vbCrLf & " SELECT @strInputOutput = " _
                                                  & "'Param InOut Test'"
      strSQL = strSQL & vbCrLf & " SELECT @strOutput = 'Param Out Test'"
      strSQL = strSQL & vbCrLf & " RETURN @intReturn"
      strSQL = strSQL & vbCrLf & "END"
      MsgBox strSQL
      db.Execute strSQL, dbSQLPassThrough

      'Get ready to call procedure.
      'Declare the variables in SQL and then set them
      'in SQL code. Then retrieve the values as a recordset.
      strSQL = "SET NOCOUNT ON /* stops unwanted messages */"
      strSQL = strSQL & vbCrLf & "/* Declare the variables */"
      strSQL = strSQL & vbCrLf & "DECLARE @intIn       int"
      strSQL = strSQL & vbCrLf & "DECLARE @strInOut     varchar(20)"
      strSQL = strSQL & vbCrLf & "DECLARE @strOut      varchar(20)"
      strSQL = strSQL & vbCrLf & "DECLARE @intRet      int"
      strSQL = strSQL & vbCrLf & "/* Populate In Parameters */"
      strSQL = strSQL & vbCrLf & "SELECT @intIn = 5"
      strSQL = strSQL & vbCrLf & "SELECT @strInOut = 'In/Out In'"
      strSQL = strSQL & vbCrLf & "/* Execute the procedure */"
      strSQL = strSQL & vbCrLf & "EXECUTE @intRet = sp_ReturnParams "
      strSQL = strSQL & "@intIn, @strInOut OUTPUT, @strOut OUTPUT"
      strSQL = strSQL & vbCrLf & "/* Select back the records */"
      strSQL = strSQL & vbCrLf & "SELECT @intRet AS param1, @intIn "
      strSQL = strSQL & "AS param2, @strInOut AS param3, @strOut AS _
                                                       param4"
      Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)

      'Display the data in the parameters in a messagebox
      strResult = "intReturn = " & rs.Fields("param1")
      strResult = strResult & vbCrLf & "intInPut = " & rs.Fields("param2")
      strResult = strResult & vbCrLf & "strInPutOutput = " & _
                                                       rs.Fields("param3")
      strResult = strResult & vbCrLf & "strOutput = " & _
                                                       rs.Fields("param4")
      MsgBox strResult
   End Sub


In the ODBC Administrator applet, create a DSN named DSN_SP_TEST that connects to an SQL Server that you have the necessary rights to.
Run the project. The code should connect to the server, create a stored procedure named dbo.sp_ReturnParams, execute it passing in some values, and display a message box with the return parameters.
Ravindra M.G.

Ask a Question