CRUD DB With Stored Procedures

cd Developer

mkdir CrudApp
cd CrudApp

git init
touch .gitignore README.md

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

-- 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
);

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

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

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);

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

# 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.