CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Michael meshaev
Michael meshaev

Reputation: 31

Dynamic functions in sql - using IF

I have a small question

 @CMD nvarchar (MAX)='' 
SELECT @CMD +=N'


 SELECT
     COUNT(CASE WHEN RFRD.QueuTypeID=4
           THEN RFRD.QueuTypeID
           END) RequiredaQueue
    ,COUNT(CASE WHEN RFRD.QueuTypeID=3
          THEN RFRD.QueuTypeID
          END) BhinaQueue      
    ,COUNT(CASE WHEN RFRD.QueuTypeID NOT IN (3,4) 
    ,COUNT(DISTINCT RFRD.LeadDocumentID)AS Repeat,CONCAT('''+RF+''' ,''*'')A'                                              
FROM #cte RF

p.s - The From is outside the dynamic.

Works fine here... But when I add IF statement:

   SELECT
     COUNT(CASE WHEN RFRD.QueuTypeID=4
           THEN RFRD.QueuTypeID
           END) RequiredaQueue
    ,COUNT(CASE WHEN RFRD.QueuTypeID=3
          THEN RFRD.QueuTypeID
          END) BhinaQueue      
    ,COUNT(CASE WHEN RFRD.QueuTypeID NOT IN (3,4)'
     **IF @MyParam IS NOT NULL**
   SET @CMD+='
    ,COUNT(DISTINCT RFRD.LeadDocumentID)AS Repeat,CONCAT('''+RF+'''                                               ,''*'')A'                                              
FROM #cte RF

The program doesn't understand what is the RF means. it doesn't see the FROM #cte RF Anymore. any body knows whats the problem with the IF statement?? thanks

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Then don't use if. Use case:

 @CMD nvarchar (MAX)='' 

SELECT @CMD +=N'
 SELECT
     COUNT(CASE WHEN RFRD.QueuTypeID=4
           THEN RFRD.QueuTypeID
           END) as RequiredaQueue
    ,COUNT(CASE WHEN RFRD.QueuTypeID=3
          THEN RFRD.QueuTypeID
          END) as BhinaQueue   
    ,COUNT(CASE WHEN RFRD.QueuTypeID NOT IN (3,4) '
    (case when @MyParem is not null then ',COUNT(DISTINCT RFRD.LeadDocumentID)AS Repeat,CONCAT('''+RF+''' ,''*'')A' 
          else ''
     end)  + '                                           
FROM #cte RF ';

All the code is inside a select statement, so case is the appropriate construction, not if.

Upvotes: 1

Related Questions