How to replace a table variable in T-SQL 2008 string contatenation
I have a UDF running in SQL 2000 that uses a table var to return a comma delimited string from several rows of data:
CREATE FUNCTION [dbo].[ReturnVisitsTaskNos]
( @MainlinePlanID int )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @TaskNoTable table
( TaskNo varchar (15) )
DECLARE @TaskList varchar(1000)
SET @TaskList = ''
INSERT INTO @TaskNoTable
SELECT TaskNo
FROM MainlinePlanTask
WHERE MainlinePlanID = @MainlinePlanID
开发者_开发百科 IF @@ROWCOUNT > 0
UPDATE @TaskNoTable
SET @TaskList = ( @TaskList + TaskNo + ', ' )
RETURN substring( @TaskList, 1, ( len( @TaskList ) - 1 ))
END
Is it possible to replace the table var code with something faster now that it is running on SQL 2008?
Thanks!
I can't vouch for the performance but if you use the FOR XML clause like this you will definitely get cleaner T-SQL in the function you have, without a temp table and cursor.
From the article:
Here is a very simple t-sql sample using FOR XML PATH() in order to sql concatenate strings values as a sample sql concatenation in MSSQL.
SELECT
STUFF(
(
SELECT
' ' + Description
FROM dbo.Brands
FOR XML PATH('')
), 1, 1, ''
) As concatenated_string
Your updated function might look somethign like this:
CREATE FUNCTION [dbo].[ReturnVisitsTaskNos]
( @MainlinePlanID int )
RETURNS varchar(1000)
AS
BEGIN
RETURN
SELECT STUFF((
SELECT
TaskNo + ', '
FROM MainlinePlanTask
WHERE MainlinePlanID = @MainlinePlanID
FOR XML PATH('')
), 1, 0, ''
) As concatenated_string
END
CREATE FUNCTION [dbo].[ReturnVisitsTaskNos]
( @MainlinePlanID int )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @TaskList varchar(1000)
SET @TaskList = ''
SELECT @TaskList = ( @TaskList + TaskNo + ', ' )
FROM MainlinePlanTask
WHERE MainlinePlanID = @MainlinePlanID
RETURN substring( @TaskList, 1, ( len( @TaskList ) - 1 ))
END
精彩评论