Dynamic WHERE Statement - SQL Server Forums

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 Statement

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2005-02-11 : 15:43:44

I'm using this code from an article on this site to create a dynamic WHERE clause for an ASP page.

CREATE PROCEDURE sp_DynamicSQL
(
@Ticker nvarchar(255) = NULL,
@CompanyName nvarchar(255) = NULL,
@Industry nvarchar(255) =NULL
)
AS
SELECT
Ticker,
CompanyName,
Industry
FROM TCompanies
WHERE
Ticker = COALESCE(@Ticker,Ticker) AND
CompanyName = COALESCE(@CompanyName, CompanyName) AND
Industry = COALESCE(@Industry,Industry)
GO

I need to search the @CompanyName variable like using LIKE '%@CompanyName%' but I don't know how to do this using COALESCE.

Something like this:

CompanyName = LIKE COALESCE(%@CompanyName%, CompanyName) AND

Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-11 : 16:47:19

I would do it more like:

where
(Ticker = @Ticker or @ticker is null)
and
(CompanyName like '%'+@companyName+'%' or @companyName is null)
and
(Industry = @Industry or @Industry is null)

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources