CRUD DB With Stored Procedures

Create Project Directory and Files

Use the terminal to navigate to the development folder where you would like to create the project

cd Developer

Create the folder to contain the project, then navigate into it

mkdir CrudApp 
cd CrudApp

Initialise Git and create a README file

git init
touch .gitignore README.md

Create the SQL files and README

touch schema.sql stored-procedures.sql seed.sql teardown.sql README.md

Create the Tables

Open the schema.sql file and enter the following

-- Create and use the CrudApp database
IF DB_ID('CrudApp') IS NULL
    CREATE DATABASE CrudApp;
GO

USE CrudApp;
GO

-- Create Tasks table
CREATE TABLE Tasks (
                       Id INT PRIMARY KEY IDENTITY(1,1),
                       Title NVARCHAR(100) NOT NULL,
                       Description NVARCHAR(255),
                       DueDate DATE,
                       Completed BIT DEFAULT 0
);

Highlight the code and press go

If datagrip asks what schema to use, select to use the default

In the Database explorer window on the left, click the box to the right of localhost. You should now see our new CrudApp database listed, click the checkbox next to this to add it to the displayed databases.

Once you can see the database listed, select the number besides its name to show the list of available schemas, select dbo, this contains the Tasks table we created.

Add the Stored Procedures (returning data sets)

Open the stored-procedures.sql file and add the following

USE CrudApp;
GO

-- Create
CREATE PROCEDURE AddTask
    @Title NVARCHAR(100),
    @Description NVARCHAR(255),
    @DueDate DATE,
    @Completed BIT
AS
BEGIN
INSERT INTO Tasks (Title, Description, DueDate, Completed)
VALUES (@Title, @Description, @DueDate, @Completed);
END;
GO

-- Read all
CREATE PROCEDURE GetTasks
    AS
BEGIN
SELECT * FROM Tasks ORDER BY DueDate;
END;
GO

-- Read by ID
CREATE PROCEDURE GetTaskById
    @Id INT
AS
BEGIN
SELECT * FROM Tasks WHERE Id = @Id;
END;
GO

-- Update
CREATE PROCEDURE UpdateTask
    @Id INT,
    @Title NVARCHAR(100),
    @Description NVARCHAR(255),
    @DueDate DATE,
    @Completed BIT
AS
BEGIN
UPDATE Tasks
SET Title = @Title,
    Description = @Description,
    DueDate = @DueDate,
    Completed = @Completed
WHERE Id = @Id;
END;
GO

-- Delete
CREATE PROCEDURE DeleteTask
    @Id INT
AS
BEGIN
DELETE FROM Tasks WHERE Id = @Id;
END;
GO

After running the above select refresh in the Database Explorer left-hand window., a new folder called routines should appear containing your stored procedures.

Add the Stored Procedures (returning JSON)

Open the stored-procedures.sql file and add the following

CREATE PROCEDURE AddTask
    @Title NVARCHAR(100),
    @Description NVARCHAR(255),
    @DueDate DATE,
    @Completed BIT
AS
BEGIN
    DECLARE @NewId INT;

    INSERT INTO Tasks (Title, Description, DueDate, Completed)
    VALUES (@Title, @Description, @DueDate, @Completed);

    SET @NewId = SCOPE_IDENTITY();

    SELECT * FROM Tasks WHERE Id = @NewId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
END;
GO

CREATE PROCEDURE GetTasks
    AS
BEGIN
SELECT * FROM Tasks ORDER BY DueDate
    FOR JSON PATH;
END;
GO

CREATE PROCEDURE GetTaskById
    @Id INT
AS
BEGIN
SELECT * FROM Tasks WHERE Id = @Id
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
END;
GO

CREATE PROCEDURE UpdateTask
    @Id INT,
    @Title NVARCHAR(100),
    @Description NVARCHAR(255),
    @DueDate DATE,
    @Completed BIT
AS
BEGIN
UPDATE Tasks
SET Title = @Title,
    Description = @Description,
    DueDate = @DueDate,
    Completed = @Completed
WHERE Id = @Id;

SELECT * FROM Tasks WHERE Id = @Id
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
END;
GO

CREATE PROCEDURE DeleteTask
    @Id INT
AS
BEGIN
    DECLARE @Deleted TABLE (Id INT, Title NVARCHAR(100), Description NVARCHAR(255), DueDate DATE, Completed BIT);

INSERT INTO @Deleted
SELECT * FROM Tasks WHERE Id = @Id;

DELETE FROM Tasks WHERE Id = @Id;

SELECT * FROM @Deleted
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
END;
GO

Seed the data

Open the seed.sql file and enter the following

USE CrudApp;
GO

INSERT INTO Tasks (Title, Description, DueDate, Completed)
VALUES
('Buy groceries', 'Milk, Bread, Eggs', '2025-07-05', 0),
('Finish project report', 'Finalize and email by 3pm', '2025-07-04', 0),
('Call Alice', 'Discuss weekend plans', '2025-07-03', 1);

Select and run it to add some tasks to our table

Prepare the Teardown file

Open the teardown.sql file and paste the following but do not run it

-- Drop entire database
IF DB_ID('CrudApp') IS NOT NULL
BEGIN
    ALTER DATABASE CrudApp SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CrudApp;
END;

This file is only for if you need to destroy the database and start over

Create a README File

Open the README.md file and paste the following -

# Task Manager - Database

This folder contains the SQL scripts for the Task Manager app's database layer. It uses SQL Server and stored procedures for all data access.

## Files

### `schema.sql`
Creates the `Tasks` table:
- `Id`: Auto-incremented primary key
- `Title`: Short task title
- `Description`: Optional details
- `DueDate`: Due date
- `Completed`: Boolean flag

### `stored-procedures.sql`
Contains stored procedures for:
- `AddTask`: Insert new task
- `GetTasks`: Retrieve all tasks
- `GetTaskById`: Retrieve task by ID
- `UpdateTask`: Update task by ID
- `DeleteTask`: Delete task by ID

### `seed.sql`
Seeds the database with example tasks.

### `teardown.sql`
Drops all stored procedures and the `Tasks` table.

## Usage

1. Run `schema.sql` to create the table.
2. Run `stored-procedures.sql` to add stored procedures.
3. Run `seed.sql` to add test data.
4. Run `teardown.sql` to drop everything.

11 July 2025