CODEWITHSUNDEEP
sqlsql-server-2012
173901
173901

Reputation: 709

SQL code simplifying with ISNULL or COALESCE assistance

so i have this stored procedure and i was asked to simplify it by using a ISNULL or COALESCE but i can't seem to make it work 

CREATE PROCEDURE [GetCount]

    @Session NVARCHAR(7),
    @Activity BIT = true

AS

IF @Activity = 1 
BEGIN
      SELECT COUNT([user].[ActivityID]) AS [Count]
    FROM [ActivityListing] AS [act]
        INNER JOIN [ActivityListingDescriptions] AS [actDesc] 
            ON [act].[ActivityID] = [actDesc].[ActivityID]
    WHERE [act].[ListingSession] = @Session 
        AND [act].[ListingStatus] IN ('P','Q','N')
END
ELSE 
    SELECT COUNT([user].[ActivityID]) AS [Count]
    FROM [ActivityListing] AS [act]
        INNER JOIN [ActivityListingDescriptions] AS [actDesc] 
            ON [act].[ActivityID] = [actDesc].[ActivityID]
    WHERE [act].[ListingSession] = @Session 
        AND [act].[ListingStatus] NOT IN ('')

The problem i am encountering here is that it's not a value i am passing in, instead i am changing the query itself , One is WHERE ... IN and the other is WHERE ... NOT IN and so it wont allow me to write a query as a returned result. Is there another way to accomplish this task of simplifying the code?

Upvotes: 1

Views: 35

Answers (1)

dotnetom
dotnetom

Reputation: 24901

Instead of using ISNULL or COALESCE you can combine WHERE conditions to single query:

CREATE PROCEDURE [GetCount]
    @Session NVARCHAR(7),
    @Activity BIT = true
AS
    SELECT COUNT([user].[ActivityID]) AS [Count]
    FROM [ActivityListing] AS [act]
        INNER JOIN [ActivityListingDescriptions] AS [actDesc] 
            ON [act].[ActivityID] = [actDesc].[ActivityID]
    WHERE [act].[ListingSession] = @Session 
        AND ((@Activity = 1 AND [act].[ListingStatus] IN ('P','Q','N') ) 
            OR (@Activity <> 1 AND [act].[ListingStatus] NOT IN ('')))

If value of @Activity is 1 then the IN part of the condition will be executed, if it is anything else but 1, then the NOT IN condition will be executed

Upvotes: 2

Related Questions