Using The SQLDMO Reference To Create SQL Stored Procedures On-The-Fly And Execute

I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object.

 Here are some clipits of what you need.

Imports SQLDMOPrivate myServer As New SQLServer

Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString()

Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString()

Dim storedProcedure As New StoredProcedure

 Dim qresults As SQLDMO.QueryResults

Example Stored Procedure

Try

myServer.Connect(serverName, userName, password)

storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)

storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)

Label1.Text = "Creation of stored procedure successful"Catch ex As Exception

Label1.Text = "Creation of stored procedure failed"

Finally

myServer.DisConnect()End Try

 Run Stored Procedure and Obtain Results

myServer.Connect(serverName, userName, password)

qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows

MemberRowNo = qresults.GetColumnString(num, 1)

Next

myServer.DisConnect()