Creating Stored Procedures

After this lesson you can

• Create a stored procedure with parameters, and run it with EXEC
• Give a parameter a default value, and pass parameters by name

Key takeaways

• A stored procedure is SQL saved in the database under a name, and it takes parameters, like a C# method. Create one with CREATE OR ALTER PROCEDURE.
• Each parameter starts with @ and has a type: use the same type as the column it's compared with.
• Run a procedure with EXEC, passing values by name, such as @CategoryName = 'Produce': clearer than by order.
• A parameter with a default, such as @MaxPrice money = 1000, can be left out, and the default is used.
• Parameters keep values separate from the SQL, so they're safe from SQL injection.

A view saves a query, but it can't take parameters. A stored procedure can: it's a named piece of SQL, saved in the database, that takes values each time it runs, like a method in C#.

Creating one

CREATE OR ALTER PROCEDURE GetProductsByCategory
    @CategoryName nvarchar(15)
AS
BEGIN
    SELECT p.ProductId, p.ProductName, p.UnitPrice
    FROM Products AS p
    INNER JOIN Categories AS c
        ON p.CategoryId = c.CategoryId
    WHERE c.CategoryName = @CategoryName
    ORDER BY p.ProductName;
END;

• CREATE OR ALTER PROCEDURE, then the name. (CREATE PROC is a common short form.)
• Parameters come after the name: each starts with @ and has a type, like a C# parameter. Use the same type as the column it's compared with: CategoryName is nvarchar(15).
• The body, between BEGIN and END, can hold any SQL, many statements if you need them.
• Like a view, it must be the only statement in its batch: use GO around it in a longer script.

In SSMS, the procedure appears under Programmability > Stored Procedures.

Running it: EXEC

EXEC GetProductsByCategory @CategoryName = 'Produce';

Values can also be passed in order, without names: EXEC GetProductsByCategory 'Produce';. Naming them is clearer, and safer when a procedure has several.

A procedure can return a single value too:

CREATE OR ALTER PROCEDURE CountProductsInCategory
    @CategoryName nvarchar(15)
AS
BEGIN
    SELECT COUNT(*)
    FROM Products AS p
    INNER JOIN Categories AS c
        ON p.CategoryId = c.CategoryId
    WHERE c.CategoryName = @CategoryName;
END;

EXEC CountProductsInCategory 'Seafood';

It returns one row with one value: 12.

Several parameters, and defaults

A parameter can have a default, used when the caller leaves it out:

CREATE OR ALTER PROCEDURE SearchProducts
    @Text nvarchar(40)
    , @MaxPrice money = 1000
AS
BEGIN
    SELECT ProductName, UnitPrice
    FROM Products
    WHERE ProductName LIKE '%' + @Text + '%'
        AND UnitPrice <= @MaxPrice
    ORDER BY UnitPrice DESC;
END;

EXEC SearchProducts @Text = 'Sauce';

EXEC SearchProducts @Text = 'Sauce', @MaxPrice = 30;

Why use stored procedures

• Logic lives in one place. Every app that needs "products in a category" calls the same procedure.
• Parameters keep values separate from SQL, just as SqlParameter does in C#, so they're safe from SQL injection.
• They can do more than one thing. A procedure can check, insert and update in one call.

Teams differ on how much to put in procedures. In this course, most SQL lives in your C# data access code, and procedures are one more tool you can reach for.

Check yourself

2 questions to try on the lesson page.

So what

A stored procedure is SQL with a name and parameters, saved where every app can call it. Next, you'll call one from C#.

Key terms

• Stored Procedure
• EXEC
• BEGIN...END

Cheat sheets

• Creating Stored Procedures