Code a dynamic query in a stored procedure.

muffirulz · Posted: Fri Jun 15, 2007 1:29 am

Hi,
I would like to know if we can code a dymanic select query in Stored proceudres. I have a requirement to have a query that has 7 variables in its where clause in which n3 are fixed and the other 4 are not fixed, ie they may or may not be populated. Inside the stored procedure at run time I would like to build query which will only contain the variables in where clause that are populated omitting the other ones.

Please can anyone help me out with this.

Thanks,

trevor_rebelo · New User Joined: 18 Aug 2005 Posts: 17

Hi,

Check the variables and accordingly prepare the SQL text dynamically in the SP.
then prepare and execute the query.

just the way you write dynamic SQL.

Regards

Trevor