Stored Procedure Parameters in SQL Server: Input, Output, Default, and Table-Valued

Four parameter patterns cover almost everything you’ll need to build — from a simple optional filter to passing an entire list of values into a procedure without ever concatenating a string.

Default Parameters (Optional Input)

CREATE PROCEDURE dbo.usp_CountCustomersByTier
    @tier NVARCHAR(20) = 'standard'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) AS customer_count FROM dbo.Customer WHERE tier = @tier;
END;
GO
EXEC dbo.usp_CountCustomersByTier; -- uses default
EXEC dbo.usp_CountCustomersByTier @tier = 'premium'; -- overrides it

OUTPUT Parameters (Returning Values to the Caller)

CREATE PROCEDURE dbo.usp_GetCustomerCount
    @tier NVARCHAR(20), @total INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @total = COUNT(*) FROM dbo.Customer WHERE tier = @tier;
END;
GO

DECLARE @count INT;
EXEC dbo.usp_GetCustomerCount @tier = 'standard', @total = @count OUTPUT;
PRINT 'Standard customers: ' + CAST(@count AS VARCHAR(10));

Table-Valued Parameters: The Modern Way to Pass a List

CREATE TYPE dbo.CustomerNameList AS TABLE (name NVARCHAR(100));
GO

CREATE PROCEDURE dbo.usp_GetCustomersByNames
    @Names dbo.CustomerNameList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT c.customer_id, c.name, c.email
    FROM dbo.Customer c
    INNER JOIN @Names n ON n.name = c.name;
END;
GO

DECLARE @list dbo.CustomerNameList;
INSERT INTO @list VALUES ('Dana Park'), ('Elena Petrova');
EXEC dbo.usp_GetCustomersByNames @Names = @list;

TVPs are the correct, set-based way to pass a list into a procedure — far better than the old pattern of passing a comma-separated string and splitting it inside the procedure, which is exactly the kind of row-by-row string manipulation Chapter 1’s WHILE-loop lesson warned against. They’re always READONLY: you can read from them, never modify the caller’s table — attempting an UPDATE/DELETE/INSERT against @Names inside the procedure body is a compile error, by design.

The Old Way, for Comparison

-- The pre-TVP pattern (2005 and earlier, still seen in legacy code):
CREATE PROCEDURE dbo.usp_GetCustomersByNames_Legacy @NameCsv NVARCHAR(MAX) AS
BEGIN
    SELECT c.* FROM dbo.Customer c
    INNER JOIN STRING_SPLIT(@NameCsv, ',') s ON s.value = c.name; -- fragile: commas in names break it
END;

Beyond fragility with embedded delimiters, the string-splitting approach also loses type safety entirely (everything is text until parsed) and can’t easily pass more than one column of data per “row.” A TVP’s table type can have as many columns as you need, each with its own real data type.