Parameter List in Stored Procedure

A stored procedure can have zero, one or more than one parameters. If a stored procedure has more than one parameter, each one must be separated by a comma.

A parameter can be described by three parts : its name, its data type and its type.

The name of parameter in Microsoft SQL Server must has @ sign as the prefix otherwise the database server will notify the error and of course the stored procedure cannot be saved in the database catalog.

Following the name is the parameter data type. The data type of the stored procedure can be any valid data type which are predefined in the database server. If you specify the size of the data type you can do it as follows:

@parameter VARCHAR(255)

The third part of a parameter is its types. This part is optional, by default it is the IN. There are three parameter types : IN, OUT and INOUT. As you guess IN is abbreviation of input, OUT is abbreviation of output and INOUT is combined of both. With input parameter you can pass the value in the stored procedure, in the last example we pass the employee identity in and delete the employee based on it. Output parameter allows you get the value back from the stored procedure, for example you may write a stored procedure to insert an employee and get its id back to the application to use in another part of the program. The INOUT parameter type as its name suggested can act as both types of parameter.

In some SQL database sever product, it does not require parameter has @ sign as prefix so you should be careful that the name of the parameter must not be equal to the name of column otherwise you may face up to the disaster without any warning or error message from the database server.

In Microsoft SQL Server 2005, it allows you to specify the default value of the parameter. It is a big plus. Because you can call a stored procedure without passing parameters, it will use default values. Here is syntax of default value of parameter.

@parameter DataType(Size) = Default_Value

In our example we can modify to use default value for stored procedure as follows:

CREATE PROCEDURE Delete_Employee
   (@EmployeeId INT = 0)
AS

BEGIN
   DELETE FROM  Employees
   WHERE  EmployeeId = @EmployeeId;
END

When you call it without passing parameter value, it will delete the employee with the identity is zero.

SQL stored procedure parameter list is simple and easy to grasp? let's move to the body part of the stored procedure.