1. u should use loop inside stored procedure
2.firstly send multiple rows in single string and split string in procedure
or u can use table type variable
u should try like this
CREATE PROCEDURE [dbo].[spHRSaveSkillsDtls]
/**************************************************************************************************************
EXEC spHRSaveSkillsDtls @CandidateID=1,@SkillsDtls='0±1±P±1±1±$123±10±S±1±$124±12±P±1$'
***********************************************************************/
@CandidateID VARCHAR(20),
@SkillsDtls VARCHAR(5000)
AS
BEGIN
DECLARE
@len INT,@pos1 INT,@pos2 INT,@pos3 INT,@pos4 INT,@pos5 INT,@i INT,@row VARCHAR(50),@rowpos INT,@temp INT,
@SkillsDtlsID INT,@SkillsID SMALLINT,@SkillsType VARCHAR(10),@Active TINYINT,@DisOrder TINYINT
SET @len=LEN(@SkillsDtls)
SET @i=1
SET @temp=0
WHILE(@i<@len)
BEGIN
SET @rowpos=CHARINDEX('$',@SkillsDtls,@i)
SET @row=SUBSTRING(@SkillsDtls,@i,@rowpos-@temp-1)
SET @Pos1=CHARINDEX('±',@row,1)
SET @SkillsDtlsID= SUBSTRING(@row,1,@pos1-1)
SET @pos2=CHARINDEX('±',@row,@pos1+1)
SET @SkillsID =SUBSTRING(@row,@pos1+1,@pos2-@pos1-1)
SET @pos3=CHARINDEX('±',@row,@pos2+1)
SET @SkillsType=SUBSTRING(@row,@pos2+1,@pos3-@pos2-1)
SET @pos4=CHARINDEX('±',@row,@pos3+1)
SET @Active=SUBSTRING(@row,@pos3+1,@pos4-@pos3-1)
SET @pos5=CHARINDEX('±',@row,@pos4+1)
SET @DisOrder=SUBSTRING(@row,@pos4+1,@pos5-@pos4-1)
SELECT @pos1=0,@pos2=0,@pos3=0,@pos4=0,@pos5=0
SET @i=@rowpos+1
SET @temp=@rowpos
SET @row=null
IF(@SkillsDtlsID=0)
BEGIN
--SET @DisOrder=(SELECT MAX(@DisOrder )FROM tbHRCandidateSkillsDtls)
--SET @DisOrder=@DisOrder+1
INSERT INTO tbHRCandidateSkillsDtls(CandidateID,SkillsID,SkillsType,Active,DisOrder)
VALUES(@CandidateID,@SkillsID,@SkillsType,@Active,@DisOrder)
END
ELSE
BEGIN
UPDATE tbHRCandidateSkillsDtls SET CandidateID=@CandidateID,SkillsID=@SkillsID,SkillsType=@SkillsType,Active=@Active
WHERE SkillsDtlsID=@SkillsDtlsID
END
END
END