By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Cursors and dynamic SQL

P: n/a
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?
Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Jul 23 '05 #2

P: n/a
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.

Jul 23 '05 #3

P: n/a
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
Jul 23 '05 #4

P: n/a
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
Jul 23 '05 #5

P: n/a
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
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.