How to create stored procedure for update/insert all combinations in SQL

Question

I have table like

Col1, Col2, Col3, Col4
----  ----  ----
1      0     1     sd
1      0     2     asdas
1      1     1     sd
1      1     2     ads  
2      0     1     sad
2      0     2     ds
2      1     1     sad
2      1     2     sad

This table represent all possibilities that I have.I need to update or insert row. If one row in above table does not exist I will insert new row. If I send -1 for Col1 or Col2 or Col3, I need to update/insert all variants of that column.

Col1 accept 1 and 2.

Col2 accept 0 and 1.

Col3 accept 1 and 2.

For example:

Col1 = -1, Col2 = 1, Col3 =  -1, Col4 = test

I need to update/insert next columns

Col1, Col2, Col3, Col4
----  ----  ----    
1      1     1     test
1      1     2     test      
2      1     1     test
2      1     2     test

Can you help me with stored procedure?

CREATE PROCEDURE [dbo].[MyStoredProcedure]
(
   @Col1 int,    
   @Col2 int,
   @Col3 int,  
   @Col4 uniqueidentifier
)

What platform and version please? Also, when you send -1 how are these values to be chosen? On existing values (are they complete?) or from some other table (which would make more sense) — gbn
You said "I need to update/insert next columns" but the sample data is already contained in the first set of data. We can't answer this question as it is — gbn
First table represent all possibilities. First you create table and table is empty, you call sp and sp for example insert 1 row. Then you call again sp and now you update 1 row and insert 3 rows. Then you delete one row. Call again sp and do update/insert. — cashmere
I don't know how much effort you put into it before posting the question, but I think what you are asking is perfectly clear. — Kevin Suchlicki

Kevin Suchlicki Kevin Suchlicki · Accepted Answer · 2013-05-14T15:28:36

You can use the proc below (change column4 to whatever data type you prefer). I used MERGE, it's probably fine here... others may prefer storing values in a temp table and then doing UPDATE followed by INSERT (since MERGE has some known issues... I still like it though ;) ).

CREATE PROCEDURE [dbo].[MyStoredProcedure]
(
   @col1 INT,    
   @col2 INT,
   @col3 INT,  
   @col4 VARCHAR(MAX)
)
AS
-- 3 tables to contain possible values for columns 1,2,3 (these should really be in separate lookup tables)
;WITH C1Values AS (
    SELECT 1 AS v
        UNION ALL SELECT 2
)
, C2Values AS (
    SELECT 0 AS v
    UNION ALL SELECT 1
)
, C3Values AS (
    SELECT 1 AS v
    UNION ALL SELECT 2
) 
MERGE MyTable
USING (
    -- build all permutations that match input parameters
    SELECT  c1.v, c2.v, c3.v
    FROM    C1Values c1
            CROSS JOIN C2Values c2
            CROSS JOIN C3Values c3
    WHERE   c1.v = CASE WHEN @col1 = -1 THEN c1.v ELSE @col1 END
            AND c2.v = CASE WHEN @col2 = -1 THEN c2.v ELSE @col2 END
            AND c3.v = CASE WHEN @col3 = -1 THEN c3.v ELSE @col3 END
) AS src (c1, c2, c3)
ON MyTable.Col1 = c1 AND MyTable.Col2 = c2 AND MyTable.Col3 = c3
WHEN NOT MATCHED THEN
    INSERT (Col1, Col2, Col3, Col4)
    VALUES (src.c1, src.c2, src.c3, @col4)
WHEN MATCHED THEN
    UPDATE
    SET     MyTable.Col4 = @col4;
GO

How to create stored procedure for update/insert all combinations in SQL

1 Answers