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.

Hi

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

[quoted text, click to view]
And what an excellent site it is!

[quoted text, click to view]
I would expect quotes around your strings say:

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


[quoted text, click to view]

John

[quoted text, click to view]

Thanks a lot!


[quoted text, click to view]

Another error was here. This variable was not declared.

[quoted text, click to view]

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.

Hi

[quoted text, click to view]

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

Jo¨ko ¦ugar (josko@netgen_makniOvo.hr) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

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