SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 WHERE Clause with wildcard in variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bwol
Starting Member

19 Posts

Posted - 07/07/2008 :  15:31:44  Show Profile  Reply with Quote
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

3 Posts

Posted - 07/07/2008 :  16:22:37  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 07/07/2008 :  16:39:54  Show Profile  Visit webfred's Homepage  Reply with Quote
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...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/08/2008 :  04:46:38  Show Profile  Reply with Quote
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 )

Go to Top of Page

bwol
Starting Member

19 Posts

Posted - 07/08/2008 :  10:32:44  Show Profile  Reply with Quote
Thanks to everyone who offered suggestions. I ended up using the code that visakh16 suggested, which worked great.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000