SQL Server - CRUD operation using stored procedure for beginners (Part 1)

Hi friend!

In this tutorial, you'll learn how to perform CRUD operation using stored procedures. CRUD operation simply means to create, read, update and delete records in the database. A stored procedure also known as SP is a prepared SQL statements, that you can save in order to reuse. It act as a subroutine that can be execute to perform user predefined SQL statements. They are mostly used for data validation, crud operation, access control mechanism or to perform business logic in the database.

The most interesting part of using SQL Server stored procedure is the use of Transact SQL also known as T-SQL which give us the super power of performing programmable statements in SQL Server such as declaring variables, condition statements, loops, try catch blocks and lots more.

Prerequisites

This tutorial requires you have basic knowledge in structured query language (SQL) and you're familiar with Microsoft SQL Server management studio environment. Photo credit: Overview of microsoft sql server managemebnt studio

Requirements

I'll use We to refer to myself and the reader of this article as I want us to follow up as a team. We are going to build a simple stored procedure services for user management which will allow us to perform the following operations:

• Create user basic information
• Read the user information
• Update the user information
• Delete the user information

Also we are going to perform validation where necessary in order to have an error free stored procedure.

Steps to achieve our requirements

We are to follow these steps below

• Create Database
• Create Table to store users information
• Create our stored procedure
• Create each action types to perform our crud operation
• Test the stored procedure

Lets dive in

The following steps can be perform using the SQL Server Management Studio object explorer and table designer but this guide will show you how to use the query editor to achieve same purpose.

Create Database

Below is a sample snippet to create a database. If you want to know more about specifying options while creating a database check this guide .

CREATE DATABASE SAMPLE

Create Table

Just as I mentioned above, we can use the management studio to create a new table using the table designer; see guide here.

This sample use T-SQL in the query editor to create our user table.

In Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute.

CREATE TABLE dbo.USERS (
    USER_ID INT PRIMARY KEY IDENTITY (1, 1),
    FIRST_NAME VARCHAR (50) NOT NULL,
    LAST_NAME VARCHAR (50) NOT NULL, 
    EMAIL VARCHAR(200) NOT NULL,    
    PHONE VARCHAR(20) NOT NULL,
    CREATED_AT DATETIME DEFAULT GETDATE(),
    CONSTRAINT UK_EMAIL UNIQUE(EMAIL), 
    CONSTRAINT UK_PHONE UNIQUE(PHONE) 
);

In the snippet above, we created USERS table with USER_ID as the primary key. IDENTITY keyword is used to perform an auto-increment feature. The starting value for IDENTITY is 1, and it will increment by 1 for each new record. Our table is now created.

SQL Server comes with a default schema which is the dbo (DatabaseObject). When we create a table or database without specifying a schema attached, It automatically uses dbo schema for the table because the current user default schema is dbo.

A schema is a named container for database objects, which allows you to group objects into separate namespaces. Schema separation allows more flexibility in managing database objects permissions. See reference

Create Stored Procedure (Transact SQL)

In Object Explorer, connect to an instance of Database Engine. From the File menu, click New Query. Copy and paste the following example into the query window and click Execute. To learn more about stored procedures check this guide.

This example creates our user management stored procedure .

USE SAMPLE;
GO
-- =============================================
-- Author:        <Ahmed Olanrewaju>
-- Create date: <Sept 23, 2020>
-- Description:    <For user management>
-- =============================================
CREATE PROCEDURE DBO.USER_MANAGEMENT
    -- Add the parameters for the stored procedure here 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * FROM USERS
END
GO

Preview from management studio

Stored procedures are defined in a way to allow reusability of SQL statements. To make it dynamic, it allows parameter definition. This means the stored procedure can be executed with or without parameter. In the example above, when the stored procedure (SP) is executed it will return all records from user table as specified by this statement SELECT * FROM USERS.

Before we proceed, let's create our error log table to enable us catch error while executing the stored procedure.

  CREATE TABLE [dbo].[ERROR_LOG](
        ERROR_LOG_ID [bigint] IDENTITY(1,1) NOT NULL, 
        ERROR_NUMBER INT NULL,
        ERROR_LINE INT NULL,
        ERROR_MESSAGE VARCHAR (max) NULL,
        OBJECT_NAME VARCHAR(250) NULL, 
        CREATED_AT DATETIME NULL DEFAULT GETDATE()
    );

Paste the above snippet in your query editor and execute it.

Create each action types to perform our crud operation

An ACTION_TYPE parameter will be use to control the execution block for each of the CRUD operation. Let's name the first section CREATE, this performs creation of user information.

USE SAMPLE;
GO
ALTER PROCEDURE DBO.USER_MANAGEMENT
    --  PARAMETERS FOR STORED PROCEDURE
    @JSON_STRING NVARCHAR(MAX), @ACTION_TYPE VARCHAR(20)
    --  ======================================================== 
