1

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;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Enoque Duarte
  • 689
  • 4
  • 22
  • Possible duplicate of [Get Multiple Values in SQL Server Cursor](https://stackoverflow.com/questions/4974981/get-multiple-values-in-sql-server-cursor) – Hary Nov 14 '18 at 15:31
  • Why use a cursor at all, in any of those databases? SQL is a set-based language. It's faster (N times at least) and easier to use an UPDATE, INSERT or SELECT based on the results of a subquery than write a cursor – Panagiotis Kanavos Nov 14 '18 at 15:41
  • You don't need to preserve execution plans. SQL Server caches them. *Cursors* harm performance in this case as well, as SQL Server can cache the plan of the entire query while the cursor would allow only the `SELECT` part to be cached. – Panagiotis Kanavos Nov 14 '18 at 15:44
  • In any case, the duplicate shows how to declare a cursor but a proper query would perform a *lot* faster - for 1M rows it could perform 1M times faster – Panagiotis Kanavos Nov 14 '18 at 15:47

1 Answers1

1

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.

S3S
  • 24,809
  • 5
  • 26
  • 45
  • `sp_executesql` will execute a query, not create a cursor – Panagiotis Kanavos Nov 14 '18 at 15:41
  • yea but that would only execute the query, but i'd like to iterate the results for some other matters – Enoque Duarte Nov 14 '18 at 15:44
  • Correct @PanagiotisKanavos but a cursor isn't needed here to simply bind parameters. I know cursors or loops perform differently in other RDMS, like Informix, but can't speak to postgres or Oracle, so figured it wasn't needed – S3S Nov 14 '18 at 15:44
  • 1
    @EnoqueDuarte *why* do you want to iterate at all? Why use a cursor? What is the *actual* problem you want to solve? Cursors are almost never the answer, unless the question is `how do I delete 1M rows in batches` – Panagiotis Kanavos Nov 14 '18 at 15:44
  • to make some calcs, with some bigger logic – Enoque Duarte Nov 14 '18 at 15:45
  • @EnoqueDuarte if you post your sample data and expected results, we can help you. There are things like window functions in sql server that can replace cursors – S3S Nov 14 '18 at 15:45
  • 1
    @EnoqueDuarte that's not an answer. Reporting databases don't use cursors. Imagine using a loop over 1M rows to calculate a sum, and that's a *small* number of rows – Panagiotis Kanavos Nov 14 '18 at 15:48