WHERE clause dynamic, based on input parameters

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

General SQL Server Forums

New to SQL Server Programming

WHERE clause dynamic, based on input parameters

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-02-28 : 03:35:02

Hello all,
I have a stored procedure that get some data, like:

Select A.*
From TableA A inner join TableB B
on A.ID = B.ID

now I have to pass 3 optional parameters that compose the Where clause, like:

create stored procedure dbo.GetData
@Type1 int = null,
@Type2 int = null,
@Type3 int = null
as

Select A.*
From TableA A inner join TableB B
on A.ID = B.ID
Where B.Field1 = @Type1 and B.Field2 = @Type2 and B.Field3 = @Type3

but this only in the case that I have all parameters.
If I have just one or two, the Where clause has to change opportunely.

How can I make this Where dynamic?

Luigi

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 05:14:09

this is one of the few scenarios where dynamic sql is a good fit.

Here's the standard text on the subject: (Short and to the point)

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-02-28 : 05:44:34

Thank you Charlie.
I'll study this article.

Luigi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources