1

I created a dictionary table with 'Condition' column where I store conditions for particular customers. Table's name is CustomerConditions:

enter image description here

Then I created dynamic SQL where I want to use this Condition:

declare 
    @TableName as nvarchar(10),
    @FieldName as nvarchar(20),
    @CustName as Nvarchar(50),
    @Condition as NVARCHAR(MAX)

set @Condition = (SELECT o.Condition FROM CustomerConditions o WHERE o.Group = @CustName)

declare @strSQL as NVARCHAR(MAX)
SET @strSQL = 

'DECLARE @FieldName as nvarchar(20),
        @CustName as nvarchar(50)

;WITH NewCTE AS (
                    SELECT Tab1.Group, '+@FieldName+'
                    FROM (
                    SELECT
                    '+@Condition+' AS Group,
                    '+@FieldName+'
                    FROM '+@TableName+' as c) as Tab1)
            
SELECT * FROM NewCTE'

EXEC(@strSQL)

The problem is that when I pass @Condition to dynamic SQL string from column 'Condition' does not become part of SQL syntax - it's passed as expression, in the result I got:

enter image description here

This is not what I want. I want this 'Case WHEN...' to become part of my SQL syntax.

On the other hand when I'm not using @Condition but pass 'CASE WHEN..' explicitly then everything works well (all declared parameters works well, all is good):

;WITH NewCTE AS (
                    SELECT Tab1.Group, '+@FieldName+'
                    FROM (
                    SELECT
                    CASE WHEN '+ @FieldName +' LIKE ''XY%'' OR '+ @FieldName +' LIKE ''XYZ%'' then '''+ @CustName +''' END AS Group,
                    '+@FieldName+'
                    FROM '+@TableName+' as c) as Tab1)

So how can I pass this 'Case when' condition into dynamic SQL using parameter?

Improve this question
6
  • 1
    Warning, this is wide open to injection. Dos and Don'ts of Dynamic SQL Commented Jul 16, 2020 at 20:07
  • @Muska, Your query is incorrect (I mean it has syntax errors) Commented Jul 16, 2020 at 20:16
  • Where are the values for @FieldName, @TableName and so on? Commented Jul 16, 2020 at 20:23
  • Those values are passed in stored procedure. I didn't wrapped up my qurey in procedure here, just wanted to show how it works inside. Commented Jul 16, 2020 at 20:28
  • @SrinikaPinnaduwage I see you deleted your post. I had a question. Why we ought to take paramteres inside in triple commas instead of single commas? I did it with single commas and query worked well (of course excepting Condition parameter, I tried without this parametere and qurey worked properly) Commented Jul 16, 2020 at 20:42

1 Answer 1

Reset to default
0

While agreeing with the issue of dynamic prone to injection attacks, and assuming some random values for your non initialized variables, here is how I approach

The trick is

SELECT @Condition = REPLACE(@Condition, '@FieldName', @FieldName )
SELECT @Condition = REPLACE(@Condition, '@CustName', @CustName )

So the main Query will be

declare 
    @TableName as nvarchar(10) = 'MyTbl',
    @FieldName as nvarchar(20) = 'FldName',
    @CustName as Nvarchar(50) = 'C1' ,
    @Condition as NVARCHAR(MAX) 
    
SELECT @Condition = (SELECT o.Condition FROM CustomerConditions o WHERE o.[Group] = @CustName)
SELECT @Condition = REPLACE(@Condition, '@FieldName', @FieldName )
SELECT @Condition = REPLACE(@Condition, '@CustName', @CustName )

declare @strSQL as NVARCHAR(MAX)
SET @strSQL = 'DECLARE @FieldName as nvarchar(20),       
@CustName as nvarchar(50)
;WITH NewCTE AS (
                    SELECT Tab1.Group, '+@FieldName+'
                    FROM (
                   SELECT
                    '+@Condition+' 
                    '+@FieldName+'
                    FROM '+@TableName+' as c) as Tab1)
            
SELECT * FROM NewCTE'

Select(@strSQL)

First provide proper values to your variables and execute this. Instead of executing the query, what I did was create the query to see whether it got created as you want.

To be certain, you can copy paste the outcome and execute that. Then you can change the last line to Execute ...

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

9 Comments

Ok, looks tricky. I'll check it out tomorrow at work. So instead SET parameter we SELECT it? Could you explain me the difference in this case? Can I use SET istead? (I'm talking about this part: SELECT @Condition = (SELECT o.Condition FROM...)
Set and Select - no difference, its my preference
You can try it in a fiddle, Key change is the REPLACE function
I implemented your solution but got in response: Incorrect syntax near '+'. Don't know why. When I double click on message it appoints SELECT @Condition = (SELECT o.Condition FROM CustomerConditions o WHERE o.[Group] = @CustName)
Or maybe is it sth wrong with 'CASE WHEN...' condition itself? Do I need '+' inside?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.