SQL Query

astrosathya · November 10th, 2010, 03:24 AM

Hi Techies,

Greetings,

I have a stored procedure which has a query [static] for which I need to append one variable [dyanamic] so that it is executed when SP is run.

Ex:

Declare @WhereCondition Varchar(500)
SET @WhereCondition = 'where id between 1 and 10'
"Select * from Some_Table" is my query

I want to append @WhereCondition to the above query in such a way that, when it is run the query should run like

"Select * from Some_Table Where id between 1 and 10"

PS: I dont want to use dynamic queries as my SP has got 100s of queries which will be a difficult task to change the entire procedure.

Kindly Help ASAP.

My Sincere Thanks well in advance. Thanks for your Valuable Time

Regards,
Sathya

Old Pedant · November 10th, 2010, 03:11 PM

Unless the *type* of the WHERE condition is fixed in advance, you have no choice but to use dynamic SQL inside the stored proc.

Code:

CREATE PROCEDURE foo (
    @Where VARCHAR(500)  
)
AS
DECLARE @sql VARCHAR(100)
SET @sql = 'SELECT * FROM some_table ' + @Where
EXEC( @sql )

Note that this is dangerous coding. You need to "sanitize" the @where value to be sure it isn't doing SQL Injection.

astrosathya · November 12th, 2010, 03:04 AM

Hi..

Thanks for your sincere reply for my post. Even I am left with that option only. Actually I was trying to see if there is any better option as my Stored Procedure has 100's of queries which have to be made dynamic now but Not so much time left with me..

melvik · November 28th, 2010, 06:38 AM

u can send ur table name by Parameter too if u wanna do it for many tables
but be aware of SQL Injection my friend