WHERE Clause with wildcard in variable

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 with wildcard in variable

Author

Topic

bwol
Starting Member

19 Posts

Posted - 2008-07-07 : 15:31:44

I currently am using the MSSQL stored procedure below, which is called by a vb.net page that pull values from drop down lists and uses those variables for the input variables in the stored procedure.

Currently the drop down lists are populated with all of the current values stored in the respective database fields (i.e. there is an EmployerID drop down list that is populated with all of the EmployerIDs in my reports table).

However, I would like to add a feature where the results of the query are limited based on on only some of the four variables instead of all four.

How would I modify my stored procedure, such that a value of "*" (or some other value indicating a wildcard) for the each variable would result in the results of the query not being limited based on the value of that field?

Any help is much appreciated.

CREATE PROCEDURE ReportSelect

(
@SenderID int,
@EmployerID int,
@ReportPeriod varchar(20),
@ReportType varchar(50)
)

AS

SELECT ReportID, ReportType, ReportPeriod, EmployerName
FROM Reports
WHERE SenderID = @SenderID AND EmployerID = @EmployerID AND ReportPeriod = @ReportPeriod AND ReportType = @ReportType)

TalMcMahon
Starting Member

4 Posts

Posted - 2008-07-07 : 16:22:37

I think what your looking for is '%' the percent sign is a wildcard.

you probobly want a ColumnName like @param not an equals though

Hope that helps

Tal

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-07 : 16:39:54

For example your variable @SenderID is an asterix:

where SenderID = case @SenderID when '*' then SenderID else @SenderID end

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 04:46:38

i think what you need is to define default values for all parms and if value is default you wont be apperaing thet particular filter. for example let default for parameters be null then you can do like

CREATE PROCEDURE ReportSelect

(
@SenderID int = NULL,
@EmployerID int =NULL,
@ReportPeriod varchar(20)=NULL,
@ReportType varchar(50)=NULL
)

AS

SELECT ReportID, ReportType, ReportPeriod, EmployerName
FROM Reports 
WHERE SenderID = COALESCE(@SenderID,SenderID) 
AND EmployerID = COALESCE(@EmployerID,EmployerID) 
AND ReportPeriod = COALESCE(@ReportPeriod,ReportPeriod)
AND ReportType = COALESCE(@ReportType,ReportType )

bwol
Starting Member

19 Posts

Posted - 2008-07-08 : 10:32:44

Thanks to everyone who offered suggestions. I ended up using the code that visakh16 suggested, which worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources