Thursday, October 29, 2009

T-SQL scalar function looping through a cursor

Scalar return valued function - example using a cursor, looping through the records, returning a string of data from all the rows in the cursor.  People say using a cursor in SQL server is not a great idea b/c of performance, so here's a link to do a simlar thing w/o a cursor:
http://www.sql-server-performance.com/articles/per/operations_no_cursors_p2.aspx



To call this function in a select:
select csl.dbo.testfn()

Function code:
CREATE Function dbo.testfn()
returns varchar(5000)
AS
BEGIN

/*
** Cursor method to cycle through the table
to retrieve data for each row
*/

-- declare all variables!
DECLARE @iLic int,
@nLicNum nvarchar(10),
@nLictype nvarchar(15) ,
@return nvarchar(2000)

-- declare the cursor
DECLARE Lic CURSOR FOR
SELECT TOP 10 pk_license,
LicenseNumber,
FK_LicenseType
FROM CSL.dbo.License

OPEN Lic
FETCH Lic INTO @iLic,
@nLicNum,
@nLictype

-- start the main processing loop.
set @return=''

WHILE @@Fetch_Status = 0

  BEGIN
  -- This is where you perform your detailed row-by-row processing.
  --concat fields
  set @return += '*' + convert(nvarchar, @iLic)
  set @return += '*' + @nLicNum
  set @return += '*' + @nLictype

  -- Get the next row.
  FETCH Lic INTO @iLic,
  @nLicNum,
  @nLictype
END

CLOSE Lic
DEALLOCATE Lic

RETURN @return

END

GO

No comments:

Post a Comment