Cursors and dynamic SQL

[Joško Šugar]

On this site:
http://www.sommarskog.se/dynamic_sql.html

I have found an example how to use cursor with dynamic SQL:
DECLARE @my_cur CURSOR
EXEC sp_executesql
N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN
@my_cur',
N'@my_cur cursor OUTPUT', @my_cur OUTPUT
FETCH NEXT FROM @my_cur
But when I tried to do this:

IF (@Naziv <> '')
SET @sql_where = @sql_where + N' AND Naziv LIKE ' + @Naziv

IF (@Funk <> '')
SET @sql_where = @sql_where + N' AND Funkcija LIKE ' + @Funk

IF (@Mj <> '')
SET @sql_where = @sql_where + N' AND NazivMjesta LIKE ' + @Mj

IF (@Drz <> '')
SET @sql_where = @sql_where + N' AND (drzava1 LIKE ' + @Drz +
' OR drzava2 like ' + @Drz + ' OR drzava3 LIKE ' + @Drz + ')'
DECLARE @CursSearch CURSOR

SET @sql = N'SET @CursSearch = CURSOR FOR
SELECT CvorID, NadCvorID,
IzvorisniCvorID, Naziv, TipCvora,
NasljednaLinija, Funkcija, NazivMjesta,
drzava1, drzava2, drzava3
FROM dbo.Pretrazivanje
WHERE NasljednaLinija LIKE @NasljednaLinija'
+ @sql_where + N'; OPEN @CursSearch'

EXEC sp_executesql @sql, N'@CursSearch CURSOR OUTPUT',
@CursSearch OUTPUT

....by fetching cursor i got this message:
The variable '@CursSearch' does not currently have a cursor allocated to it.
Can anybody tell me what i did wrong?

[John Bell]

Hi

I suggest you print out the statement @sql and debug it in Query Analyser.

"Joško Šugar" <josko@netgen_makniOvo.hr> wrote in message
news:7512.41bdcba7.c66fd@hubble...

On this site:
http://www.sommarskog.se/dynamic_sql.html And what an excellent site it is!

I have found an example how to use cursor with dynamic SQL:
DECLARE @my_cur CURSOR
EXEC sp_executesql
N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN
@my_cur',
N'@my_cur cursor OUTPUT', @my_cur OUTPUT
FETCH NEXT FROM @my_cur
But when I tried to do this:

IF (@Naziv <> '')
SET @sql_where = @sql_where + N' AND Naziv LIKE ' + @Naziv
I would expect quotes around your strings say:

SET @sql_where = @sql_where + N' AND Naziv LIKE ''' + @Naziv + '%'''

IF (@Funk <> '')
SET @sql_where = @sql_where + N' AND Funkcija LIKE ' + @Funk

IF (@Mj <> '')
SET @sql_where = @sql_where + N' AND NazivMjesta LIKE ' + @Mj

IF (@Drz <> '')
SET @sql_where = @sql_where + N' AND (drzava1 LIKE ' + @Drz +
' OR drzava2 like ' + @Drz + ' OR drzava3 LIKE ' + @Drz + ')'
DECLARE @CursSearch CURSOR

SET @sql = N'SET @CursSearch = CURSOR FOR
SELECT CvorID, NadCvorID,
IzvorisniCvorID, Naziv, TipCvora,
NasljednaLinija, Funkcija, NazivMjesta,
drzava1, drzava2, drzava3
FROM dbo.Pretrazivanje
WHERE NasljednaLinija LIKE @NasljednaLinija'
+ @sql_where + N'; OPEN @CursSearch'

EXEC sp_executesql @sql, N'@CursSearch CURSOR OUTPUT',
@CursSearch OUTPUT

...by fetching cursor i got this message:
The variable '@CursSearch' does not currently have a cursor allocated to
it.
Can anybody tell me what i did wrong?

John

[Joško Šugar]

John Bell wrote:

Hi

I suggest you print out the statement @sql and debug it in Query Analyser.

IF (@Naziv <> '')
SET @sql_where = @sql_where + N' AND Naziv LIKE ' + @Naziv

I would expect quotes around your strings say:

SET @sql_where = @sql_where + N' AND Naziv LIKE ''' + @Naziv + '%'''

Thanks a lot!

DECLARE @CursSearch CURSOR

SET @sql = N'SET @CursSearch = CURSOR FOR
SELECT CvorID, NadCvorID,
IzvorisniCvorID, Naziv, TipCvora,
NasljednaLinija, Funkcija, NazivMjesta,
drzava1, drzava2, drzava3
FROM dbo.Pretrazivanje
WHERE NasljednaLinija LIKE @NasljednaLinija'
Another error was here. This variable was not declared.
+ @sql_where + N'; OPEN @CursSearch'

EXEC sp_executesql @sql, N'@CursSearch CURSOR OUTPUT',
@CursSearch OUTPUT

Is there any kind of performance issue in using cursors like this,
compared to named cursors?
I've managed to solve this problem by using named cursors and it is more
than 2x faster.

[John Bell]

Hi

Is there any kind of performance issue in using cursors like this,
compared to named cursors?
I've managed to solve this problem by using named cursors and it is more
than 2x faster.

I don't know why using a cursor variable may be significantly slower than a
specifically declared one. It could be that you are declaring different
types of cursor.

The fastest solution will almost certainly be a set based one.

John

[Erland Sommarskog]

Jo¨ko ¦ugar (josko@netgen_makniOvo.hr) writes:

EXEC sp_executesql @sql, N'@CursSearch CURSOR OUTPUT',
@CursSearch OUTPUT

Is there any kind of performance issue in using cursors like this,
compared to named cursors?
I've managed to solve this problem by using named cursors and it is more
than 2x faster.

I have never used cursor variables, so I don't really know. But I find
it somewhat difficult to believe that the overhead would be of that
magnitude. As John said, the cursor type may matter more.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jo¨ko ¦ugar]

Erland Sommarskog wrote:

Jo¨ko ¦ugar (josko@netgen_makniOvo.hr) writes:

EXEC sp_executesql @sql, N'@CursSearch CURSOR OUTPUT',
@CursSearch OUTPUT

Is there any kind of performance issue in using cursors like this,
compared to named cursors?
I've managed to solve this problem by using named cursors and it is more
than 2x faster.

I have never used cursor variables, so I don't really know. But I find
it somewhat difficult to believe that the overhead would be of that
magnitude. As John said, the cursor type may matter more.

Right again! You guys are my idols!
Anyway, with cursor and dynamic sql I managed to speed up the original
query by 10x.
Thanks
Josko