Creating multifuntional Stored procedure using bit argument

Question

Creating multifuntional Stored procedure using bit argument

dapper sql-server stored-procedures

Question

CREATE PROCEDURE [GetSubscriptions]
@id uniqueidentifier,
@isActive bit
AS
BEGIN
SELECT 
*
FROM SUBSCRIPTION
WHERE id=@id and IsActive= @isActive 
END
GO

I want to use it in this way-

when @isActive =1, it will return active subscription rows. -it works
when @isActive =0, it will return inactive subscription rows. -it works
Indepedent of IsActive Fiag, it will return all subscription rows -?

Subscription table has only two values for IsActive that is 0 and 1. What would be the best short way so that it works for all these three conditions?Thanks in advance :-)

APL

View more on Stack Overflow

cco · Accepted Answer · 3/11/2017 10:46:15 PM

@isActive can have three states: 0, 1, or NULL.
Just add something for NULL to your where clause:

CREATE PROCEDURE [GetSubscriptions]
@id uniqueidentifier,
@isActive bit
AS
BEGIN
SELECT 
*
FROM SUBSCRIPTION
WHERE id=@id and (IsActive= @isActive or @isActive is NULL)
END
GO

The two sides of the or can never be true at the same time, since comparing vs NULL using = is always false. If @isActive is NULL, the or is True regardless of the value of the isActive column in Subscription.

Creating multifuntional Stored procedure using bit argument

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator