Use Cursor with variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use Cursor with variables

Author

Topic

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-10-28 : 23:41:12

Hi

I need to open a cursor with variable:

Set @strSQL = 'SELECT Process'
Set @strSQL = @strSQL + 'FROM ' + @table_Name_Status
Set @strSQL = @strSQL + 'WHERE Upper(Status) = ''QUEUE'''
Set @strSQL = @strSQL + 'ORDER BY Date_Tm'

DECLARE curProcessQueue CURSOR FAST_FORWARD FOR
Exec sp_executesql @strSQL

The statement throws an error indicating error near Exec.
But it works in the following way:

DECLARE curProcessQueue CURSOR FAST_FORWARD FOR
SELECT Process
FROM Table_Name_Status
WHERE Upper(Status) = 'QUEUE'
ORDER BY Date_Tm

How to make the first statement work as I wanted the table name to be a variable.
Thanks.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-29 : 00:39:24

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-10-29 : 01:56:00

If doing this way, then how about the Open Cursor, Fetch, WHILE @@Fetch_Status = 0 statement, how should I place them?

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-29 : 01:59:49

Declare
@strSQL nvarchar(1000),
@table_Name_Status varchar(100)

Set @table_Name_Status='table_Name_Status'

Set @strSQL = 'DECLARE curProcessQueue CURSOR FAST_FORWARD FOR'
Set @strSQL = @strSQL +' SELECT Process '
Set @strSQL = @strSQL + 'FROM ' + @table_Name_Status
Set @strSQL = @strSQL + ' WHERE Upper(Status) = ''QUEUE'''
Set @strSQL = @strSQL + ' ORDER BY Date_Tm'

execute (@strSQL)
Open curProcessQueue
fetch next from curProcessQueue into ...
while (@@fetch_staus=0)
Begin
--your code...
fetch next from curProcessQueue into ...
End
close curProcessQueue
deAllocate curProcessQueue

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-11-05 : 22:32:54

i tried that out, is not working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources