Stored Procedures for an SQL Server Expressby Darrin Koltow
Microsoft SQL Server Express is a free version of Microsoft's SQL Server, which is a resource for administering and creating databases, and performing data analysis. Much of the functionality of the non-free version of SQL Server is found in SQL Server Express, including the visual management tool SQL Management Studio and the ability to make use of stored procedures.
Language of Stored Procedures
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.
Creating Stored Procedures
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
Structure of a Stored Procedure
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.
Running Stored Procedures
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.