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:

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

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


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

                  b.area = (@v1)  --variable 1 outside of query string
                  c.country = (@v2)   --variable 2 outside of query string
                  ((b.something = ''aaa'') OR (b.somethingelse = ''aaaa'')) --this is connected to the query so there is no problem with this part
                  a.problematic_integer_attribute != (@v3) -- problematic integer variable 3 outside of query string
                  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

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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.