Reputation: 689
Oracle :
STR_SQL := ' SELECT ... where x = :1 and y = :2';
OPEN RS FOR STR_SQL USING VAR1, VAR2;
Postgres:
STR_SQL := ' SELECT ... where x = $1 and y = $2';
OPEN RS FOR EXECUTE STR_SQL USING VAR1, VAR2;
How can I do this in SQL Server? All I want is to avoid multiple execution plans
EDIT:
Does using a variable on the query with @
preserve the execution plan ?
SET @RS = CURSOR FOR SELECT ... where x = @MY_PROC_PARAMETER
OPEN @RS;
FETCH NEXT FROM @RS INTO @VRESULT_VALUE;
Upvotes: 1
Views: 1729
Reputation: 25152
Cursors don't perform well in SQL Server, or any loop for that matter, so I'd avoid those. The equivalent for binding parameters to dynamic SQL in SQL Server would be sp_executesql. You can also just build out a concatenated query string and execute it:
declare @x int = 1
declare @y int = 2
declare @sql varchar(max) = 'select * from table where x = ' + cast(@x as varchar) + ' and y = ' + cast(@y as varchar)
print @sql
--exec @sql
The casting is needed on dates, numerics, and other datatypes to treat the +
as concatenation instead of arithmetic.
But, if you really just want a fresh execution plan for your query, you can add the query hint OPTION(RECOMPILE). Doing this isn't usually warranted. If you are getting bad plans, you should instead investigate why you are getting those. i.e. statistics, parameter sniffing, etc.
Upvotes: 1