Select, Insert, Update, Delete Using Stored Procedure in SQL Server

Follow me Sameer Gaikwad then follow the link below! 👉 💯day challenge 📩 Reach out to me on Twitter or Linkedin, or Blogspot if you want to discuss this further. :)

MS SQL SERVER

Getting Started with SQL

Asp.Net Core MVC with Bootstrap

ASP.NET Core Web App with Blazor

Microsoft Ado.Net Entity Framework

ASP.NET MVC 5 with Bootstrap

ASP.Net Interview Questions

JavaScript Interview Questions

Select, Insert, Update, Delete Using Stored Procedure in MS SQL Server “Getting Started with SQL: A Hands-On Approach for Beginners”

Introduction

Here, we will see how to create select, insert, update, and delete statements using stored procedures in SQL Server. Let’s take a look at a practical example. We create a table. full article check here read more

Creating Database

create database sameer

Creating Table

CREATE TABLE employee
(
    id         INTEGER NOT NULL PRIMARY KEY,
    first_name VARCHAR(10),
    last_name  VARCHAR(10),
    salary     DECIMAL(10, 2),
    city       VARCHAR(20),
)

SQL

Now add some rows to the table. We can add new rows using an INSERT INTO SQL statement. Then execute a SELECT SQL query to display all records of the table. full article check here read more

INSERT INTO employee
VALUES      (2,
             'Sameer',
             'Gaikwad',
             4789,
             'Agra');
go
INSERT INTO employee
VALUES      (4,
             'Rahul',
             'Gupta',
             5567,
             'London');
go
INSERT INTO employee
VALUES      (5,
             'prabhat',
             'kumar',
             4467,
             'Bombay');
go
INSERT INTO employee
VALUES      (6,
             'ramu',
             'Khan',
             3456,
             'jk');
go

SELECT *
FROM   employee

SQL

The table looks like this.

Figure 1

Stored Procedure for Select, Insert, Update, Delete

Here, we create a stored procedure with SELECT, INSERT, UPDATE, and DELETE SQL statements. The SELECT SQL statement is used to fetch rows from a database table. The INSERT statement is used to add new rows to a table. The UPDATE statement is used to edit and update the values of an existing record. The DELETE statement is used to delete records from a database table. The following SQL stored procedure is used insert, update, delete, and select rows from a table, depending on the statement type parameter. full article check here read more

ALTER PROCEDURE Masterinsertupdatedelete (@id            INTEGER,
                                          @first_name    VARCHAR(10),
                                          @last_name     VARCHAR(10),
                                          @salary        DECIMAL(10, 2),
                                          @city          VARCHAR(20),
                                          @StatementType NVARCHAR(20) = '')
AS
  BEGIN
      IF @StatementType = 'Insert'
        BEGIN
            INSERT INTO employee
                        (id,
                         first_name,
                         last_name,
                         salary,
                         city)
            VALUES     ( @id,
                         @first_name,
                         @last_name,
                         @salary,
                         @city)
        END
      IF @StatementType = 'Select'
        BEGIN
            SELECT *
            FROM   employee
        END
      IF @StatementType = 'Update'
        BEGIN
            UPDATE employee
            SET    first_name = @first_name,
                   last_name = @last_name,
                   salary = @salary,
                   city = @city
            WHERE  id = @id
        END
      ELSE IF @StatementType = 'Delete'
        BEGIN
            DELETE FROM employee
            WHERE  id = @id
        END
  END

SQL

Now press F5 to execute the stored procedure. This will create a new stored procedure in the database. full article check here read more

Now open object explorer and select storeprocedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = ‘Insert’

MasterInsertUpdateDelete -> right click select Execute Stored Procedure…

Figure 2

Execute procedure window will be opened.

Figure 3

Now for insert, we fill the data in values in the required fields.

StatementType=insert

Figure 4

Click on the OK button.

You will see a new row added to the database table.

Figure 5

Stored Procedure to Check update

MasterInsertUpdateDelete -> right-click select Execute Stored Procedure…

Execute procedure window will be opened. full article check here read more

StatementType = ‘Update’

Figure 6

Click on the OK button.

Check the employee table with the following updated data where id is 8.

Figure 7

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> Right-click select Execute Stored Procedure…

Execute procedure window will be opened. full article check here read more

StatementType = ‘Delete’

Figure 8

We will delete records from the table which has id=2.

Click on the OK button. And check in the employee table with the following deleted data where id is 2.

Figure 9

Summary

A single stored procedure can select, add, update, and delete data from a database table. This article taught us how to create a single stored procedure to perform all operations using a single SP in SQL Server.

full article check here read more

#sameergaikwadbymesameergaikwad #sameergaikwadcrudoperation #sameergaikwadmvccrudoperation #sameergaikwadjoinsinsqlserver #sameergaikwadsqlstoredprocedure #sameergaikwadsqltolinq