By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SomeTable (
column1 int
, column2 int
, column3 int
, column4 int
, column5 int
)
CREATE TABLE SomeTable_Debug (
id int identity
, createdDate datetime
, column1 int
, column2 int
, column3 int
, column4 int
, column5 int
)
INSERT INTO SomeTable
VALUES
(1,2,3,4,5)
, (2,3,4,5,1)
, (3,4,5,1,2)
, (4,5,1,2,3)
, (5,1,2,3,4)
;
5 rows affected
-- Simulate procedure execution 1
DECLARE @param1 int = 1
, @param2 int = 22
, @param3 int = 3
, @param4 int = 4
, @param5 int = 5
SELECT TOP 1 *
FROM SomeTable
WHERE column1 = @param1
AND column2 = @param2
AND column3 = @param3
AND column4 = @param4
ORDER BY column1
;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO SomeTable_Debug( createdDate, column1, column2, column3, column4, column5)
VALUES (getDate(), @param1, @param2, @param3, @param4, @param5)
END
| column1 | column2 | column3 | column4 | column5 |
|---|
-- Simulate procedure execution 2
DECLARE @param1 int = 1
, @param2 int = 22
, @param3 int = 3
, @param4 int = 4
, @param5 int = 56
SELECT TOP 1 *
FROM SomeTable
WHERE column1 = @param1
AND column2 = @param2
AND column3 = @param3
AND column4 = @param4
ORDER BY column1
;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO SomeTable_Debug( createdDate, column1, column2, column3, column4, column5)
VALUES (getDate(), @param1, @param2, @param3, @param4, @param5)
END
| column1 | column2 | column3 | column4 | column5 |
|---|
-- demo results
SELECT *
, (SELECT 1 WHERE NOT EXISTS (SELECT NULL FROM SomeTable st WHERE st.column1 = d.column1)) AS Matches_Column1
, (SELECT 1 WHERE NOT EXISTS (SELECT NULL FROM SomeTable st WHERE st.column2 = d.column2)) AS Matches_Column2
, (SELECT 1 WHERE NOT EXISTS (SELECT NULL FROM SomeTable st WHERE st.column3 = d.column3)) AS Matches_Column3
, (SELECT 1 WHERE NOT EXISTS (SELECT NULL FROM SomeTable st WHERE st.column4 = d.column4)) AS Matches_Column4
, (SELECT 1 WHERE NOT EXISTS (SELECT NULL FROM SomeTable st WHERE st.column5 = d.column5)) AS Matches_Column5
FROM SomeTable_Debug d
| id | createdDate | column1 | column2 | column3 | column4 | column5 | Matches_Column1 | Matches_Column2 | Matches_Column3 | Matches_Column4 | Matches_Column5 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2022-04-18 16:57:56.643 | 1 | 22 | 3 | 4 | 5 | null | 1 | null | null | null |
| 2 | 2022-04-18 16:57:56.643 | 1 | 22 | 3 | 4 | 56 | null | 1 | null | null | 1 |