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-
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 :-)
@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
.