SQL Server Performance

If exists condition

Discussion in 'General Developer Questions' started by SQL DBA, Jun 20, 2008.

  1. SQL DBA New Member

    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
  2. waqar Member

    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.

Share This Page