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
Thursday, October 29, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment