A Comprehensive Guide to Creating Databases, Tables, and CRUD Stored Procedures in SQL Server

01 May, 2023

READ TIME - 10 MINUTES

Introduction:

SQL Server is a powerful relational database management system (RDBMS) widely used for storing, managing, and retrieving data. In this blog post, we will explore the essential concepts of creating databases, tables, and implementing CRUD (Create, Read, Update, Delete) operations using stored procedures in SQL Server. Whether you are a beginner or an experienced developer, this guide will provide you with a solid foundation for working with SQL Server databases efficiently and professionally.

Note: The scripts contain introductory comments with author, creation date, and update date information for each stored procedure. They also describe the purpose of the procedures. Instructions for executing the procedure using EXEC command are included. To run a specific procedure, uncomment the corresponding "EXEC" command. Avoid using the sp_ prefix as it's reserved for the master database. Keeping the comments is recommended for developer's understanding.

Creating a Database:

First, we need to log in to SQL Server Management Studio. Then, in order to create a new database on our server, we need to use the following command:

Here, our database name is CrudDb. Afterward, we can run the following commands:

The USE database_name command is used to specify the database context as CrudDb. This ensures that all subsequent queries executed after the USE statement will be applied to the specified database.

Therefore, when you see the statement USE CrudDb, it means that the subsequent queries in the script or session will be executed against the CrudDb database. This is useful when you have multiple databases in your SQL Server instance and want to ensure that your queries operate within a specific database. The GO keyword is used as a batch separator in SQL Server Management Studio to separate and execute different batches of commands.

Creating Table:

The provided query creates a table named Employees in a SQL Server database. Here's a breakdown of the table's structure:

Id: This column is of type INT and serves as the primary key for the table. It is set as NOT NULL and uses the IDENTITY(1,1) property, which means it will auto-increment by 1 starting from 1.

FirstName: This column is of type NVARCHAR(450) and represents the first name of an employee. It is set as NOT NULL, indicating that a value must be provided for every record.

LastName: This column is of type NVARCHAR(450) and represents the last name of an employee. It is allowed to be NULL, meaning it can be left empty if necessary.

Email: This column is of type NVARCHAR(450) and stores the email address of an employee. It is set as NOT NULL, ensuring that an email value is provided for each record.

PhoneNumber: This column is of type NVARCHAR(450) and stores the phone number of an employee. It is set as NOT NULL to ensure that a phone number is provided for every record.

Address: This column is of type NVARCHAR(MAX) and represents the address of an employee. It is set as NOT NULL to ensure that an address is provided for every record. The NVARCHAR(MAX) data type allows for storing large amounts of Unicode text.

Salary: This column is of type FLOAT and stores the salary of an employee. It is set as NOT NULL, indicating that a salary value must be provided for each record.

JoiningDate: This column is of type DATETIME and represents the date when an employee joined the company. It is set as NOT NULL to ensure that a joining date is provided for every record.

PermanentDate: This column is of type DATETIME and represents the date when an employee became a permanent employee. It is allowed to be NULL, indicating that it may not have a value if the employee is not yet permanent or if the information is not available.

By creating this table, you have defined the structure to store employee-related information in an organized manner, with appropriate constraints to ensure data integrity.

Now, we will create our CRUD stored procedure.

Create Stored Procedure:

It creates a stored procedure named [dbo].[usp_Employees_Insert] in the CrudDb database. Let's go through the description and functionality of this stored procedure:

This stored procedure is used to perform an insertion operation in the Employees table. It checks if the provided email already exists in the Employees table before inserting a new employee record.

Inputs:
@FirstName: Employee's first name (nvarchar)
@LastName: Employee's last name (nvarchar)
@Email: Employee's email (nvarchar)
@PhoneNumber: Employee's phone number (nvarchar)
@Address: Employee's address (nvarchar(max))
@Salary: Employee's salary (float)
@JoiningDate: Employee's joining date (datetime)
@PermanentDate: Employee's permanent date (datetime)

Outputs:
@OutStatus: Output parameter indicating the status of the operation (int)
@OutMessage: Output parameter containing a message describing the result of the operation (varchar(500))

The stored procedure follows the following logic: It first checks if the @FirstName or @Email parameters are empty. If any of them is empty, it sets the @OutStatus to 0 and provides a corresponding error message.

If the @Email is not empty, it checks if there is no existing record in the Employees table with the same email address (case-insensitive comparison). If there is no existing record, it proceeds with the insertion. Inside a transaction, it performs the insertion of the employee record into the Employees table using the provided parameters.

If the insertion is successful, it sets @OutStatus to 1 and provides a success message. If any error occurs during the insertion, it captures the error details, sets @OutStatus to the error number, and provides the error message.

If an employee with the same email already exists in the table, it sets @OutStatus to 0 and provides a corresponding error message. Finally, it prints the values of @OutStatus and @OutMessage .

If you want to update anything in this stored procedure, you can use the ALTER statement instead of creating a new one. Instead of using Create, you have to use the word Alter. This allows you to modify the logic of the existing stored procedure as follows:

                     ALTER PROCEDURE [dbo].[usp_Employees_Insert]
                    

Update Stored Procedure:

It creates a stored procedure named [dbo].[usp_Employees_Update] in the CrudDb database. Let's go through the description and functionality of this stored procedure:

The purpose of this stored procedure is to update various information fields of an employee in the Employees table. The procedure takes several input parameters such as @Id (Employee Id), @FirstName (Employee First Name), @LastName (Employee Last Name), @Email (Employee Email), @PhoneNumber (Employee Phone Number), @Address (Employee Address), @Salary (Employee Salary), @JoiningDate (Employee Joining Date), and @PermanentDate (Employee Permanent Date).

The procedure first checks if the@FirstName or @Email values are empty or contain only double quotes. If either of these conditions is true, it sets the @OutStatus variable to 0 and assigns an appropriate error message to @OutMessage.

If the @Id value exists in the Employees table, it attempts to update the corresponding employee's information using the provided parameter values. The update statement sets the various fields to their respective parameter values. Upon successful update, it sets the @OutStatus variable to 1 and assigns a success message to @OutMessage. If an error occurs during the update, it captures the error information, rolls back the transaction, and assigns the error details to @OutStatus and @OutMessage.

If the @Id value does not exist in the Employees table, it sets @OutStatus to 0 and provides a message indicating that the employee id does not exist.

Finally, the procedure prints the values of @OutStatus and @OutMessage which can be used to determine the outcome of the procedure execution.

Delete Stored Procedure:

The provided SQL script creates a stored procedure named [dbo].[usp_Employees_DeleteById] in the CrudDb database. This stored procedure allows for the deletion of an employee record based on the provided employee ID . It includes error handling, transaction management, and output parameters to indicate the status of the operation and provide descriptive messages.

Get All Stored Procedure:

The stored procedure [dbo].[usp_Employees_GetAll] is designed to fetch all the employee details from the Employees table, including their ID, first name, last name, email, phone number, address, salary, joining date, and permanent date.

Get By Id Stored Procedure:

The stored procedure [dbo].[usp_Employees_GetById] retrieves employee details based on the provided Id parameter. The stored procedure selects specific columns from the Employees table and filters the results to match the given employee Id . The SET NOCOUNT ON statement improves performance by suppressing the row count messages.

Note: If you don't want to pass the output status or message, you can do it that way as well.

---