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