Stored Procedures ...When?

<i><b>Originally posted by : Tejal (Shah)</b></i><br /><br />Hi,<br /> I am working on a shopping mall site. I have used sequel queries in my asp. My queries have varying conditions. This query can have 1 to 20 conditions which means conditions are formed at run time. Hence , do you think stored procedures should be used in such a situation where the condition itself is not fixed.<br />If so, how to use it?<br />I know the advantages of stored procedure.<br />Please guide me<br /><br />Thanks,<br />Tejal

<i><b>Originally posted by : tantulus (tantulus@my-deja.com)</b></i><br />This is something I am working on at the moment.<br />It seems for dynamically built SQL statements the best way to put them into a stored procedure is to use:<br /><br />CREATE PROCEDURE [proc_get_details]<br />@OrderBy char(10)<br />AS<br />begin<br />declare @sql varchar(100)<br />set @sql = 'SELECT Name FROM table1 order by ' + @OrderBy + 'asc ' exec( @sql )<br />end<br /> <br />This doesn't really take advantage of the compiled stored procedure. Cause it has reconstruct the statement every time. What I am trying to do is make it dynamic by passing a few varible in and then using the SQL IF statement to determine which SQL statement to use.<br /><br />Hope this helps,<br />Tantulus<br /><br /><br />------------<br />Tejal at 7/6/2000 5:53:58 AM<br /><br /><br />Hi,<br /> I am working on a shopping mall site. I have used sequel queries in my asp. My queries have varying conditions. This query can have 1 to 20 conditions which means conditions are formed at run time. Hence , do you think stored procedures should be used in such a situation where the condition itself is not fixed.<br />If so, how to use it?<br />I know the advantages of stored procedure.<br />Please guide me<br /><br />Thanks,<br />Tejal