Dynamically create stored procedure in sql server from a vb.netwindows forms app

  • Thread starter Thread starter Karl Rhodes
  • Start date Start date
K

Karl Rhodes

Hi all, I have a Windows Forms application (VB.Net 2005) that connects
to a SQL Server 2005 Database.

When the application runs it checks the database for the existance of
a certain stored procedure and if it doesnt exist, it needs to create
it. The code in the VB app that is used to create the SP is shown
below...

Dim strSQL As String = ""
Dim oDT As New DataTable
strSQL = strSQL & "select * from INFORMATION_SCHEMA.ROUTINES where
ROUTINE_NAME like 'MyNewSP'"
Using oSQLConn As New
SqlConnection(ConfigurationManager.ConnectionStrings("MyDataStore").ConnectionString)
Using oDA As New SqlDataAdapter(strSQL, oSQLConn)
oDA.Fill(oDT)
If oDT.Rows.Count < 1 Then
Using oCmd As New SqlCommand
Dim strCreateSP As String = ""
strCreateSP = strCreateSP & "CREATE PROCEDURE MyNewSP ( " &
vbCrLf
strCreateSP = strCreateSP & "@Param1 Varchar(100) = Null, " &
vbCrLf
strCreateSP = strCreateSP & "@Param2 Varchar(100) = Null ) " &
vbCrLf
strCreateSP = strCreateSP & "AS " & vbCrLf
strCreateSP = strCreateSP & "BEGIN " & vbCrLf
strCreateSP = strCreateSP & "DECLARE @strSQL varchar(8000) " &
vbCrLf
strCreateSP = strCreateSP & "SET @strSQL = 'Select * from
tbl_MyTable " & vbCrLf
strCreateSP = strCreateSP & "WHERE FieldOne like <Param1> " &
vbCrLf
strCreateSP = strCreateSP & "AND FieldTwo Like <Param2> " &
vbCrLf
strCreateSP = strCreateSP & "ORDER BY FieldOne' " & vbCrLf
strCreateSP = strCreateSP & "SET @strSQL = replace(@strSQL,
'<Param1>', @Param1 ) " & vbCrLf
strCreateSP = strCreateSP & "SET @strSQL = replace(@strSQL,
'<Param2>', @Param2 ) " & vbCrLf
strCreateSP = strCreateSP & "EXEC (@strSQL) " & vbCrLf
strCreateSP = strCreateSP & "END"
oCmd.Connection = oSQLConn
oCmd.CommandType = CommandType.Text
oCmd.CommandText = strCreateSP
oCmd.Parameters.Clear()
oCmd.Parameters.AddWithValue("@Param1", "@Param1")
oCmd.Parameters.AddWithValue("@Param2", "@Param2")
Try
If oCmd.Connection.State <> ConnectionState.Open Then
oCmd.Connection.Open()
oCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Cannot continue!" & vbCrLf & vbCrLf & "Failed to create
the stored procedure.")
Me.Close()
End Try
End Using
End If
End Using
End Using


When I run this and check SQL Profiler I see the text "exec
sp_executesql N'" before the word "CREATE" and get the error
"incorrect syntax near the keyword 'PROCEDURE'"

Can anyone see where I'm going wrong, or does anyone know how to
dynamically create a stored procedure in a sql server database in this
way?

Thanks
Karl
 
Hello, Karl

Try to replace the following line:

oCmd.CommandText = strCreateSP

with this line:

oCmd.CommandText = "EXEC ('" & Replace(strCreateSP,"'","''") & "')"
 
First of all, you are using VB.NET 2005 and SQL Server 2005, so use the
features that they give you and simplfy your code somewhat. If you don't
then you are making a rod for your own back.

The code simplified would look like this:

