Stored procedures and parameters

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Call a parameterized procedure created only inside a rolled-back teaching transaction. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

BEGIN TRANSACTION;
EXEC(N'CREATE PROCEDURE dbo.DDI_LessonProductByID @ID int AS
    SELECT ProductID, ProductName FROM dbo.Products WHERE ProductID = @ID');
EXEC dbo.DDI_LessonProductByID @ID = 1;
ROLLBACK TRANSACTION;

Read it step by step

1. The procedure declares a typed @ID parameter.
2. EXEC creates it in a separate dynamic batch because CREATE PROCEDURE must start its batch.
3. The next EXEC passes 1; ROLLBACK removes the disposable dbo procedure created in this transaction.

Check the result

A stored procedure saves executable statements, not the product row itself.

Practice

Create a rolled-back procedure that returns ProductID 2 from a typed parameter.

BEGIN TRANSACTION;
EXEC(N'CREATE PROCEDURE dbo.DDI_LessonProductByID2 @ID int AS
    SELECT ProductID, ProductName FROM dbo.Products WHERE ProductID = @ID');
EXEC dbo.DDI_LessonProductByID2 @ID = 2;
ROLLBACK TRANSACTION;