CODEWITHSUNDEEP
sql-server-2008t-sqlstored-procedures
ArchieTiger
ArchieTiger

Reputation: 2243

Create stored procedure to modify row if it exist and insert new row into table if it doesnt

enter image description here

How can I create a stored procedure to update the Std_Course's column based on the Id and Name parameter, OR inserts a new row if the Id and Name does not exist

Upvotes: 3

Views: 5864

Answers (1)

marc_s
marc_s

Reputation: 754953

Try something like this:

CREATE PROCEDURE dbo.proc_InsertOrUpdate
    @ID INT, @Name VARCHAR(50), @StdCourse INT
AS 

    IF EXISTS (SELECT * FROM dbo.YourTable WHERE ID = @ID AND Name = @Name)

        UPDATE dbo.YourTable
        SET Std_course = @StdCourse
        WHERE ID = @ID AND Name = @Name

    ELSE    

        INSERT INTO dbo.YourTable(ID, Name, Std_Course)
        VALUES(@ID, @Name, @StdCourse

Update: since you're on SQL Server 2008, you could also use a simple MERGE statement - either directly "inline" or inside the stored procedure. It would look something like this:

CREATE PROCEDURE dbo.proc_InsertOrUpdate
    @ID INT, @Name VARCHAR(50), @StdCourse INT
AS 
    MERGE dbo.YourTable AS t
    USING (SELECT @ID, @Name, @StdCourse) AS Source(ID, NAME, Std_Course) 
    ON source.ID = t.ID AND source.Name = t.Name

    WHEN MATCHED THEN
        UPDATE SET Std_Course = @StdCourse

    WHEN NOT MATCHED THEN
        INSERT(ID, Name, Std_Course)
        VALUES(source.ID, source.Name, source.Std_Course);

Upvotes: 3

Related Questions