create stored procedure dynamically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Haiii!!!!
Is it possible to create stored procedure dynamically in C#.NET. Because
some of my requirements needs such a situation.
 
Yes, why not, if your database supports DDL (data definition language) or
your .net provider supports it.
 
Yes it is - exactly as Miha just described.

I want to add another peice of information - in Sql Server 2000 onwards,
Dynamic SQL isn't as evil as it used to be. The query plan is cached. So you
might get away with dynamic sql - instead of trying to code up a brand new
stored procedure at runtime. Given a choice though I still like s.procs, but
there are conflicting views on that between developers.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Hi Hazira,

Without DDL, here's some code you can use (vb .net - change to c# as
necessary):
Dim creationstring As String

If glf_custstat = "" Then

creationstring = "CREATE PROCEDURE sp_buildbranchlist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring

Else

creationstring = "CREATE PROCEDURE sp_buildbranchlist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring & " and custstat
= '" & glf_custstat & Chr(39)

End If

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

HTH,

Bernie Yaeger
 
Sahil said:
Yes it is - exactly as Miha just described.

I want to add another peice of information - in Sql Server 2000 onwards,
Dynamic SQL isn't as evil as it used to be. The query plan is cached. So you
might get away with dynamic sql - instead of trying to code up a brand new
stored procedure at runtime. Given a choice though I still like s.procs, but
there are conflicting views on that between developers.
Click to expand...

Query plans are also cached on SqlServer 7 btw :)

FB
 
Back
Top