Dynamic functions in sql - using IF

Question

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

Seems like an XY problem to me. What exactly are you trying to acheive? — GarethD
– GarethD, Commented Jun 24, 2015 at 9:30

Gordon Linoff · Accepted Answer · 2015-06-24 10:02:27Z

1

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.

answered Jun 24, 2015 at 10:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic functions in sql - using IF

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related