Using _con As New
SqlConnection(ConfigurationManager.ConnectionStrings("MyDataStore").ConnectionString)
_con.Open()
Using _cmd As New SqlCommand("select count(*) from
INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME='MyNewSP'", _con)
If CType(_cmd.ExecuteScalar, Integer) = 0 Then
_cmd.CommandText = "CREATE PROCEDURE MyNewSP ( " & vbCrLf
_cmd.CommandText &= "@Param1 varchar(100)=Null, " & vbCrLf
_cmd.CommandText &= "@Param2 Varchar(100)=Null ) " & vbCrLf
_cmd.CommandText &= "AS" & vbCrLf
_cmd.CommandText &= "BEGIN " & vbCrLf
_cmd.CommandText &= "DECLARE @strSQL varchar(max) " & vbCrLf
_cmd.CommandText &= "SET @strSQL = 'Select * from tbl_MyTable WHERE
FieldOne like ''<Param1>'' AND FieldTwo Like ''<Param2>'' ORDER BY
FieldOne'" & vbCrLf
_cmd.CommandText &= "SET
@strSQL=replace(@strSQL,'<Param1>',@Param1)" & vbCrLf
_cmd.CommandText &= "SET
@strSQL=replace(@strSQL,'<Param2>',@Param2)" & vbCrLf
_cmd.CommandText &= "EXEC (@strSQL)" & vbCrLf
_cmd.CommandText &= "END"
Try
_cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Cannot continue!" & vbCrLf & vbCrLf & "Failed to create
the stored procedure.")
Close()
End Try
End If
End Using
End Using

Note that there is absolutely no need for strSQL, oDT, oDA and strCreateSP.

The fact that the stored procedure exists or not is easily ascertained by
executing a scalar ccommand that returns a count of the rows of interest.
The result will be 1 if it exists and 0 if it doesn't. There is no need to
wate resource filling a datatable.

Note that the CommandText property for the SqlCommand object is filled
directly. There is no need to 'stage' it via other temporary variables.

Note also that the SqlCommand object is reused, and again the CommandText
property is filled directly, first by overwriting and then by contenation.

There is no need to set any other properties of the SqlCommand object in
this case.

Note further that is inappropriate to supply any parameters to the creation
of the stored procedure. You supply parameters when you execute the stored
procedure.

Now, to the SQL itself ....

You are missing ' (single-quote) characters within your SQL so it is no
surprise that it failed. All you 'pretty' formatting makes it more difficult
to spot such things.

The biggest question of all is ....

Why the hell are you writing dynamic SQL, or even using a stored procedure
at all when a simple select ... statement will suffice?

The actual query is as simple as:

select
*
from
tbl_MyTable
where
FieldOne like @param1 and
FieldTwo like @param2
order by
FieldOne

So, there is no need to create stored procedures, check if they exist or
not, rather, the whole thing simply becomes:

Using _con As New
SqlConnection(ConfigurationManager.ConnectionStrings("MyDataStore").ConnectionString)
Using _da As New SqlDataAdapter("select * from tbl_MyTable where
FieldOne like @param1 and FieldTwo like @param2 order by FieldOne", _con)
Try
_da.Parameters.AddWithValue("@param1", "my value 1")
_da.Parameters.AddWithValue("@param2", "my value 2")
Dim _dt As New DataTable
_da.Fill(_dt)
' Do what you like with the resulting DataTable object
Catch ex As Exception
MsgBox("Cannot continue!" & vbCrLf & vbCrLf & "Failed to create the
stored procedure.")
Close()
End Try
End Using
End Using
 
Hi all, I have a Windows Forms application (VB.Net 2005) that connects
to a SQL Server 2005 Database.

When the application runs it checks the database for the existance of
a certain stored procedure and if it doesnt exist, it needs to create
it. The code in the VB app that is used to create the SP is shown
below...

Dim strSQL As String = ""
Dim oDT As New DataTable
strSQL = strSQL & "select * from INFORMATION_SCHEMA.ROUTINES where
ROUTINE_NAME like 'MyNewSP'"
Using oSQLConn As New
SqlConnection(ConfigurationManager.ConnectionStrings("MyDataStore").ConnectionString)
Using oDA As New SqlDataAdapter(strSQL, oSQLConn)
oDA.Fill(oDT)
If oDT.Rows.Count < 1 Then
Using oCmd As New SqlCommand
Dim strCreateSP As String = ""
strCreateSP = strCreateSP & "CREATE PROCEDURE MyNewSP ( " &
vbCrLf
strCreateSP = strCreateSP & "@Param1 Varchar(100) = Null, " &
vbCrLf
strCreateSP = strCreateSP & "@Param2 Varchar(100) = Null ) " &
vbCrLf
strCreateSP = strCreateSP & "AS " & vbCrLf
strCreateSP = strCreateSP & "BEGIN " & vbCrLf
strCreateSP = strCreateSP & "DECLARE @strSQL varchar(8000) " &
vbCrLf
strCreateSP = strCreateSP & "SET @strSQL = 'Select * from
tbl_MyTable " & vbCrLf
strCreateSP = strCreateSP & "WHERE FieldOne like <Param1> " &
vbCrLf
strCreateSP = strCreateSP & "AND FieldTwo Like <Param2> " &
vbCrLf
strCreateSP = strCreateSP & "ORDER BY FieldOne' " & vbCrLf
strCreateSP = strCreateSP & "SET @strSQL = replace(@strSQL,
'<Param1>', @Param1 ) " & vbCrLf
strCreateSP = strCreateSP & "SET @strSQL = replace(@strSQL,
'<Param2>', @Param2 ) " & vbCrLf
strCreateSP = strCreateSP & "EXEC (@strSQL) " & vbCrLf
strCreateSP = strCreateSP & "END"
oCmd.Connection = oSQLConn
oCmd.CommandType = CommandType.Text
oCmd.CommandText = strCreateSP
oCmd.Parameters.Clear()
oCmd.Parameters.AddWithValue("@Param1", "@Param1")
oCmd.Parameters.AddWithValue("@Param2", "@Param2")
Try
If oCmd.Connection.State <> ConnectionState.Open Then
oCmd.Connection.Open()
oCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Cannot continue!" & vbCrLf & vbCrLf & "Failed to create
the stored procedure.")
Me.Close()
End Try
End Using
End If
End Using
End Using

When I run this and check SQL Profiler I see the text "exec
sp_executesql N'" before the word "CREATE" and get the error
"incorrect syntax near the keyword 'PROCEDURE'"

Can anyone see where I'm going wrong, or does anyone know how to
dynamically create a stored procedure in a sql server database in this
way?

Thanks
Karl
Click to expand...

In many cases it is considered a very dangerous practice to run your
application under the account which can modify DDL,
 
Karl said:
When I run this and check SQL Profiler I see the text "exec
sp_executesql N'" before the word "CREATE" and get the error
"incorrect syntax near the keyword 'PROCEDURE'"

Can anyone see where I'm going wrong, or does anyone know how to
dynamically create a stored procedure in a sql server database in this
way?
Click to expand...

The exact reason for the error is that you pass parameters to the
command batch where you create the procedure. That does not fly. Nor
would there be any point to so so.

But I completely agree with Stephany Young's analysis. That stored
procedure is completely meaningless, so it's a good thing that
you are not able to create it.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
The exact reason for the error is that you pass parameters to the
command batch where you create the procedure. That does not fly. Nor
would there be any point to so so.

But I completely agree with Stephany Young's analysis. That stored
procedure is completely meaningless, so it's a good thing that
you are not able to create it.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Click to expand...

Thanks for your help everyone.
Of course the SP shown is a simple example of an SP. The actual SP
does perform a vital task on our servers and does need to be there. I
was simply having trouble getting the VB app to add it.
 
Thanks for your help everyone.
Of course the SP shown is a simple example of an SP. The actual SP
does perform a vital task on our servers and does need to be there. I
was simply having trouble getting the VB app to add it.- Hide quoted text -

- Show quoted text -
Click to expand...

Just on quick note to say thanks to Stepheny... youre example worked a
treat.

Karl
 
Back
Top