Calling stored procedure with Parameters

Calling stored procedure with Parameters


To pass parameter values to a stored procedure, you code the values in the EXEC statement after the procedure name. You can pass the parameters either by position or by name.

Passing parameters by Name:

Write the following code in Query Analyzer
DECLARE @LN VARCHAR(100)
EXEC spGetShashi @FirstName = ‘shashi', @LastName = @LN OUTPUT


Passing parameters by Position:
DECLARE @LN VARCHAR(100)
EXEC spGetShashi @LN OUTPUT, ‘shashi'

In fact you can use both notations to pass parameters to stored procedures when you are calling. To pass parameters by position, list them in the same order as they appear in the CREATE PROCEDURE statement and separate them with commas. When you use this technique, you can omit optional parameters only if they are declared after any required parameters.

To use an output parameter in the calling program, you must declare a variable to store its value. Then you use the name of the variable in the EXEC statement and you code the OUTPUT keyword after it to identify it as an output parameter.