Jump Start: Stored Procedure Parameters

In "Inside Stored Procedure Templates" (www.sqlmag.com/Article/ArticleID/97201 ), I explained how to customize the generic stored procedure template that's generated by SQL Server Management Studio Express (SSMSE). My sample procedure, which I named MyNwdSP, uses one parameter and one variable and contains the following T-SQL source code:

CREATE PROCEDURE MyNwdSP
	@P1city nvarchar(15)
AS
	SELECT * FROM Customers WHERE city = @P1city
GO

MyNwdSP's parameter is @P1city. The first thing to note is that parameters are used as variables within stored procedures, and all T-SQL variables begin with the @ symbol. The name can be anything you designate. I used @P1city to make it clear that this is the first parameter in the stored procedure and that it's used for the city column, but I could have given it a different name, such as @parm1, @InputCity, or @SomethingElse.

Let's modify this stored procedure to accept two variables and to provide default values for those variables. The modified stored procedure is

CREATE PROCEDURE MyNwdSP
	@P1city nvarchar(15) = 'Seattle',
	@P2country nvarchar(15) = 'USA'
AS
	SELECT * FROM Customers WHERE city = @P1city
GO

Multiple variables must be separated by commas, and each parameter variable must have a unique name. You specify default values for a parameter by adding an equals sign (=) and the default value after the parameter. Now that you've defined default values, you can call this stored procedure either with or without parameter values. When the call provides no values for the parameters, the result set will return rows where the country is USA and the city is Seattle.