AS
    --  ========================================================
    --  DECLARE GLOBAL VARIABLES 
    DECLARE @USER_ID INT, @FIRST_NAME VARCHAR(50), @LAST_NAME VARCHAR(50),
    @EMAIL VARCHAR(200), @PHONE VARCHAR(20), @CREATED_AT DATETIME;

    --  ========================================================
    --  RESPONSE DECLARATIONS 
    DECLARE @RESPONSE_NUMBER INT = 1, @RESPONSE_MESSAGE VARCHAR(250), 
    @RESPONSE_DATA VARCHAR(MAX), @ERROR_OBJECT_NAME VARCHAR(50), 
    @ERROR_LINE BIGINT

BEGIN 

    IF @ACTION_TYPE = 'CREATE'
    BEGIN
        -- GET VALUES FROM @USER_JSON_STRING        
        SET @FIRST_NAME = JSON_VALUE(@JSON_STRING, '$.FIRST_NAME')    
        SET @LAST_NAME = JSON_VALUE(@JSON_STRING, '$.LAST_NAME')        
        SET @EMAIL = JSON_VALUE(@JSON_STRING, '$.EMAIL')        
        SET @PHONE = JSON_VALUE(@JSON_STRING, '$.PHONE')

        -- VALIDATE PARAMS 
        IF(@FIRST_NAME IS NULL OR @LAST_NAME IS NULL OR @EMAIL IS NULL OR @PHONE IS NULL)
        BEGIN

            SET @RESPONSE_NUMBER = -1
            SET @RESPONSE_MESSAGE = 'Missing field(s)!'
            SET @ERROR_OBJECT_NAME  = ERROR_PROCEDURE()
            SET @RESPONSE_DATA  = ''             
            SET @ERROR_OBJECT_NAME = ERROR_PROCEDURE()
            SET @ERROR_LINE = ERROR_LINE()

            GOTO ERR_HANDLER
        END    

        BEGIN TRY        
            -- CREATE USER
            INSERT INTO USERS(FIRST_NAME, LAST_NAME, EMAIL, PHONE)
            VALUES(@FIRST_NAME, @LAST_NAME, @EMAIL, @PHONE)

            SET @USER_ID = SCOPE_IDENTITY();

            SET @RESPONSE_NUMBER = 1
            SET @RESPONSE_MESSAGE = 'User successfully saved' 
            SET @RESPONSE_DATA  = (SELECT USER_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE  FROM USERS 
                                   WHERE USER_ID = @USER_ID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)             
        END TRY
        BEGIN CATCH
            SET @RESPONSE_NUMBER = ERROR_NUMBER()
            SET @RESPONSE_MESSAGE = ERROR_MESSAGE()
            SET @ERROR_OBJECT_NAME = ERROR_PROCEDURE()
            SET @ERROR_LINE = ERROR_LINE()
            GOTO ERR_HANDLER     
        END CATCH

    END

    --  ========================================================
    IF @@TRANCOUNT > 0
    COMMIT TRANSACTION ; SET XACT_ABORT OFF
    --  ========================================================

    --  ========================================================
    --RESPONSE SECTION
    ERR_HANDLER:
    IF @RESPONSE_NUMBER <> 1
    BEGIN             
        DECLARE @TRAN INT
        SELECT @TRAN = @@TRANCOUNT
        IF @TRAN > 1 COMMIT TRANSACTION
        IF @TRAN = 1 ROLLBACK TRANSACTION    

        INSERT INTO ERROR_LOG(ERROR_NUMBER, ERROR_LINE, ERROR_MESSAGE, OBJECT_NAME)
        VALUES(@RESPONSE_NUMBER, @ERROR_LINE, @RESPONSE_MESSAGE, @ERROR_OBJECT_NAME) 
    END

    SELECT @RESPONSE_NUMBER AS RESPONSE_NUMBER, @RESPONSE_MESSAGE AS RESPONSE_MESSAGE, 
    @RESPONSE_DATA AS RESPONSE_DATA, @ERROR_OBJECT_NAME AS [OBJECT_NAME]

END

In the above snippet, we made use of the T-SQL built-in IF statement to check if the ACTION_TYPE param is equal to CREATE which then allows the code block to execute. Another interesting part of this snippet is that we have handle how response will be return when the SP is executed.

Also take note of the ERR_HANDLER subroutine that helps us log error into the ERROR_LOG table we created earlier.

The above stored procedure can tested by executing this snippet in another query window

EXEC USER_MANAGEMENT @JSON_STRING=N'{"FIRST_NAME":"Lanre","LAST_NAME": "Omobukola",
"EMAIL":"lanre@domain.com","PHONE": "2348099889980"}', @ACTION_TYPE=N'CREATE'

Preview after executing the stored procedure

We can run a SELECT * FROM USERS to preview all saved users

Let's try to save duplicate data so that we can understand where our error log table becomes handy

You'll notice that, the compiled time error has been successfully catched and it display in the response table. This approach allows our stored procedure not to break but return a user friendly message. The response number denote SQL Server error number while the object name denote the stored procedure which is the error source.

Now that we have the above foundation put in place, it will be easy to scaffold READ, UPDATE AND DELETE action block.

Stay tuned for the update part of this article.