Creating dymanic stored procedure

[chicdog]

What I need to do is to create the stored procedure on the fly, and then once it is created, execute it and once execution is done, needs to be dropped. What I'm not sure of though is how to create it in ASP.NET using VB.NET. Any help would be appreciated. Thanks

 

[RichS]

I think you can do that by running dynamic sql like:

sql = "create proc sp_Address_Select as select * from Address"

and then drop it:

sql = "drop sp_Address_Select"

if memory serves. But my why do you want to do it this way?

 

[RichS]

Sorry - too early in the morning here I suppose
>>>But my why do you want to do it this way <<<<

Should be: My question is, why do you want to do it this way rather than just creating dynamic SQL in code and running it with the DataReader or DataAdapter, etc.

 

[chicdog]

Here's an example to better explain:
&quot;Select * From Mytable order by @Sort1,@Sort2,.....@Sort5&quot;
The @Sort are passed using all or some of the varibles. Is there a better way to do so?

 

[RichS]

You might try using a stored procedure that generates dynamic SQL. Something like:

create proc MyTable_Select_Sort
@SortString varchar(500)

as

declare @SQL varchar(500)

select @SQL = 'Select * from MyTable order by '

select @SQL = @SQL + @SortString

exec @SQL


From your asp.net page call this sp as you would any other and pass it the value @SortString

 

[chicdog]

The decision came from higher up the use Dynamic created stored prodcedures. The example gave me the idea that since the stored procedure will be used for the same thing, I'll just pass the sort string as a parameter. Thanks for the push in the right direction.