CODEWITHSUNDEEP
sqlstored-proceduressql-server-2012
Laziale
Laziale

Reputation: 8225

Conditional stored procedure with bool values

I am trying to assemble a stored procedure to return a set of results.

On my page, I have three checkboxes and a searchbox.

Then in the stored procedure, I have this:

CREATE Procedure [dbo].[sp_Search]
   @Bool1 bit = NULL, 
   @Bool2 bit = NULL,
   @Bool3 bit = NULL,
   @SearchTerm nvarchar(30)
As
Begin
   Select 
        [CompanyID],
        [CompanyName],
        [Label],
        [Bool1],[Bool2],[Bool3]     
    From [Contract Companies]
    WHERE (CompanyName LIKE @SearchTerm OR [Label] LIKE @SearchTerm) AND
    if(@Bool1 IS NOT NULL)
    [Bool1] = 'True'
    end 

if(@Bool2 IS NOT NULL)
 AND    [Bool2] = 'True'
    end 

if(@Bool3 IS NOT NULL)
AND [Bool3] = 'True'
    end

Something like that, with those three boolean values not true all the time, just when their checkbox will be checked.

How can I achieve that?

Thanks in advance, Laziale

Upvotes: 2

Views: 2069

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Option with CASE expression

CREATE Procedure [dbo].[sp_Search]
   @Bool1 bit = NULL, 
   @Bool2 bit = NULL,
   @Bool3 bit = NULL,
   @SearchTerm nvarchar(30)
As
Begin
   Select 
        [CompanyID],
        [CompanyName],
        [Label],
        [Bool1],[Bool2],[Bool3]     
    From [Contract Companies]
    WHERE (CompanyName LIKE @SearchTerm OR [Label] LIKE @SearchTerm) AND
          [Bool1] = CASE WHEN @Bool1 IS NOT NULL THEN 'True' ELSE [Bool1] END AND
          [Bool2] = CASE WHEN @Bool2 IS NOT NULL THEN 'True' ELSE [Bool2] END AND
          [Bool3] = CASE WHEN @Bool3 IS NOT NULL THEN 'True' ELSE [Bool3] END

Upvotes: 0

Tim Lehner
Tim Lehner

Reputation: 15251

You might try something like this:

create procedure [dbo].[sp_Search]
   @Bool1 bit = null, 
   @Bool2 bit = null,
   @Bool3 bit = null,
   @SearchTerm nvarchar(30)
as
begin
   select 
        CompanyID,
        CompanyName,
        Label,
        Bool1,
        Bool2,
        Bool3    
    from Contract Companies
    where (CompanyName like @SearchTerm or Label like @SearchTerm)
        and (Bool1 = @Bool1 or @Bool1 is null)
        and (Bool2 = @Bool2 or @Bool2 is null)
        and (Bool3 = @Bool3 or @Bool3 is null)
end

This will get matching records for each bool when present, and all records otherwise.

It is noted that this may not be the best performing method of dynamically checking values, but they're bits here (often not indexed) and I think it fits very elegantly. There are alternatives discussed in that article.

Upvotes: 2

Related Questions