SQL Server Performance Forum – Threads Archive

Simple Cursor for SP

Hi,
I am new to tart writing Store prod with cursor.
I encounter the following simple issue.
Hope able to get help from you all.
Thanks. Code for SP:
======================= Declare @SQLStatement AS varchar(4000)
Declare @A AS varchar(255) SET @SQLStatement = ‘SELECT a FROM tableA ‘ DECLARE mv_cursor CURSOR for @SQLStatement;
OPEN mv_cursor FETCH NEXT FROM mv_cursor
INTO @A
WHILE @@FETCH_STATUS = 0
BEGIN
— This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM mv_cursor
INTO @A
END
CLOSE mv_cursor
DEALLOCATE mv_cursor The above code hit error on the this statement:
DECLARE mv_cursor CURSOR for @SQLStatement Error msg : Incorrect syntax near ‘@SQLStatement’. If i change it to DECLARE mv_cursor CURSOR for SELECT a FROM tableA
then it works.
Anyone know why DECLARE mv_cursor CURSOR for @SQLStatement; does not work?

---

http://www.sommarskog.se/dynamic_sql.html —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

---

Anyway, why do you need dynamic sql+cursor?
Can you expain what you are trying to do? Madhivanan Failing to plan is Planning to fail

---

Hi dear
your problem is that u r not running your table variable query
so try this code
Declare @SQLStatement AS varchar(4000)
Declare @A AS varchar(255) SET @SQLStatement = ‘SELECT a FROM tableA ‘ DECLARE mv_cursor CURSOR for
exec(@SQLStatement);
OPEN mv_cursor FETCH NEXT FROM mv_cursor
INTO @A
WHILE @@FETCH_STATUS = 0
BEGIN
— This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM mv_cursor
INTO @A
END
CLOSE mv_cursor
DEALLOCATE mv_cursor regards
shashi kant Keep In Touch
Thanks & Regards
Shashi Kant Chauhan

---

thanks for you help.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

]]>