Parse values in SQL SP

[sqldba]

Hi Please help me I have to create a SP. The secenario is that, A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......) UserID AcctID Level1 level2 test testee N Y eg: the string of value from application will be as (SVDB, N, Y; VGBY, Y, B; SCFV, Y, S; SRVD, Y, N; .....) The SP have to get the fist set of data i.e SVDB, N, Y , check if SVDB exists in the table for that login, if yes update else insert. Finally do acheck and delete the Acctid not available in above list Im using SQL 7 server. Please help me in doing this Thanks in advance

[leoinfo]

See below...

DECLARE @s NVARCHAR(4000);
SET @s = '(SVDB, N, Y; VGBY, Y, B; SCFV, Y, S; SRVD, Y, N; SRVD, Y, N; )';

/* START PROCESSING */;
SET @s = REPLACE(REPLACE(REPLACE( @s ,' ','') ,'(','') ,')','');

CREATE TABLE #S (pk INT IDENTITY(1,1), line NVARCHAR(100));
CREATE TABLE #T (col1 NVARCHAR(4), col2 NVARCHAR(1), col3 NVARCHAR(1));

DECLARE @p1 INT;
DECLARE @p2 INT;
SET @p1 = 1;
SET @p2 = CHARINDEX(';' , @s);

WHILE @p1 < @p2
BEGIN
INSERT INTO #S (line) SELECT SUBSTRING(@s, @p1, @p2-@p1);
SET @s = SUBSTRING(@s, @p2+1, LEN(@s));
SET @p1 = 1;
SET @p2 = CHARINDEX(';' , @s);
END

DECLARE @k INT;SET @k = 1;
DECLARE @max INT;
SELECT @max = MAX(pk) FROM #S;

WHILE @k < @max
BEGIN
SELECT @s =
'INSERT INTO #T (col1 , col2 , col3) SELECT '''
+ REPLACE( line , ',', ''',''') + '''' FROM #S WHERE pk = @k;
EXEC(@s);
SET @k = @k + 1;
END

DROP TABLE #S;

SELECT * FROM #T;

/* ;
......
Do what ever you have to do with data in #T table
.....
*/ ;

DROP TABLE #T;
/* END PROCESSING */

Tell me please if this works. Good luck! :)

modified on Thursday, July 10, 2008 1:38 PM