/* Author...: Michael Thomas email....: michael@michael-thomas.com Date.....: 07/14/07 Modified.: 07/14/07 Used for the examples at: www.michael-thomas.com for MSSQL Commands. Description: Example of using Cursors. */ use master IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyTempDB') begin DROP DATABASE [MyTempDB] end go create database MyTempDB go use MyTempDB -- ************************** -- MyTable -- ************************** if exists (select * from [dbo].[sysobjects] where id = object_id(N'MyTable') ) begin drop table MyTable end go -- Table that has an ID that is created automatically. create table MyTable ( id int IDENTITY(1,1) not null, code char(1), codedesc nvarchar(255) ) go -- Inserts where all you need to do is add a select. insert into MyTable ( code, codedesc ) select 'A', 'Hello World 1' union all select 'B', 'Hello World 2' union all select 'C', 'Hello World 3' /* ****************************************************** Example of using a Cursor ****************************************************** */ -- Other Variables declare @txtcodelist nvarchar(255) -- Cursor Variables declare @id int declare @code char(1) declare @codedesc nvarchar(255) DECLARE cursorCodes CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR select id, code, codedesc from MyTable set @txtcodelist = '' OPEN cursorCodes FETCH NEXT FROM cursorCodes INTO @id, @code, @codedesc WHILE (@@FETCH_STATUS = 0) BEGIN set @txtcodelist = @txtcodelist + convert(nvarchar, @id) + ': ' + @code + ' - ' + @codedesc + '; ' + char(13)+ char(10) FETCH NEXT FROM cursorCodes INTO @id, @code, @codedesc end Print 'Code list: ' + char(13)+ char(10) + @txtcodelist /*****************************************************/ /* Close the cursor and deallocate it. */ /*****************************************************/ Close cursorCodes DEALLOCATE cursorCodes