How to Use SQL Express Stored Procedure Variable

Microsoft SQL Server Express is the free version of SQL Server, an application used to create and manage databases. One central feature of SQL Server is the stored procedure. Stored procedures are SQL statements saved in a script to be run multiple times. Like functions in other programming languages just as BASIC or Javascript, stored procedures can accept parameters or input variables.

Things You'll Need

• Microsoft SQL Server Management Studio

Instructions

1. To create a test database, open the SQL Server Management Studio software. Press the "Connect" button on the Connect to Server window, then right click the "Databases" icon in the Object Explorer pane. Select "New Database," type "MyDb" for the database name. Click "OK." Select Tools > Options. Expand the Designers node to reveal Table and Database Designers. Uncheck the option called "Prevent Saving Changes that Require Table Re-Creation."

2. Create a table for MyDb by expanding the new MyDb subfolder in the Object Explorer pane. Right click "Tables" and select "New Table." In the new tab that appears, enter the column descriptions for the new table. Under the headings that read "Column Name," "Data Type" and "Allow Nulls," enter this data:

   title, nvarchar(50), Allow Nulls <unchecked>
   director, nvarchar(50), Allow Nulls <unchecked>
   year, int, Allow Nulls <unchecked>

   Press "Ctrl" and "S" to save the table and request SQL Server to actually create the table. Enter "Movies" for the table name when prompted.

3. Enter sample data in the new table. Right click the new "Dbo.movies" subfolder in the Object Explorer pane and select "Edit Top 200 Rows." Enter the following data in the new tab that appears with column headers you previously entered ("title," "director," "year"):

   star wars, Lucas, 1977
   excalibur, Boorman, 1980
   groundhog day, Ramis, 1993

4. Create the stored procedure to query the new data: In the Object Explorer pane, expand the subfolder called "Programmability" under the MyDb folder by left clicking on it. On the Stored Procedures subfolder that appears, right-click and select "New Stored Procedure."

   In the new tab that appears, select and delete all the existing SQL query text and replace it with this query, which uses a variable called @name:

   USE [MyDb]
   GO
   create PROCEDURE [dbo].[mysp_selectstuff]
   @name varchar(50)
   AS
   SELECT * from dbo.Movies where @name=title;
   go

   exec [dbo].[mysp_selectstuff] @name="star wars";
   go
   drop procedure [dbo].[mysp_selectstuff];
   go

5. Execute your new stored procedure by pressing "F5." Notice the results of the query in the window under the stored procedure. Save the procedure by pressing "Ctrl" and "S" and entering a suitable filename.

References

• Creating Stored Procedures