Using Split function in Stored Procedure for multiple parameters in SQL Server

Last Reply 9 days ago By dharmendr

Posted 9 days ago

hi,

how can i split comma separated values in two strings and save them as records along with other values

passing values to stored procedure from data access layer as (string shpVals, string paramid, string user, int AnalysisId, int valwith, int vesId)

values be like ('1.5,2.89,3.46,8.99,0.28','1027,1,285,367,8',user,1,2,8)

 desired output is

Shp_val

Param_ID

UserName

Analysis_Id

Valwith

VesID

1.5

1027

Xxx

1

2

8

2.89

1

Xxx

1

2

8

3.46

285

Xxx

1

2

8

8.99

367

Xxx

1

2

8

 i am using a split function which can split value in one string, how can i use it for multiple string values

ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return     

while @idx!= 0     
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0     
        set @slice = left(@String,@idx - 1)     
    else     
        set @slice = @String     

    if(len(@slice)>0)
        insert into @temptable(Items) values(@slice)     

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break     
end 
return     
end

 

You are viewing reply posted by: dharmendr 9 days ago.
Posted 9 days ago

Hi bkc,

Check the below sample procedure.

CREATE PROCEDURE SpitMultipleAsTable
	@shpVals VARCHAR(MAX)
	,@paramid VARCHAR(MAX)
	,@user VARCHAR(50)
	,@AnalysisId INT
	,@valwith INT
	,@vesId INT
AS
BEGIN
	SELECT Shp_val.Shp_val,Param_ID.Param_ID,@user UserName,@AnalysisId Analysis_Id,@valwith Valwith,@vesId VesID
	FROM
		(
		SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNo,Item Shp_val FROM dbo.SplitString(@shpVals, ',')
		) Shp_val,
		(
		SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNo,Item Param_ID FROM dbo.SplitString(@paramid, ',')
		) Param_ID
	WHERE Shp_val.RowNo=Param_ID.RowNo
END
GO
EXEC SpitMultipleAsTable '1.5,2.89,3.46,8.99,0.28','1027,1,285,367,8','xxx',1,2,8

For SplitString function refer below article.

Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server 2005, 2008 and 2012

Output

Shp_val Param_ID UserName Analysis_Id Valwith VesID
1.5 1027 xxx 1 2 8
2.89 1 xxx 1 2 8
3.46 285 xxx 1 2 8
8.99 367 xxx 1 2 8
0.28 8 xxx 1 2 8