If exists condition

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; .......)
For example, the table in the below format
UserID AcctID Level1 level2
test testee N Y
the SP have to get the first string of data and check if the Acctid exists or not. If yes then update else insert.Then get then the second string of data and check if the Acctid exists or not. If yes then update else insert.
After checking all the strings , it have to check if any Acctids other than acctid mentioned in the string exists in the table for that login, then delete those rows
Thanks in Advance

Hi,

First of all you need to see how the string is passed?
You mentioned Acctid1 level1 level2;AcctId2 level1 level2;....
I understand 2 different account information can be separated by ";" but how about AcctID and level information. I only see empty space between them?
Anyway what you need to do is to first break your string into a table, i copied this from a site (i didn't test it) .CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value int)
AS
begin
declare @dx varchar(9)
-- declare @loops int
--set @loops = 0

DECLARE @TempList table
(
Value int
)

if @delimiter is null set @delimiter = ' '
if len(@delimiter) < 1 set @delimiter = ' '
set @dx = left(@delimiter, len(@delimiter)-1)

DECLARE @Value varchar(8000), @Pos int

SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)

IF REPLACE(@Parameters, @delimiter, @dx) <> ''
BEGIN
WHILE @Pos > 0 -- AND @Loops < 100
BEGIN
--set @loops = @loops + 1
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
IF @Value <> ''
BEGIN
INSERT INTO @TempList (Value) VALUES (CAST(@Value AS INT)) --Use Appropriate conversion
END
SET @Parameters = SUBSTRING(@Parameters, @Pos+ len(@delimiter), 8000)
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)

END
END
INSERT @result
SELECT value
FROM @TempList
RETURN
END


once you will create this function you can test your string output with
SELECT * FROM dbo.ParmsToList('Acctid level1 level2; Acctid level1 level2; .......',';')
It will show you each Acctid in different row of table.


Now loop through the table and check each id if exists
IF EXISTS (SELECT * FROM TABLE WHERE USERID=@USERID AND AcctID=@AcctID)
UPDATE ...
ELSE
INSERT ...
You can keep AcctID in a TEMP table and once you gone through the loop you can do this
DELETE FROM TABLE WHERE NOT EXISTS (SELECT * FROM TEMPTABLE WHERE USERID=@USERID)

Hope this will help.