Stored Procedures and Functions

Stored procedures and functions encapsulate reusable T-SQL logic inside the database. They improve performance through plan caching, enhance security by limiting direct table access, and centralise business logic.

---

Stored Procedures

A stored procedure is a saved collection of T-SQL statements:

CREATE PROCEDURE GetBooksByGenre
    @Genre NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Title, Author, Price
    FROM Books
    WHERE Genre = @Genre
    ORDER BY Title;
END;
GO

-- Execute
EXEC GetBooksByGenre @Genre = 'Technology';

Parameters

CREATE PROCEDURE AddBook
    @Title     NVARCHAR(200),
    @Author    NVARCHAR(100),
    @Genre     NVARCHAR(50) = 'General',   -- default value
    @Price     DECIMAL(8,2),
    @NewBookId INT OUTPUT                    -- output parameter
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Books (Title, Author, Genre, Price)
    VALUES (@Title, @Author, @Genre, @Price);

    SET @NewBookId = SCOPE_IDENTITY();
END;
GO

-- Call with output parameter
DECLARE @Id INT;
EXEC AddBook
    @Title = 'New Book',
    @Author = 'Jane Smith',
    @Price = 29.99,
    @NewBookId = @Id OUTPUT;

SELECT @Id AS InsertedBookId;

SET NOCOUNT ON

Always include SET NOCOUNT ON at the start of procedures:

• Suppresses the "X rows affected" messages
• Reduces network traffic
• Prevents interference with some client libraries

---

Error Handling with TRY...CATCH