Dynamics SQL as the source for a Cursor?
in [Programming SQL]
|
From: Thomas Malia on 9 Jul 2008 10:47 Does anyone know if it is possible, and if so how, to use a dynamically built SQL statement as the source for a cursor in T-SQL? For example, I'd like to do something functionally like the following: DECLARE @TABLENAME varchar(255) DECLARE @ID varchar(255) SET @TABLENAME = 'Customers' DECLARE CSR_TEST CURSOR FOR EXEC('Select Custid from ' + @TABLENAME) OPEN CSR_TEST FETCH NEXT FROM CSR_TEST INTO @ID WHILE @@FETCH_STATUS = 0 BEGIN PRINT @ID FETCH NEXT FROM CSR_TEST INTO @ID END CLOSE CSR_TEST DEALLOCATE CSR_TEST Is this possible? Tom Malia T&T Data Solutions L.L.C. Dynamics SL MVP since Sept. 2007
From: Uri Dimant on 9 Jul 2008 10:51 Hi Dynamic cursor should be used USE Pubs GO DECLARE @AuthorId VARCHAR(20) DECLARE @TableName VARCHAR(255) SET @TableName = 'authors' EXEC('DECLARE TAB_CURSOR CURSOR GLOBAL FOR SELECT au_id FROM ' + @TableName) OPEN TAB_CURSOR FETCH NEXT FROM TAB_CURSOR INTO @AuthorId WHILE @@FETCH_STATUS = 0 BEGIN PRINT @AuthorId FETCH NEXT FROM TAB_CURSOR END CLOSE TAB_CURSOR DEALLOCATE TAB_CURSOR "Thomas Malia" <tommalia(a)worldnet.att.net> wrote in message news:eBlTrKd4IHA.3480(a)TK2MSFTNGP03.phx.gbl... Does anyone know if it is possible, and if so how, to use a dynamically built SQL statement as the source for a cursor in T-SQL? For example, I'd like to do something functionally like the following: DECLARE @TABLENAME varchar(255) DECLARE @ID varchar(255) SET @TABLENAME = 'Customers' DECLARE CSR_TEST CURSOR FOR EXEC('Select Custid from ' + @TABLENAME) OPEN CSR_TEST FETCH NEXT FROM CSR_TEST INTO @ID WHILE @@FETCH_STATUS = 0 BEGIN PRINT @ID FETCH NEXT FROM CSR_TEST INTO @ID END CLOSE CSR_TEST DEALLOCATE CSR_TEST Is this possible? Tom Malia T&T Data Solutions L.L.C. Dynamics SL MVP since Sept. 2007
From: Alejandro Mesa on 9 Jul 2008 11:15 Thomas Malia, You can also use: USE Pubs GO DECLARE @AuthorId VARCHAR(20) DECLARE @TableName VARCHAR(255) SET @TableName = 'authors' declare @sql nvarchar(4000) declare @c cursor set @sql = N' set @c = CURSOR LOCAL STATIC READ_ONLY FOR SELECT au_id FROM ' + quotename(@TableName) + N';open @c' exec sp_executesql @sql, N'@c cursor output', @c output IF CURSOR_STATUS('variable', '@c') = 1 BEGIN WHILE 1 = 1 BEGIN FETCH NEXT FROM @c INTO @AuthorId IF @@ERROR <> 0 OR @@FETCH_STATUS <> 0 BREAK PRINT @AuthorId END CLOSE @c END DEALLOCATE @c GO Why don't you tell us your needs for a cusor? AMB "Thomas Malia" wrote: > Does anyone know if it is possible, and if so how, to use a dynamically built SQL statement as the source for a cursor in T-SQL? > > For example, I'd like to do something functionally like the following: > > DECLARE @TABLENAME varchar(255) > > DECLARE @ID varchar(255) > > > SET @TABLENAME = 'Customers' > > > DECLARE CSR_TEST CURSOR FOR > > EXEC('Select Custid from ' + @TABLENAME) > > > OPEN CSR_TEST > > FETCH NEXT FROM CSR_TEST INTO @ID > > WHILE @@FETCH_STATUS = 0 > > BEGIN > > PRINT @ID > > FETCH NEXT FROM CSR_TEST INTO @ID > > END > > CLOSE CSR_TEST > > DEALLOCATE CSR_TEST > > > > Is this possible? > > > Tom Malia > > T&T Data Solutions L.L.C. > > Dynamics SL MVP since Sept. 2007 > > >
From: Erland Sommarskog on 9 Jul 2008 19:24 Uri Dimant (urid(a)iscar.co.il) writes: > Dynamic cursor should be used Nah, use static unless you know you really want something else: > EXEC('DECLARE TAB_CURSOR CURSOR GLOBAL FOR EXEC('DECLARE TAB_CURSOR CURSOR STATIC GLOBAL FOR -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: --CELKO-- on 9 Jul 2008 19:33
Both dynamic SQL and Cursors are considered bad SQL programming. Why don't you tell us what5 you want to do instead of how you decided to do it? |