November 11, 2013 at 12:19 am
Hi ,
I need the store procedure which i need to get the comma seperator values result in different columns and pipe line seperator in in next row
i am passing the data through parameter values are suppose (4,2,true,true,true,false|4,2,true,true,true,true)
pipeline seperator is in next row
142TRUETRUEFALSEFALSE
242TRUETRUETRUE FALSE
then the result set i need to update in the database which base on primary key first column is id column which is my primary key
Can any one help me 🙂
November 11, 2013 at 1:42 am
I think you'd better use table valued parameters rather than concatenated values.
An example is available here:
http://www.codeproject.com/Articles/37174/Table-Value-Parameter-in-SQL-Server-2008
-- Gianluca Sartori
November 11, 2013 at 1:51 am
create function dbo.SplitString
(
@STR nvarchar(max),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
cast(1 as bigint),
cast(1 as bigint),
charindex(@separator, @STR)
union all
select
p + 1,
b + 1,
charindex(@separator, @STR, b + 1)
from tokens
where b > 0
)
select
p-1 ItemIndex,
substring(
@STR,
a,
case when b > 0 then b-a ELSE LEN(@str) end)
AS Item
from tokens
);
USe this function
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 11, 2013 at 2:27 am
Hi , Thanks for your reply
i had tried this already if i pass the data in the paramater as ('1,4,2,2|2,4,3,5','|')
I am getting the result set as
ItemIndexItem
0 1,4,2,2
1 2,4,3,5
now i need the 'Item' column data into different columns
as
ID Item1 Item2 Item3
1 4 2 2
2 4 3 5
November 11, 2013 at 12:09 pm
Jeff Moden has an excellent splitter here[/url] that performs extremely quickly. Once you have that, you can do the following
select rowseq,
min(col1) item1,
min(col2) item2,
min(col3) item3,
min(col4) item4,
min(col5) item5,
min(col6) item6
from (
select ps.itemnumber rowseq,
case when cs.ItemNumber = 1 then cs.Item else null end col1,
case when cs.ItemNumber = 2 then cs.Item else null end col2,
case when cs.ItemNumber = 3 then cs.Item else null end col3,
case when cs.ItemNumber = 4 then cs.Item else null end col4,
case when cs.ItemNumber = 5 then cs.Item else null end col5,
case when cs.ItemNumber = 6 then cs.Item else null end col6
from dbo.DelimitedSplit8K( '4,2,true,true,true,false|4,2,true,true,true,true' , '|') ps -- Split the string into rows on |
cross apply ( select * from dbo.DelimitedSplit8K( ps.item , ',') ) cs -- Split the rows on ,'s
) a
group by rowseq
The previous splitter function uses a counting recursive CTE. This can be a real performance killer[/url].
If you are only doing four values like your second example string, the query could be reduced to the following
select parsename(replace(item,',','.'), 4) id,
parsename(replace(item,',','.'), 3) item1,
parsename(replace(item,',','.'), 2) item2,
parsename(replace(item,',','.'), 1) item3
from dbo.DelimitedSplit8K( '1,4,2,2|2,4,3,5' , '|');
November 11, 2013 at 2:13 pm
Bhuvnesh (11/11/2013)
create function dbo.SplitString(
@STR nvarchar(max),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
cast(1 as bigint),
cast(1 as bigint),
charindex(@separator, @STR)
union all
select
p + 1,
b + 1,
charindex(@separator, @STR, b + 1)
from tokens
where b > 0
)
select
p-1 ItemIndex,
substring(
@STR,
a,
case when b > 0 then b-a ELSE LEN(@str) end)
AS Item
from tokens
);
USe this function
Have you ever tested that for performance?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2013 at 3:30 am
Jeff Moden (11/11/2013)
Have you ever tested that for performance?
I tested it for small strings but not for big ones.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 12, 2013 at 3:39 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[USP_UpdateGroupAccess](@VALUE NVARCHAR(MAX),@STRING NVARCHAR(10))
AS
CREATE TABLE #GROUPACCESS(ID INT, DATA VARCHAR(100))
DECLARE @TEMP1 TABLE
(ID INT IDENTITY(1,1),
VALUE VARCHAR(MAX) )
INSERT INTO @TEMP1 SELECT * FROM DBO.SPLIT(@VALUE,@STRING)
--- DBO.SPLIT FUNCTION WILL SPLIT THE DATA INTO DIFFERENT ROW DEPENDS ON THE STRING WHICH WE WILL BE PASSING
----SELECT * FROM @TEMP1
INSERT INTO #GROUPACCESS SELECT * FROM @TEMP1
DECLARE @PIVOT VARCHAR(MAX)
DECLARE @SELECT VARCHAR(MAX)
SELECT
@PIVOT=COALESCE(@PIVOT+',','')+'[COL'+CAST(NUMBER+1 AS VARCHAR(10))+']'
FROM
MASTER..SPT_VALUES WHERE TYPE='P' AND
NUMBER<=(SELECT MAX(LEN(DATA)-LEN(REPLACE(DATA,',',''))) FROM #GROUPACCESS)
--- THIS SELECT STATEMENT WILL SPLIT THE DATA ROWS INTO COLUMNS USING THE PIVOT THE KEY FROM THE RESULT SET WHICH HAS BEEN STORED IN THE @TEMP1
SELECT
@SELECT='
SELECT P.*
FROM (
SELECT
ID,SUBSTRING(DATA, START+2, ENDPOS-START-2) AS TOKEN,
''COL''+CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY START) AS VARCHAR(10)) AS N
FROM (
SELECT
ID, DATA, N AS START, CHARINDEX('','',DATA,N+2) ENDPOS
FROM (SELECT NUMBER AS N FROM MASTER..SPT_VALUES WHERE TYPE=''P'') NUM
CROSS JOIN
(
SELECT
ID, '','' + DATA +'','' AS DATA
FROM
#GROUPACCESS
) M
WHERE N < LEN(DATA)-1
AND SUBSTRING(DATA,N+1,1) = '','') AS DATA
) PVT
PIVOT ( MAX(TOKEN)FOR N IN ('+@PIVOT+'))P'
-- EXEC(@SELECT) -- IF WE NEED TO CHECK THE RESULT FROM THE STORE PROCEDURE
CREATE TABLE #ADGROUPACCESS(ID INT NOT NULL,
[ADGROUPACCESSID] [SMALLINT] NOT NULL,
[ADGROUPID] [SMALLINT] NOT NULL,
[SUBFEATUREID] [SMALLINT] NOT NULL,
[ISVIEWALLOWED] [BIT] NULL,
[ISADDALLOWED] [BIT] NULL,
[ISDELETEALLOWED] [BIT] NULL,
[ISEDITALLOWED] [BIT] NULL,
[ISAUTHALLOWED] [BIT] NULL
)
INSERT INTO #ADGROUPACCESS EXEC(@SELECT)
SELECT * FROM #ADGROUPACCESS
UPDATE [ADGROUPACCESS] SET [ADGROUPID]= #ADGROUPACCESS.[ADGROUPID]
,[SUBFEATUREID]=#ADGROUPACCESS.[SUBFEATUREID]
,[ISVIEWALLOWED]=#ADGROUPACCESS.[ISVIEWALLOWED]
,[ISADDALLOWED]=#ADGROUPACCESS.[ISADDALLOWED]
,[ISDELETEALLOWED]=#ADGROUPACCESS.[ISDELETEALLOWED]
,[ISEDITALLOWED]=#ADGROUPACCESS.[ISEDITALLOWED]
,[ISAUTHALLOWED]=#ADGROUPACCESS.[ISAUTHALLOWED]
FROM [ADGROUPACCESS],#ADGROUPACCESS WHERE #ADGROUPACCESS.[ADGROUPACCESSID]=[ADGROUPACCESS].[ADGROUPACCESSID]
DROP TABLE #GROUPACCESS
DROP TABLE #ADGROUPACCESS
--EXECUTE USP_UPDATEGROUPACCESS '1,4,2,TRUE,TRUE,False,False,TRUE|2,4,2,TRUE,TRUE,False,TRUE,False','|'
try this
November 12, 2013 at 8:03 am
Bhuvnesh (11/12/2013)
Jeff Moden (11/11/2013)
Have you ever tested that for performance?I tested it for small strings but not for big ones.
I kind'a figured that. The code you're using is known as an "inch worm" splitter and, although I've not tested your specific code yet, they tend to be comparatively slow in the 8K range. I'll try to find the time to gin up a test to demonstrate what I'm talking about. In the mean time, please have a look at the following articles. The first explains a different method and the second explains why you might not want to use an rCTE for such a thing.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2013 at 11:36 am
Hi nhimabindhu
Looking at the procedure that you posted, I think this is what you are trying to do.
Note that I am using the DelimitedSplit8k[/url] function previously mentioned.
Create PROCEDURE [dbo].[USP_UpdateGroupAccess](
@VALUE VARCHAR(8000), -- HOW BIG IS THIS LIKELY TO GET?
@STRING VARCHAR(1)
)
AS
BEGIN
WITH updateSet AS (
SELECT rowseq,
adgroupaccessid = min(col1) ,
adgroupid = min(col2) ,
subfeatureid = min(col3) ,
isviewallowed = min(col4) ,
isaddallowed = min(col5) ,
isdeleteallowed = min(col6) ,
iseditallowed = min(col7) ,
isauthallowed = min(col8)
FROM (
SELECT ps.itemnumber rowseq,
case when cs.ItemNumber = 1 then cs.Item else null end col1,
case when cs.ItemNumber = 2 then cs.Item else null end col2,
case when cs.ItemNumber = 3 then cs.Item else null end col3,
case when cs.ItemNumber = 4 then cs.Item else null end col4,
case when cs.ItemNumber = 5 then cs.Item else null end col5,
case when cs.ItemNumber = 6 then cs.Item else null end col6,
case when cs.ItemNumber = 7 then cs.Item else null end col7,
case when cs.ItemNumber = 8 then cs.Item else null end col8
FROM dbo.DelimitedSplit8K( @VALUE , @STRING) ps -- Split the string into rows on |
CROSS APPLY ( SELECT * FROM dbo.DelimitedSplit8K( ps.item , ',') ) cs -- Split the rows on ,'s
) a
GROUP BY rowseq
)
SELECT -- UNCOMMENT THIS AND COMMENT NEXT TO TEST
-- UPDATE A SET --UNCOMMENT THIS AND COMMENT PREV TO UPDATE
adgroupaccessid = u.adgroupaccessid ,
adgroupid = u.adgroupid ,
subfeatureid = u.subfeatureid ,
isviewallowed = u.isviewallowed ,
isaddallowed = u.isaddallowed ,
isdeleteallowed = u.isdeleteallowed ,
iseditallowed = u.iseditallowed ,
isauthallowed = u.isauthallowed
FROM updateSet u
INNER JOIN [ADGROUPACCESS] A ON a.[ADGROUPACCESSID]=u.[ADGROUPACCESSID];
END
November 12, 2013 at 6:09 pm
Jeff Moden (11/12/2013)
The code you're using is known as an "inch worm" splitter
It's been awhile since my high school biology days, but don't inch worms move pretty slow?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 12, 2013 at 6:15 pm
dwain.c (11/12/2013)
Jeff Moden (11/12/2013)
The code you're using is known as an "inch worm" splitterIt's been awhile since my high school biology days, but don't inch worms move pretty slow?
Especially after you've dissected them:-D
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply