Insert/Update SQL Server Stored Procedure

Question

I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and managed to create a stored procedure using MERGE. The table looks likes this,

bl em number title email fl name opt
null 101 00659 ABC null 01 John null
null 102 00660 DEF null 02 Rosy null

And the procedure:

CREATE PROCEDURE [dbo].[uspInsUpd]
 @bl char(8),
 @em char(35),
 @number char(12),
 @title varchar(64),
 @email varchar(50),
 @fl char(4),
 @name char(32),
 @opt char(16),
AS
MERGE [dbo].[employee] AS [Target]
USING (SELECT @bl, @em , @number, @title, @email, @fl, @name, @opt)
 AS [Source] ( [bl], [em], [number], [title], [email], [fl], [name], [opt]) 
ON [Target].[em] = [Source].[em]
WHEN MATCHED THEN
 UPDATE SET [bl] = [Source].[bl],
 [em] = [Source].[em],
 [number] = [Source].[number],
 [title] = [Source].[title],
 [email] = [Source].[email],
 [fl] = [Source].[fl],
 [name] = [Source].[name],
 [opt] = [Source].[opt],
WHEN NOT MATCHED THEN
 INSERT ( [bl], [number], [email], [fl], [name], [opt], [em],[title])
 VALUES ( [Source].[bl], [Source].[number], [Source].[email], [Source].[fl], [Source].[name], [Source].[opt], [Source].[em], [Source].[title]);
GO

Now I am not sure how can I test this stored procedure. Can anybody tell me if my above code is right?

• Aaron Bertrand: Please MERGE with caution - for updating or inserting a single row, why not UPDATE ... IF @@ROWCOUNT = 0 INSERT?
• user2698249: Here it is going to be a set of records from a table.
• Aaron Bertrand: How is that possible? Your procedure accepts exactly one value for each parameter and these map 1:1 with the columns in the table. Anyway, even if it's multiple rows and you pass via TVP or similar, then you can still do UPDATE with a JOIN, and INSERT ... WHERE NOT EXISTS.

Answer 1

As a very generic answer, if you want to test code of this nature without actually changing the data then it is possible to run your code, query the results, and then undo any changes before they are committed.

-- Begin a new transaction
begin transaction
 -- Show initial data
 select * from [dbo].[employee]
 -- Your statement to change the data
 MERGE [dbo].[employee] AS [Target]
 ....
 ....
 ....
 -- Show the new data
 select * from [dbo].[employee]
-- Rollback the transaction, leaving the data in its original state.
rollback transaction