Inside Microsoft® SQL Server™ 2005

The Stored Procedure Interface

This section covers the interface (that is, the input and output parameters) of stored procedures.

Input Parameters

You can define input parameters for a stored procedure in its header. An input parameter must be provided with a value when the stored procedure is invoked unless you assign the parameter with a default value. As an example, the following code creates the usp_GetCustOrders procedure, which accepts a customer ID and datetime range boundaries as inputs, and returns the given customer’s orders in the given datetime range:

USE Northwind; GO IF OBJECT_ID('dbo.usp_GetCustOrders') IS NOT NULL DROP PROC dbo.usp_GetCustOrders; GO CREATE PROC dbo.usp_GetCustOrders @custid AS NCHAR(5), @fromdate AS DATETIME = '19000101', ...