Stored procedure that needs to handle multiple scenarios

user_9266521 0
Viewed 72

I have a rather complicated (at least for me) stored procedure to write that needs to handle multiple scenarios coming from the front end.

The frontend is passing 2 parameters that has values like this

@Levelmarker= (1234515-564546-65454,4654342-154658-56767,5465489-546549-65456)

These are GUIDS that are comma separated.

@`UserNameId= (5797823-65432143-65451213)

GUID of the user that entered this data on the front end

The values need to go to a table that has the following structure:

CREATE TABLE LevelTable 
(
    LevelId uniqueidentifier NOT NULL
    LevelMarker uniqueidentiriet NOT NULL
    UserName uniqueidentifier NOT NULL
);

I want the value to go into the table like this:

LevelId    Levelmarker              UserName
--------------------------------------------------------
NEWID()    1234515-564546-65454     5797823-654321-65451 
NEWID()    4654342-154658-56767     5797823-654321-65451
NEWID()    5465489-546549-65456     5797823-654321-65451

Here are the scenarios the stored procedure should handle.

  1. Once the levelmarkers are inserted into the table, if the same user comes back and wants to add additional Levelmarkers, the front end will pass the old values and the new ones as so: (1234515-564546-65454,4654342-154658-56767,5465489-546549-65456,1332245-9852135-7841265).

    My stored procedure should recognize that I already have the first three Levelmarkers in the table and should only insert the new ones.

  2. If the same user decides to delete values from before, lets say two values as an example, the front end will pass me the values (1234515-564546-65454,4654342-154658-56767). The stored procedure should recognize that the user has deleted two values and should delete the same values from the table and keep the non deleted ones.

  3. If the user deletes some values and inserts a new ones, then the stored procedure should recognize the ones to delete and insert the new ones.

What is the best approach to this problem?

sql-insert sql-delete stored-procedures sql-server string
1 Answers

I think you can do this in a single query, using string_split() and a merge statement:

merge leveltable t
using (
    select value levelmarker, @UserNameId username
    from string_split(@LeveMarker, ',')
) s
on (s.levelmarker = t.levelmarker and s.username = t.username)
when not matched by target  
    then insert (leveid,    levelmarker,   username) 
         values (newid(), s.levelmarker, s.username)
when not matched by source 
    then delete

In the using clause, we split the @LevelMarker parameter into new rows, and associate the given @UserNameId. Then, the merge statement checks if each combination already exists in the target table, and creates or deletes rows accordingly.

user_106767160
Related