Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Prepend Value to Delimited List Expand / Collapse
Author
Message
Posted Tuesday, March 8, 2011 9:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 29, 2013 2:54 PM
Points: 2, Visits: 50
Comments posted to this topic are about the item Prepend Value to Delimited List
Post #1075282
Posted Wednesday, March 9, 2011 2:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 17, 2014 6:38 AM
Points: 278, Visits: 534
In general, functions in SQL Server are a feature worth avoiding.
http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/Are-all-Scalar-User-Defined-Functions-UDF-always-bad.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlquiz+%28SQL+Server+Quiz%29.

The functions you suggest are probably the "best" or maybe "least-worst", in that they are scalar functions that access no database tables. They do require a small overhead over writing out the function inline, but on the other hand, they promote code-reuse and make the code easier to read.

However, the contents of your function
BEGIN
    -- If the delimiter passed was null, use an empty string.
    set @Delim = IsNull(@Delim, '')

    -- If the list is empty, set it equal to the value that was passed in.
    if (@ValueList IS NULL)
    begin
        set @ValueList = @Item
    end
    -- If the item passed in was not null, append it to the list.
    else if (@Item IS NOT NULL)
    begin
        set @ValueList = @Item + @Delim + @ValueList
    end

    RETURN @ValueList
END

can be re-written as
select @Item + ISNULL(IsNull(@Delim,'') + @ValueList,'')

and for a simple piece of code like this, I would prefer to write out inline and avoid the overhead of calling a function.
Post #1075395
Posted Wednesday, April 27, 2011 3:03 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:34 PM
Points: 790, Visits: 646



DECLARE @Str varchar(100) = 'FN,SP,TBL,091.889'

DECLARE @Separator VARCHAR(10) = ','

DECLARE @ValueAdd VARCHAR(10) = '80990'


DECLARE @Action VARCHAR(10) = 'R'--1. P..PostFix 2. R..Prefix

SELECT @Str =
STUFF((
SELECT

CASE WHEN @Action = 'R' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END
+
ISNULL(@Separator+@Str,'')
+
CASE WHEN @Action = 'P' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END
),1,1,'')

select @Str




Regards,
Mitesh OSwal
+918698619998
Post #1099259
Posted Wednesday, April 27, 2011 3:32 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:34 PM
Points: 790, Visits: 646

DECLARE @Numbertable table
(
ID INT PRIMARY KEY

)


INSERT INTO @Numbertable
select TOP 1000 ROW_NUMBER() OVER(order by si.object_id)
from sys.objects si,sys.objects s

DECLARE @separatolog TABLE
(
SeparatorLogID INT,
ID INT IDENTITY(1,1) PRIMARY KEY
)
DECLARE @Str varchar(100) = 'FN,SP,TBL,091.8891'

DECLARE @Separator VARCHAR(10) = ','

DECLARE @ValueAdd VARCHAR(10) = '80990'


DECLARE @Action VARCHAR(10) = '3'--1. P..PostFix 2. R..Prefix 3.Number for postion.

IF (ISNUMERIC(@Action) =0)

BEGIN
SELECT @Str =
STUFF((
SELECT

CASE WHEN @Action = 'R' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END
+
ISNULL(@Separator+@Str,'')
+
CASE WHEN @Action = 'P' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END
),1,1,'')

END
ELSE
BEGIN

INSERT INTO @separatolog

select distinct CHARINDEX(',',@str,ID)
from @Numbertable
where ID <= LEN(@str) and CHARINDEX(',',@str,ID) > 0
ORDER BY 1

DECLARE @CurrentSepartorID INT

SET @CurrentSepartorID = isnull((select SeparatorLogID
from @separatolog where ID = CAST(@Action AS INT)-1),CASE WHEN @Action = 1 THEN 0 ELSE LEN(@str)+1 END)


select @Str = STUFF(SUBSTRING(ISNULL(@Separator+@str,''),1,@CurrentSepartorID)
+ISNULL(@Separator+@ValueAdd,'')
+ISNULL(@Separator+NULLIF(SUBSTRING(@str,@CurrentSepartorID+1,LEN(@str)),''),''),1,1,'')
END

select @Str




Regards,
Mitesh OSwal
+918698619998
Post #1099272
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse