How to use iff function inside in dynamic SQL string

Question

This is my code, I am facing trouble in using iff function in the Dynamic sql string The SQL server posting the error as "Incorrect syntax near 'won'." Help me with this.

Declare @Colname NVARCHAR (128)
Declare @SQl NVARCHAR(MAX)

set @Colname = N'Channel_Edited'

Set @SQl = N'Select '+@Colname+', Count(WonOpps) as Won, Count(LostOpps) as Lost, Count(OpenOpps) as [open]
From
    (        select '+@Colname+',
                    iif (Stage_Edited = 'won', opp_id,null) WonOpps,
                    iif (Stage_Edited = 'lost', opp_id,null) LostOpps,
                    iif (Stage_Edited = 'open', opp_id,null) OpenOpps

            From CRM_Deal2
            ) as ABC
 Group by '+@Colname

 Exec sp_executesql @SQL

Place double apostrophes by won, lost and open constants.

Paweł Dyl
– Paweł Dyl

2016-08-31 05:26:32 +00:00
Commented Aug 31, 2016 at 5:26 — Paweł Dyl
– Paweł Dyl, Commented Aug 31, 2016 at 5:26

TheGameiswar · Accepted Answer · 2016-08-31 05:27:16Z

2

change below part in your code

  iif (Stage_Edited = ''won'', opp_id,null) WonOpps,
                    iif (Stage_Edited = ''lost'', opp_id,null) LostOpps,
                    iif (Stage_Edited = ''open'', opp_id,null) OpenOpps

answered Aug 31, 2016 at 5:27

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

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

1 Comment

Sowmiya Raja Radhakrishnan Over a year ago

Thanks :) That Worked.

Collectives™ on Stack Overflow

How to use iff function inside in dynamic SQL string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related