WildCard Entries for SP parameter

[jenninbe]

Is there an easy way to enter a wildcard for a Stored Procedure Parameter (int or nvarchar)?

I have the following params:
@CompanyID int,
@DateFrom datetime,
@DateTo datetime,
@VendorID int,
@SiteID nvarchar,
@ServiceTypeID int

and criteria:
HAVING SubUtilCurrent.ClientID = @CompanyID AND
SubUtilCurrent.VendorID = @VendorID AND
SubUtilCurrent.[Site Number] = @SiteID AND
SubUtilCurrent.ServiceTypeID = @ServiceTypeID

A lot of times I won't have a value to enter for the Vendor, SiteID and ServiceTypeID..... and I just want to see all records.

TIA!!

Beau

 

[grahambo]

I've used this technique in where clauses before - I'm not sure if it'll work in a having clause or not, but you might try it:

HAVING
(@CompanyID IS NULL OR (SubUtilCurrent.ClientID = @CompanyID)) AND
(@VendorID IS NULL OR (SubUtilCurrent.VendorID = @VendorID)) AND
(@SiteID IS NULL OR (SubUtilCurrent.[Site Number] = @SiteID)) AND
(@ServiceTypeID IS NULL OR (SubUtilCurrent.ServiceTypeID = @ServiceTypeID))

If you supplied nulls for all the parameters, then you'd get back all the rows, or you could supply values for just the parameters you have (i.e. exec SP_NAME @CompanyID = null, @VendorID = 23, @SiteID = null, @ServiceTypeID = 2 )

 

[tlbroadbent]

I aggree with grahambo. This is a standard approach to this type of query. Some alternatives would be to use the CASE function or create dynamic SQL. grahambo's solution is far simpler and easier.

I recommend using the WHERE clause for selection criteria rather than HAVING. Having should only used to search for the results of aggregation. Where should be more efficient. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.