Stored Procedures: Input Parameters

Stored procedures can have input parameters. These are similar to function parameters. We can pass in some data when executing a stored procedure.

Syntax

Declaring parameters for a stored procedure differs slightly from the function declaration syntax:

CREATE PROCEDURE Schema.ProcedureName
    @Parameter1 ParameterType,
    @Parameter2 ParameterType,
    ...
    @ParameterN ParameterType
AS
BEGIN
    [Procedure body]
END;

As we can see, there are no parentheses in the procedure declaration.

Usage

To call a stored procedure that requires an input parameter, we pass parameters in the same order in which they were declared:

EXEC Schema.ProcedureName 'ValueForParameter1', 'ValueForParameter2', ..., 'ValueForParameterN';

Unlike with functions, we do not use parentheses when calling stored procedures.

Example

Let’s consider the following table definition:

CREATE TABLE dbo.Students
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    FirstName NVARCHAR(100) NOT NULL,
    LastName NVARCHAR(100) NOT NULL,
    EnrollmentDate DATETIME NOT NULL,
);

We want to create a stored procedure that will insert new records in the table above. When the user calls this stored procedure, they will only provide the values for the FirstName and LastName columns. The Id will be generated by the database, and the EnrollmentDate will be the current date and time when the row is inserted.

To achieve these objectives, we can create a stored procedure called dbo.InsertStudent that will accept two parameters called @FirstName and @LastName:

CREATE PROCEDURE dbo.InsertStudent
    @FirstName NVARCHAR(100),
    @LastName NVARCHAR(100)
AS
BEGIN
    [Procedure body]
END

Because the FirstName and LastName columns do not allow NULL, we can add the corresponding constraint, NOT NULL, to our input parameters.

In the body of the stored procedure, we just place an INSERT INTO statement:

INSERT INTO dbo.Students (FirstName, LastName, EnrollmentDate)
VALUES (@FirstName, @LastName, GETDATE())

Here is the final result: