Dynamic Where Condition in Stored procedures.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Where Condition in Stored procedures.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-15 : 10:32:24

Santosh writes "Hello ,
Please help me in solving following question.
I have filter developed in ASP from which user selects different conditions.
All these conditions are stored in a variable. Now how do I pass this variable to a stored procedure and append it to where clause?
E.g: Assume I have two tables
T1 -- Columns - A1,B1
T2 -- Coulmns - C1,D1

Now my variable holds a String as
Sql_Where_Clause = "T1.A1='dd' OR T1.B1='HH' AND T2.C1='ff'"

Now I would like to append this to a SQL Query in a Stored Procedure by passing this Sql_Where_Clause

e.g Select * from T1,T2 where @Sql_Where_Clause

If I do this , this is not working w.r.t. Stored Procedures.

So please explain me how to handle such conditions.

Thanks,
Santosh"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-15 : 10:40:34

Look for help on dynamic SQL (sp_executesql )

Basically you need:
DECLARE @sql nvarchar(4000)
SELECT @SQL ="Select * from T1,T2 where " & @Sql_Where_Clause
EXEC sp_executesql @SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources