July 26, 2012 at 9:14 am
This seems to work as I would expect it to, but is there any reason that you can think it would not be a good idea?
CREATE TABLE TestTable (id tinyint primary key identity(1,1), name nvarchar(10));
INSERT INTO TestTable (name) VALUES ('a')
GO
CREATE PROCEDURE SaveTestTable @id tinyint, @name nvarchar(10) AS
BEGIN
MERGE INTO TestTable AS Target
USING (select @id as l_id, @name as l_name) as List
ON target.id = l_id
WHEN NOT MATCHED BY TARGET THEN
INSERT (name) VALUES (l_name)
WHEN MATCHED THEN
UPDATE SET target.name = l_name;
END
GO
SELECT * FROM TestTable
EXEC SaveTestTable @id = NULL, @name = 'b'
SELECT * FROM TestTable
EXEC SaveTestTable @id = 1, @name = 'c'
SELECT * FROM TestTable
EXEC SaveTestTable @id = 5, @name = 'e'
SELECT * FROM TestTable
DROP PROCEDURE SaveTestTable;
DROP TABLE TestTable;
I can see how I would potentially want to build in more handling so results are more expected (like when you provide a nonexistent id).
Does anyone know if this would cause plan cache issues?
Thanks for the help!
July 26, 2012 at 9:21 am
I'm not sure what you're asking here. Merge, as you've used it, is good for "upsert" operations (update/insert). That's largely what it was created for. As such, you seem to be using it standardly. But I'm not sure what you mean by "generic save" in the title of your post.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2012 at 9:24 am
I think you answered my question. I have never used the term "upsert", but that is exactly what I am doing.
"Generic Save" was the only thing I could come up with...
So this is an acceptable way to build an "upsert" SP?
July 26, 2012 at 11:18 am
Is it safe to put a delete switch in as well?
CREATE TABLE TestTable (id TINYINT PRIMARY KEY IDENTITY(1,1), name NVARCHAR(10));
INSERT INTO TestTable (name) VALUES ('a')
GO
CREATE PROCEDURE SaveTestTable @id TINYINT, @name NVARCHAR(10), @isDelete BIT = 0 AS
BEGIN
MERGE INTO TestTable AS TARGET
USING (select @id AS l_id, @name AS l_name) AS List
ON TARGET.id = l_id
WHEN NOT MATCHED AND @isDelete = 0 THEN
INSERT (name) VALUES (l_name)
WHEN MATCHED AND @isDelete = 1 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET TARGET.name = l_name;
END
GO
SELECT * FROM TestTable
EXEC SaveTestTable @id = null, @name = 'b'
SELECT * FROM TestTable
EXEC SaveTestTable @id = 1, @name = 'c'
SELECT * FROM TestTable
EXEC SaveTestTable @id = 5, @name = 'e'
SELECT * FROM TestTable
EXEC SaveTestTable @id = 3, @name = null, @isDelete =1
SELECT * FROM TestTable
DROP PROCEDURE SaveTestTable;
DROP TABLE TestTable;
July 26, 2012 at 11:28 am
Yes, as you've written it, it's a pretty standard and usual sort of proc. That's the way it's meant to be used.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2012 at 11:29 am
The additional condition is perfectly acceptible.
July 26, 2012 at 11:43 am
Fantastic thanks for the feedback!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply