SQL Server CRUD Operation with Store Procedure with NodeJS

Back to: Mastering Node.js and SQL Server

SQL Server CRUD Operations with Node.js – Utilizing Stored Procedures

Introduction: In this comprehensive guide, we’ll delve into executing CRUD operations (Create, Read, Update, Delete) in a SQL Server database using Node.js, emphasizing the use of stored procedures. Stored procedures can improve performance and security in your applications.

Prerequisites:

Node.js installed on your machine.
SQL Server installed and properly configured.
npm packages: mssql for SQL Server connectivity.

Below are SQL queries and stored procedures for the CRUD operations (Create, Read, Update, Delete) in SQL Server. You can use these as examples and adapt them to your specific database schema and requirements.

1. SQL Queries for Table Creation (Assuming a simple ‘Products’ table):

-- Create Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    Quantity INT NOT NULL
);

2. Stored Procedure for Create (INSERT) Operation:

-- Create Stored Procedure for Insert
CREATE PROCEDURE InsertProduct
    @ProductName NVARCHAR(255),
    @Price DECIMAL(10, 2),
    @Quantity INT
AS
BEGIN
    INSERT INTO Products (ProductName, Price, Quantity)
    VALUES (@ProductName, @Price, @Quantity);
END;

3. Stored Procedure for Read (SELECT) Operation:

-- Create Stored Procedure for Select
CREATE PROCEDURE GetProducts
AS
BEGIN
    SELECT * FROM Products;
END;

4. Stored Procedure for Update (UPDATE) Operation:

-- Create Stored Procedure for Update
CREATE PROCEDURE UpdateProduct
    @ProductID INT,
    @NewProductName NVARCHAR(255),
    @NewPrice DECIMAL(10, 2),
    @NewQuantity INT
AS
BEGIN
    UPDATE Products
    SET ProductName = @NewProductName,
        Price = @NewPrice,
        Quantity = @NewQuantity
    WHERE ProductID = @ProductID;
END;

5. Stored Procedure for Delete (DELETE) Operation:

-- Create Stored Procedure for Delete
CREATE PROCEDURE DeleteProduct
    @ProductID INT
AS
BEGIN
    DELETE FROM Products
    WHERE ProductID = @ProductID;
END;

These SQL queries and stored procedures provide a basic structure for a ‘Products’ table and corresponding CRUD operations. You can use these as a starting point and customize them based on your specific database schema and application requirements.

Working with Nodejs Code

Step 1: Setting Up Your Node.js Project: Begin by creating a new Node.js project and installing the required dependencies.

mkdir node-crud-sql-server
cd node-crud-sql-server
npm init -y
npm install mssql

Step 2: Connecting to SQL Server:

Establish a connection to your SQL Server instance using the mssql package.

const sql = require('mssql');

const config = {
  user: 'your_username',
  password: 'your_password',
  server: 'your_server_name',
  database: 'your_database_name',
};

async function connectToSqlServer() {
  try {
    await sql.connect(config);
    console.log('Connected to SQL Server');
  } catch (error) {
    console.error('Error connecting to SQL Server:', error);
  }
}

connectToSqlServer();

Step 3: CRUD Operations with Stored Procedures:

3.1. Create (INSERT) Operation:

async function createRecord() {
  try {
    const request = new sql.Request();
    const params = {
      param1: 'Value1',
      param2: 'Value2',
    };

    const result = await request.execute('YourStoredProcedureName', params);
    console.log('Record inserted successfully.');
  } catch (error) {
    console.error('Error creating record:', error);
  }
}

3.2. Read (SELECT) Operation:

async function readRecords() {
  try {
    const request = new sql.Request();
    const result = await request.query('EXEC YourSelectStoredProcedure');
    console.log('Records retrieved:', result.recordset);
  } catch (error) {
    console.error('Error reading records:', error);
  }
}

3.3. Update (UPDATE) Operation:

async function updateRecord() {
  try {
    const request = new sql.Request();
    const params = {
      param1: 'NewValue',
      param2: 'SomeCondition',
    };

    const result = await request.execute('YourUpdateStoredProcedure', params);
    console.log('Record updated successfully.');
  } catch (error) {
    console.error('Error updating record:', error);
  }
}

3.4. Delete (DELETE) Operation:

async function deleteRecord() {
  try {
    const request = new sql.Request();
    const params = {
      param1: 'SomeCondition',
    };

    const result = await request.execute('YourDeleteStoredProcedure', params);
    console.log('Record deleted successfully.');
  } catch (error) {
    console.error('Error deleting record:', error);
  }
}

Conclusion: With this guide, you’ve learned how to execute CRUD operations in a SQL Server database using Node.js, with an emphasis on the use of stored procedures. Replace placeholders like ‘YourStoredProcedureName’ and ‘YourSelectStoredProcedure’ with your specific stored procedure names, and adapt the parameters accordingly. You can also create the ‘Products’ table and associated stored procedures using the provided SQL statements as a foundation for your application’s database operations.

Working with Nodejs Code

Stay Connected