CODEWITHSUNDEEP
sqlstored-procedures
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

Simple SQL Query Help

I am building a query for a search in MS SQL 05

i have 4 things the user can select, and i want to use AND logic on it.

but i can't seem to get it to work when NULLs are being passed in.

this is what i have:

ALTER PROCEDURE [dbo].[sp_FindSource] 
    -- Add the parameters for the stored procedure here
 @Code varchar(500),
  @ssid varchar(50),
  @serialNo varchar(50),
  @category decimal(10,5)


as begin
SELECT *
FROM tblSource 
WHERE Code IN (
            SELECT Value
            FROM funcListToTableInt(@Code,',')
                   )
and SSID LIKE '%' + @ssID + '%'
and serialNo LIKE '%' + @serialNo + '%'
and category = @category




end

NOTE: funcListToTableInt function, parses comma seporated values passed in (it works by itself, if i take the other where statements out)

The above search never returns anything, how can i ignore values if they are passed in black and only query the ones that have something in them? uuggh, it's been killing me.

Upvotes: 1

Views: 191

Answers (3)

Nitin Midha
Nitin Midha

Reputation: 2268

There are two ways:

Way 1, ADD OR Clause: It can kill the performance ....

SELECT *
FROM tblSource 
WHERE (Code IN (
            SELECT Value
            FROM funcListToTableInt(@Code,',')
                   ) OR @Code IS NULL)
and (SiteSourceID LIKE '%' + @ssID + '%' OR @SSID IS NULL)
and (serialNo LIKE '%' + @serialNo + '%' OR @serialNo IS NULL)
and (category = @category OR @category)

Way 2: Conditional logic Considering you have 4 parameters and each may have a value or may not have a value, So you have 2*2*2*2 , 16 different cases. You can write something like:

IF (@SSID IS NULL AND @Code IS NULL AND @serialNo IS NULL AND @category  IS NOT NULL) THEN
-- SEARCH only of category
ELSE IF (@SSID IS NULL AND @Code IS NULL AND @serialNo IS NOT NULL AND @category  IS  NULL) THEN
-- SEARCH only on Serial Number

.
.
.
.
.

As in SQL Server each If block cache its own plan, it will be more performing but based on parameter and there possible combinations this approach may or may not be desired ...

Upvotes: 0

JonnyBoats
JonnyBoats

Reputation: 5187

Try adding OR clauses for the nulls. SO for example change 'and category = @category' to 'and ((category = @category) or (category is null)).

Do this for all the items for whiuch you want to have a null imput essectially disable that particular test.

Upvotes: 1

HardCode
HardCode

Reputation: 6756

You just have to wrap some OR @param IS NULL checks around your WHERE conditions:

ALTER PROCEDURE [dbo].[sp_FindSource] 
    -- Add the parameters for the stored procedure here
 @Code varchar(500),
  @ssid varchar(50),
  @serialNo varchar(50),
  @category decimal(10,5)


as begin
SELECT *
FROM  tblSource 
WHERE (Code IN (SELECT Value FROM funcListToTableInt(@Code,',')) OR @Code IS NULL)
      AND (SiteSourceID LIKE '%' + @ssID + '%' OR @ssID IS NULL)
      AND (serialNo LIKE '%' + @serialNo + '%' OR @serialNo IS NULL)
      AND (category = @category OR @category IS NULL)

end

This looks very strange at first glance, since it is checking the parameters for IS NULL, but it works.

Upvotes: 3

Related Questions