CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
Denis Voronchuk
Denis Voronchuk

Reputation: 23

How to merge two stored procedures

I have two stored procedures one for INSERT and second for Update and I'd like to merge them in one so could you please tell me which satement should I use for that ? 

CREATE PROCEDURE Tools
(@CategoryID_1 int,
 @CategoryName_2 nvarchar(100),
 @Description_3 ntext)

AS INSERT INTO Categories 
 (CategoryID,
 CategoryName,
 Description) 

VALUES 
(@CategoryID_1,
 @CategoryName_2,
 @Description_3)

go

CREATE PROCEDURE Tools1

     (@CategoryID_1     [int],
 @CategoryID_2  [int],
 @CategoryName_3    [nvarchar](50),
 @Description_4     [ntext],

AS UPDATE [Teachdb].[dbo].[Categories] 

SET  [CategoryID]    = @CategoryID_2,
 [CategoryName]  = @CategoryName_3,
 [Description]   = @Description_4,

WHERE 
( [CategoryID]   = @CategoryID_1)
go

GO

Upvotes: 0

Views: 1394

Answers (2)

Dave Simione
Dave Simione

Reputation: 1441

If you are unable to change the structure of the stored procedure (by adding an additional parameter), you can use the MERGE statement.

http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx

One note, though - while I love the MERGE statement, there seems to be an increasing number of bugs being found with it. I suggest to not use it unless you understand what you are doing (no cargo cult programming), and have reviewed the issues to see if your scenario is included.

Upvotes: 0

Naresh Pansuriya
Naresh Pansuriya

Reputation: 2045

Here you can merge two store procedure code in below way by passing @Mode value..

/*
For Insert 
exec Tools @Mode='Insert', @CategoryID='1', @CategoryName='DemoCat', @Description='demoDesc'

For Update
exec Tools @Mode='UPDATE', @CategoryID='1', @CategoryName='DemoCatupdate', @Description='demoDescupdate'

*/

CREATE PROCEDURE Tools
(
    @Mode nvarchar(100), -- Use for insert or Update
    @CategoryID int,
    @CategoryName nvarchar(100),
    @Description ntext
)

AS 
BEGIN 
    IF @Mode = 'INSERT'
    BEGIN
        INSERT INTO Categories (CategoryID, CategoryName, Description) 
        VALUES (@CategoryID, @CategoryName, @Description)
    END
    ELSE IF @Mode = 'UPDATE'
    BEGIN
        UPDATE [dbo].[Categories] 
            SET  [CategoryID]    = @CategoryID,
                [CategoryName]  = @CategoryName,
                [Description]   = @Description
        WHERE ([CategoryID]   = @CategoryID)
    END
END
go

Upvotes: 1

Related Questions