0

EDITED:

I am running the following query 

DECLARE @value0 INT; 
DECLARE @filter = 'values.country = ''Germany''';
EXEC('SELECT 
'+@value0+' = SUM(CASE WHEN valuecolumn >= 0   
AND valuecolumn < 31  THEN POWER(2, valuecolumn - 0) ELSE 0 END) ,
'+@value1+' = SUM(CASE WHEN valuecolumn >= 32  AND valuecolumn < 63  THEN 
POWER(2, valuecolumn - 32)  ELSE 0 END)
FROM dbo.values
where '+@filter+'');

when I am executing this query I am getting this error :

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '='.

The filter is a dynamic variable that I need to use and that is why I am using execution function. How is it possible to fill a declared variable using execution function?

Improve this question
4
  • 1
    Have a look at sp_executesql Commented Mar 21, 2018 at 15:06
  • 1
    Why do you use dynamic SQL for this in the first place? Commented Mar 21, 2018 at 15:08
  • @Zohar Peled I edited my question as an answer to your question Commented Mar 21, 2018 at 15:16
  • 1
    And I've edited my answer... Commented Mar 21, 2018 at 15:20

1 Answer 1

Reset to default
2

When you do need to execute dynamic sql with an output parameter, you must use sp_executesql:

DECLARE @sql NVARCHAR(1500),
        @ParmDefinition NVARCHAR(500),
        @value0 INT,
        @filter NVARCHAR(1000);

-- Set the @filter values here....

SET @sql = 'SELECT @result = SUM(
                   CASE WHEN valuecolumn >= 0 AND valuecolumn < 31 THEN 
                       POWER(2, valuecolumn - 0)
                   ELSE 
                       0 
                   END) FROM dbo.values
             WHERE '+ @filter

SET @ParmDefinition = N'@result int OUTPUT';

EXEC sp_executesql @Sql, @ParmDefinition, @result = @value0 OUTPUT;

Please note that this use of your @Filter parameter is vulnerable to SQL injection attacks. If possible, you should refactor this query into something safe (using catch-all techniques).

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

5 Comments

Must declare the scalar variable "@result".
and when I am declaring : Conversion failed when converting the varchar value 'SELECT ' to data type int. I am not quite sure how does this work
Sorry, I've misplaced the @result and @value0 in the exec statement. Edited my answer.
and if I have value0 = sum(), value1 = sum(), value2 = sum(), value3 = sum().. etc I have to make for each of them different ParmDefinition?
No, you concatenate them: SET @ParmDefinition = N'@value0 int output, @value1 int output, @value3 int output'...

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.