8. Stored Procedure

Scenario: Get List of Books by Author

We have a database named LibraryDB with a table Books.

Table Schema:

CREATE TABLE Books (
    BookId INT PRIMARY KEY,
    Title NVARCHAR(250),
    Author NVARCHAR(250),
    PublishedYear INT
);

Sample Data:

BookId	Title	Author	PublishedYear
1	SQL Basics	John Doe	2020
2	Advanced SQL Techniques	Jane Smith	2021
3	Introduction to Databases	John Doe	2019
4	Data Science Essentials	Alice Johnson	2022

---

Stored Procedure to Get Books by Author

USE [LibraryDB]
GO
 
CREATE PROCEDURE GetBooksByAuthor
    @Author NVARCHAR(250),
    @MinYear INT = NULL -- Optional parameter for filtering by year
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT
        Title,
        Author,
        PublishedYear
    FROM
        Books
    WHERE
        Author = @Author
        AND (@MinYear IS NULL OR PublishedYear >= @MinYear)
    ORDER BY
        PublishedYear;
END
GO

---

Explanation of the Stored Procedure

1. Input Parameters:

   • @Author: The author’s name to filter the books.
   • @MinYear: Optional parameter. If provided, filters books published after this year.

2. Query Logic:

   • Select books where the Author matches the given input.
   • If @MinYear is provided, include only books published after or in the specified year.
   • Results are sorted by PublishedYear.

---

Executing the Stored Procedure

1. Call to Get Books by John Doe:

EXEC GetBooksByAuthor @Author = 'John Doe';

Result:

Title	Author	PublishedYear
Introduction to Databases	John Doe	2019
SQL Basics	John Doe	2020

---

2. Call with a Year Filter:

EXEC GetBooksByAuthor @Author = 'John Doe', @MinYear = 2020;

Result:

Title	Author	PublishedYear
SQL Basics	John Doe	2020