SQL conditional where Clause executing using querystring parameters - If function needed?

Question

Hello all, me again!

I have the following sp that uses a condtional where clause - ish!

@MemberId  varchar(7), 
@LocationId varchar(8),
@UUF1 varchar(150),
@UUF2 varchar(50),
@UUF4 varchar(50),
@IDDesc varchar(255),
@OwnBrand nvarchar(50),
@WeightStatus varchar(50),
@MaterialLevel varchar(10),
@BaseMaterialName varchar(15),
@ExtendedMaterialName varchar(50),
@PackagingTypeCode varchar(20),
@Biodegradable nvarchar(50),
@Recyclability varchar(30),
@Feedback varchar(255)

AS

SELECT     MemberId, MemberName, LocationId, IDDesc, UnitId, UnitDescription, UnitUserField1, UnitUserField2, UnitUserField4, IsOwnBrand, OwnBrand, 
                      UnitUserField10, SaleDate, SaleQty, LevelNo, WeightStatus, RevisionSourceCode, RevisionDate, MaterialLevel, BaseMaterialID, BaseMaterialName, 
                      ExtendedMaterialId, ExtendedMaterialName, PackagingTypeCode, UnitWeight, WeightUnitCode, IsBiodegradable, Biodegradable, RecycledContent, 
                      Recyclability, Tonnage, ProductPercentage, Productpriority, Feedback, PriKey
INTO            #tblSPPartI
FROM         tblWeights
WHERE     ((MemberId = @MemberID) OR (@MemberId IS NULL)) 
AND ((LocationId = @LocationID) OR (@LocationID IS NULL))  
AND ((UnitUserField1 = @UUF1) OR (@UUF1 IS NULL)) 
AND ((UnitUserField2 = @UUF2) OR (@UUF2 IS NULL))  
AND ((UnitUserField4 = @UUF4) OR (@UUF4 IS NULL)) 
AND ((IDDesc= @IDDesc) OR (@IDDesc IS NULL))  
AND ((OwnBrand = @OwnBrand) OR (@OwnBrand IS NULL)) 
AND ((WeightStatus = @WeightStatus) OR (@WeightStatus IS NULL)) 
AND ((MaterialLevel = @MaterialLevel) OR (@MaterialLevel IS NULL)) 
AND ((BaseMaterialName = @BaseMaterialName) OR (@BaseMaterialName IS NULL)) 
AND ((ExtendedMaterialName = @ExtendedMaterialName) OR (@ExtendedMaterialName IS NULL)) 
AND ((PackagingTypeCode = @PackagingTypeCode) OR (@PackagingTypeCode IS NULL)) 
AND ((IsBiodegradable = @Biodegradable) OR (@Biodegradable IS NULL)) 
AND ((Recyclability = @Recyclability) OR (@Recyclability IS NULL)) 
AND ((Feedback = @Feedback) OR (@Feedback IS NULL))

When I test this in Sql, it works beautifully.

However, I use this in a web app context where the parameters are set using a querystring.

A query string will never be sent as null in this context - if a user has not selected a parameter, the querystring for the parameter will be "Not Specified".

Consequently, when the query string parameters are passed into the above SQL, the parameter value returned is "Not Specified" as opposed to what I require i.e. a null value.

How would I manipulate (a) either the code above so that the "Not Specified" is taken into account or (b) would I need to change my html/c# codebehind markup?

Apologies if this doesn't make sense.

Answer 1

You can use COALESCE-

COALESCE(@Feedback, 'Not Specified')

This will return you 'Not Specified' when @feedback is NULL.

for your example it will be -

....WHERE     ((MemberId = COALESCE(@MemberID, 'Not Specified'))  
AND ((LocationId = COALESCE(@LocationID, 'Not Specified'))   
AND ((UnitUserField1 = COALESCE(@UUF1, 'Not Specified')) .....