Share via

  • Article

How to: Create a Stored Procedure (SQL Server Management Studio)

This topic describes how to create a Transact-SQL stored procedure by using Object Explorer in SQL Server Management Studio and provides an example that creates a simple stored procedure in the AdventureWorks2008R2 database.

To create a stored procedure

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.

  3. Right-click Stored Procedures, and then click New Stored Procedure.

  4. On the Query menu, click Specify Values for Template Parameters.

  5. In the Specify Values for Template Parameters dialog box, the Value column contains suggested values for the parameters. Accept the values or replace them with new values, and then click OK.

  6. In the query editor, replace the SELECT statement with the statements for your procedure.

  7. To test the syntax, on the Query menu, click Parse.

  8. To create the stored procedure, on the Query menu, click Execute.

  9. To save the script, on the File menu, click Save. Accept the file name or replace it with a new name, and then click Save.

Security noteSecurity Note

Validate all user input. Do not concatenate user input before you validate it. Never execute a command constructed from unvalidated user input. For more information, see SQL Injection.

To create a stored procedure example

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. Expand Databases, expand the AdventureWorks2008R2 database, and then expand Programmability.

  3. Right-click Stored Procedures, and then click New Stored Procedure.

  4. On the Query menu, click Specify Values for Template Parameters.

  5. In the Specify Values for Template Parameters dialog box, enter the following values for the parameters shown.

    Parameter

    Value

    Author

    Your name

    Create Date

    Today's date

    Description

    Returns employee data.

    Procedure_name

    HumanResources.uspGetEmployees

    @Param1

    @LastName

    @Datatype_For_Param1

    nvarchar(50)

    Default_Value_For_Param1

    NULL

    @Param2

    @FirstName

    @Datatype_For_Param2

    nvarchar(50)

    Default_Value_For_Param2

    NULL

  6. Click OK.

  7. In the query editor, replace the SELECT statement with the following statement:

        SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;

  8. To test the syntax, on the Query menu, click Parse. If an error message is returned, compare the statements with the information above and correct as needed.

  9. To create the stored procedure, on the Query menu, click Execute.

  10. To save the script, on the File menu, click Save. Enter a new file name, and then click Save.

  11. To run the stored procedure, on the toolbar, click New Query.

  12. In the query window, enter the following statements:

    USE AdventureWorks2008R2;
GO
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Diane', @LastName = N'Margheim';
GO

  13. On the Query menu, click Execute.