How to populate variable using execution function in tsql?

Question

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?

Answer 1

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).