SQL Server – Optimize for Variables in Dynamic SQL

dynamic-sqlsql server

I have problem related to my post variable for query string (or dynamic query?).

I encountered a hurdle related to using an integer parameter. The query looks like this:

DECLARE @area NVARCHAR(255)
SET @area = 'here_is_the_name'    --i have specyfic names here
DECLARE @country NVARCHAR(255)
SET @country = 'here_is_the_country_name'     --and there
DECLARE @instance INTEGER
SET @instance = 41

DECLARE @SQLq NVARCHAR(MAX)  --declaring variable for query string
SET @SQLq ='

              SELECT
                  a.something,
                  b.somethingelse,
                  c.entirelysomethingelse,
                  c.diffrentthanthoseabove

              FROM
                  tablea a 
                  LEFT JOIN tableb b ON ...
                  LEFT JOIN tablec c ON ...
                  LEFT JOIN tabled d ON ...
              ...    

              WHERE
                  b.area = (@v1)  --variable 1 outside of query string
              AND
                  c.country = (@v2)   --variable 2 outside of query string
              AND
                  ((b.something = ''aaa'') OR (b.somethingelse = ''aaaa'')) --this is connected to the query so there is no problem with this part
              AND
                  a.problematic_integer_attribute != (@v3) -- problematic integer variable 3 outside of query string
              AND
                  d.something LIKE (''%@%'')  --not important in this case

              GROUP BY
              ...
              ORDER BY
              ...

--and here lies the biggest hurdle with option(optimize for variables)

OPTION(OPTIMIZE FOR (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '+@instance+'))'  --end of a string query

EXEC SP_EXECUTESQL @SQLq, 
N'@v1 NVARCHAR(255), @v2 NVARCHAR(255), @v3 INTEGER', 
@v1 = @area, @v2 = @country, @v3 = @instance

To be able to use variables from outside of the string query I had to "break" a string chain as @mortb wrote in related post. But even though I did the same with @instance (the integer variable) it's not working.
I'm getting an error: 

Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the nvarchar value '...here is the query...' to data type int.

The error is connected with @v3 or @instance in this part:

option(optimize for (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '+@instance+'))'

I can't find solution for this problem. I tried to "break" the string chain with the same method as I used for string/varchar variables like:

 option(optimize for (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '''+@instance+'''))'

But it's not working. Is there a different method for using integer variables?

I'm using query string (it's called dynamic query I suppose) because I have to make a research connected to parameter sniffing (here option(optimize….)) without creating stored procedures and being able to use variables. Does anyone encountered similar problem and found a solution? If someone does please help me with overcome this hurdle.

Best Answer

The problem is with the line that builds the dynamic query, specifically here:

'... @v3 = '+@instance+'))'

The @instance variable is an int variable. The int type has higher precedence than any string type, and so it changes the meaning of the + operation, which now stands for mathematical addition rather than string concatenation. In other words, you are effectively trying to add a string, which is your query, to an integer, which is the @instance variable. Since the string fails to convert to an integer, you get the error shown in your question.

Solution: change the @instance in that fragment to CAST(@instance AS varchar(11)) (or CAST(@instance AS nvarchar(11))), like this:

'... @v3 = ' + CAST(@instance AS varchar(11)) + '))'
Related Question