Dynamic procedures, are they possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Dynamic procedures, are they possible?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2005-10-27 : 10:10:50

Please excuse my ignorance but I'm new to T-SQL and am having a problem. I have a proc that I want to send three parameters too, but only have it search on the paramters if they are valid (above 0).
In the following example I am filtering on County, Town and Type. But only want to filter if the value of the parameter is included. So if they only pass in county, then thats all I want to filter on.

Any ideas?


CREATE PROCEDURE FeatureBusinessByTown @County int, @Town int, @Type int AS

Select Top 5 BusinessID
FROM SELECT TOP 5 fbisBusinessLookup.BusinessID
 FROM fbisBusinessLookup INNER JOIN
 DIRECTORY ON fbisBusinessLookup.BusinessID = directory.[id]
 WHERE 

IF (@county > 0) THEN "(fbisBusinessLookup.countyid = @County)"
IF (@Town > 0) THEN " AND fbisBusinessLookup.TownID = @Town"
IF (@Type>0) THEN " AND fbisBusinessLookup.TypeID = @Type"

 ORDER BY NEWID()) DERIVEDTBL
GO

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-27 : 10:59:50

Do you mean to say that county, town and type are identity values? Forget 0, and just use Null

WHERE countyid = COALESCE(@county,countyid)
AND TownID = COALESCE(@Town, TownID)
AND TypeID = COALESCE(@Type, TypeID)

Guess you could also use CASE

WHERE countyid = CASE WHEN @county = 0 THEN countyid ELSE @County END
AND TownID = CASE WHEN @Town = 0 THEN TownID ELSE @Town END
AND TypeID = CASE WHEN @Type = 0 THEN TypeID ELSE @Type END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources