Stored procedure that filter on optional parameter

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 2005 Forums

Transact-SQL (2005)

Stored procedure that filter on optional parameter

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-10-01 : 04:00:05

I would like to create a stored procedure that can execute a query and filter based on 3 parameters, but there might be just 1 or 2 of the parameters that actually contains any data that can be used to filter the query.

something like this...

@Model nVarChar(50) = NULL, -- Optional
@Brand nVarChar(50) = NULL, -- Optional
@CountryID Int = NULL -- Optional

SELECT * FROM tbl_Cars WHERE -- here I want to filter on parameters that is passed in and contains data.

Is this possible?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-01 : 04:02:05

you mean....

where (Model = @model or @model is null)
and (brand = @brand or @brand is null)
and (countryid = @countryid or @countryid is null)

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 04:05:41

You have been well trained, padawan

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 04:16:37

@Model nVarChar(50) = NULL, -- Optional
@Brand nVarChar(50) = NULL, -- Optional
@CountryID Int = NULL -- Optional


SELECT * FROM tbl_Cars 
WHERE Model=COALESCE(@Model,Model)
AND Brand=COALESCE(@Brand,Brand)
AND CountryID=COALESCE(@CountryID,CountryID)
GO

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-01 : 04:18:49

quote:
Originally posted by Peso

You have been well trained, padawan

E 12°55'05.63"
N 56°04'39.26"

are you my father??

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-10-01 : 04:59:20

Works brilliant! Thank you very much all of you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources