19 November 2005

Cursors and Lists

    I found Cursors really useful when the linear logic provided by a query is not solving a special problem (e.g. concatenate in a list the values returned by a select, multiple updates/insert/deletions based on the success or failure of previous logic).     Cursors are not complicated, but writing the basic code again and again could be painful when the time is limited, so I prefer to have the simplest code and then modify it according to the new requirements.     Just a simple sample of using a Cursor, it returns a list of emails. --creating the sample table CREATE TABLE EmailAddresses(ID int, Email varchar(50)) --insert test records INSERT EmailAddresses VALUES (1, 'John.Travolta@star.com') INSERT EmailAddresses VALUES (2, 'Robert.DeNiro@star.com') INSERT EmailAddresses VALUES (3, 'MegRyan@star.com') INSERT EmailAddresses VALUES (4, 'Helen.Hunt@star.com') INSERT EmailAddresses VALUES (5, 'Jodie.Foster@star.com') CREATE FUNCTION dbo.GetEmails() RETURNS varchar(1000) /* Purpose: returns a list of Emails Parameters: Notes: Sample: SELECT dbo.GetEmails() AS ListEmails */ AS BEGIN DECLARE @Email varchar(50) DECLARE @Emails varchar(1000) SET @Emails = '' -- Create Emails Cursor DECLARE Emails CURSOR FOR SELECT Email FROM EmailAddresses ORDER BY Email OPEN Emails -- Open Emails Cursor --fetch first set of records from Emails Cursor FETCH NEXT FROM Emails INTO @Email WHILE @@FETCH_STATUS = 0 --if the fatch was successful BEGIN SET @Emails = @Emails + @Email + ',' --fetch next set of records from Emails Cursor FETCH NEXT FROM Emails INTO @Email END CLOSE Emails -- close Emails cursor DEALLOCATE Emails --deallocate Emails cursor --remove the extra comma IF Len(@Email)>0 SET @Emails = Left(@Emails, Len(@Emails)-1) RETURN @Emails END     The sample was tested on SQL Server 2000+.     The source query is not complicated, what if the content of the query is not known until runtime, all is known is that only one column of data is returned. Here interferes the magic of EXEC function and a table data type. --creating the sample table CREATE TABLE Countries(ID int, Country varchar(50)) --insert test records INSERT Countries VALUES (1, 'US') INSERT Countries VALUES (2, 'UK') INSERT Countries VALUES (3, 'Germany') INSERT Countries VALUES (4, 'Spain') INSERT Countries VALUES (5, 'France') CREATE PROCEDURE dbo.pListCountries AS SELECT Country FROM Countries ORDER BY Country CREATE FUNCTION dbo.GetList( @Query varchar(250)) RETURNS varchar(1000) /* Purpose: returns a list of List Parameters: Notes: Sample: SELECT dbo.GetList('SELECT Country FROM Countries ORDER BY Country') AS List */ AS BEGIN DECLARE @Result varchar(50) DECLARE @List varchar(1000) DECLARE @Temp TABLE (Val varchar(10)) SET @List = '' --insert in a table data type the results returned by query INSERT @Temp EXEC (@Query) -- Create List Cursor DECLARE List CURSOR FOR SELECT Val FROM @Temp OPEN List -- Open List Cursor --fetch first set of records from List Cursor FETCH NEXT FROM List INTO @Result WHILE @@FETCH_STATUS = 0 --if the fatch was successful BEGIN SET @List = @List + @Result + ',' --fetch next set of records from List Cursor FETCH NEXT FROM List INTO @Result END CLOSE List -- close List cursor DEALLOCATE List --deallocate List cursor --remove the extra comma IF Len(@Result)>0 SET @List = Left(@List, Len(@List)-1) RETURN @List END     Now let’s test the function using a simple SELECT SELECT dbo.GetList('SELECT Country FROM Countries ORDER BY Country') AS List Result: List -------------------------- France,Germany,Spain,UK,US (1 row(s) affected)     Let’s create a stored procedure using the table created above: CREATE PROCEDURE dbo.pListCountries AS SELECT Country FROM Countries ORDER BY Country     And use the stored procedure as parameter for GeList. SELECT dbo.GetList('dbo.pListCountries') AS List Result: List -------------------------- France,Germany,Spain,UK,US (1 row(s) affected)     Let’s create a table defined function based on the table created above: CREATE FUNCTION dbo.ListCountries() RETURNS TABLE AS RETURN(SELECT Country FROM Countries)     And use the stored procedure as parameter for GeList. SELECT dbo.GetList('SELECT * FROM ListCountries ORDER BY Country') AS List Result: List -------------------------- France,Germany,Spain,UK,US (1 row(s) affected)     Can be used as source any other table as long the length of the target column is less than 50 characters (@Result was defined as varchar(50)).     The method could be useful in SQL Server 2000, while in 2005 version can be used a managed table valued function instead .

No comments: