integer variable for query string - option(optimize for variables)

Question

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.

Answer 1

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)) + '))'