CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-cursor
Enoque Duarte
Enoque Duarte

Reputation: 689

How to bind parameters on a SQL Server query cursor

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

Answers (1)

S3S
S3S

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

Related Questions