CSV value passed in SQL Procdure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CSV value passed in SQL Procdure

Author

Topic

faijurrahuman
Starting Member

15 Posts

Posted - 2012-02-25 : 12:08:05

Hi ;

This is my procedure input parameter , I want split the column name and column value to insert the temp table, and next to move the main table production table.

Temp table like columnname and value

Example

Create Proc sample
(
@name VARCHAR(MAX)
)

@NAME='column1=value1,column2=value2,column3=value3'

Create Table #temp(columnname varchar(max),value varchar(max))

My Question :

1.How can I split the input parameter in desired format
2.How to find the column Value

any help would be appreciated

Share Knowledge team

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 12:28:21

you need a string parsing function for that like below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

faijurrahuman
Starting Member

15 Posts

Posted - 2012-02-26 : 00:49:56

thanks for replay

please given your valuable query for desired output format ;

Share Knowledge team

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-02-26 : 02:54:19

Please find the answer:

CREATE FUNCTION ParseValues
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

select * into #tmp from dbo.ParseValues ('column1=value1,column2=value2,column3=value3',',')

alter table #tmp add Data varchar(20)

alter table #tmp add value varchar(20)

update #tmp set val = val + '='
from #tmp

update #tmp set Data = substring(val,1,CHARINDEX('=',val,1)-1),
value = substring(val,(CHARINDEX('=',val,1)+1),(CHARINDEX('=',val,(CHARINDEX('=',val,1)+1)))-1)
from #tmp

update #tmp set value1 =REPLACE(value1,'=','')
from #tmp

alter table #tmp drop column val

select * from #tmp

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-02-26 : 02:59:12

correction Please replace below code

update #tmp set value1 =REPLACE(value1,'=','')
from #tmp

to

update #tmp set value =REPLACE(value,'=','')
from #tmp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-26 : 15:06:47

and just to give you other ides, you might be better off sending the parameter as a xml document so that you can dispense with parsing the string and getting values but you can use improved xml function like nodes() and value() to get values instead

see an example here

http://visakhm.blogspot.com/2010/04/using-xml-to-batch-load-master-child.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources