Stored Procedures for an SQL Server Express

Stored procedures are SQL queries that SQL Server users can save and then recall for later execution. Key features of stored procedures include their programmability and their ability to accept parameters. Stored procedures are written in the programming language of Transact SQL, which is used to perform database queries. Stored procedures take the form of other SQL statements written in transact SQL, while also accepting parameters.

In Microsoft SQL Server, a new stored procedure can be created by right-clicking on a folder of existing stored procedures, called \"Stored Procedures,\" in the Object Explorer pane. SQL Server creates this folder automatically when a new database is created, and places it here in the folder hierarchy: [dbaseName]>[\"Programmability\"]>[\"Stored Procedures\"].

New stored procedures for dbaseName are stored outside of the subfolder \"System Stored Procedures,\" which is under [\"Stored Procedures\"]. On creating a new database, SQL Server fills the System Stored Procedures folder with scripts to maintain the database. SQL Server users don't need to use these System Stored Procedures to create their own.

Once the stored procedure is initially created, a new query tab opens. The tab contains a template or sample query, which the user then modifies to suit her purposes.

An example of a stored procedure is the following:

USE [test] GO CREATE PROCEDURE [dbo].[mysp_selectstuff] AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- SET NOCOUNT ON GO

-- Insert statements for procedure here SELECT * from dbo.Movies GO GO EXEC [dbo].[mysp_selectstuff] GO GO DROP PROCEDURE [dbo].[mysp_selectstuff] GO GO

It's common practice to capitalize the SQL statements, to differentiate them from the database components the statements are operating on. Comments begin with the \"--\" string. These have no effect on the query. In this example, the database being queried is called \"test,\" and the table inside that database is Movies. The prefix \"dbo\" is added to Movies to separate it from other tables that might have the same name. The full query simply selects all fields for all records in the Movies table.

The GO statements are like the RUN statements in BASIC programs: they tell the SQL interpreter to perform the statements that come before them. The EXEC statement performs the query, and DROP PROCEDURE releases the query after it's run, which is necessary before running the query again. The entered query is actually executed by SQL Server when F5 or the Execute toolbar button is pressed.