CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Mani Rathinam
Mani Rathinam

Reputation: 11

Unable to access multiple where case in where condition in SQL Server

I try to access multiple where condition as stored procedure:

--EXEC RPT_LIST_BAK @Status='P/C,F,AT,CP'

ALTER PROCEDURE [dbo].[RPT_LIST_BAK]   
@STATUS         NVARCHAR(50)=NULL

AS
BEGIN

   set @STATUS = '''' + REPLACE(@STATUS,  ',', ''',''') + ''''

   SELECT   
            TotalFailed = COUNT(CASE WHEN DIV_REQ_COM_IND IN('F') THEN DIV_REQ_COM_IND END)
    FROM
      TWAD_DIV_MASTER A

    WHERE A.DIV_REQ_DEL_STATUS_ID = 1   
    AND A.DIV_REQ_COM_IND IN (@STATUS)
    GROUP BY A.DIV_REQ_MAT_ID
    ORDER BY A.DIV_REQ_MAT_ID  ASC

END

  1. EXEC RPT_LIST_BAK @Status='P/C,F,AT,CP'

  2. In select I get 'P/C','F','AT','CP' -- this s correct and also return value,

  3. If I use "'P/C','F','AT','CP'" in where condition means it returns values
  4. But if I use in where condition (AND A.DIV_REQ_COM_IND IN (@STATUS)) it shows null value?

Upvotes: 0

Views: 45

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Can you please try with this below script? this should work.

ALTER PROCEDURE [dbo].[RPT_LIST_BAK] @STATUS NVARCHAR(50) = NULL
AS
BEGIN

    DECLARE @QueryScript NVARCHAR(MAX);
    SET @STATUS = '''' + REPLACE(@STATUS, ',', ''',''') + '''';

    SET @QueryScript = '
    SELECT  TotalFailed = COUNT(CASE WHEN DIV_REQ_COM_IND IN(''F'') THEN DIV_REQ_COM_IND END)
    FROM TWAD_DIV_MASTER A
    WHERE A.DIV_REQ_DEL_STATUS_ID = 1   
    AND A.DIV_REQ_COM_IND IN (' + @STATUS + ')
    GROUP BY A.DIV_REQ_MAT_ID
    ORDER BY A.DIV_REQ_MAT_ID  ASC'

    EXEC (@QueryScript);
END;

Upvotes: 1

Related Questions