March 8, 2011 at 9:09 pm
Comments posted to this topic are about the item Prepend Value to Delimited List
March 9, 2011 at 2:38 am
In general, functions in SQL Server are a feature worth avoiding.
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.
April 27, 2011 at 3:03 am
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
April 27, 2011 at 3:32 am
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
May 17, 2016 at 6:36 am
Thanks for the script. Nice continuation on the earlier one.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply