Creating SQL Server Stored Proc in VB

[Longia]

Hello Everybody

Need to know if I can create stored procedure from vb code. I need this because number of parameters in stored proc is not fixed, it varies as per my logic and every time I run my program I need different parameters so is there a way to create stored proc from VB program and drop it later.

Thx

 

[SonOfEmidec1100]

You can do this by executing the following sql
"create procedure sp_proc1 (@param1 int) As
select * from mytable"

or to drop the sp "drop procedure sp_proc1"

However Im sure this is not good practice and somewhat defeats the purpose of using stored procedures. There must be better ways to achieve what you want, could you not just set the parameters as optional and only pass the ones you need each time ?

cjw

 

[sunaj]

Hi,

You can make a parameter optional by adding a value to the definition:
--------------------------------------
Create procedure UP_MyStoredProc
@ID int,
@Hits int = NULL output
as ....
--------------------------------------
In this case it is an optional output parameter, but it work in the same way with input parameters.
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'