Stored Procedures For Beginners

Input Variables

There are many reasons for wanting to pass data to a stored procedure, especially if your stored procedure is being called by a dynamic web page or other application. You may want to use a SELECT statement to pull information into the application for dynamic display. In this case, you would pass selection criteria to the stored procedure (for use in a WHERE clause). If you are inserting new records, you will need to get the data from somewhere. Updating existing records also involves simply getting the data. In both INSERT and UPDATE statements, it is necessary to pass data to the stored procedure. For INSERT, UPDATE, and SELECT statements (to name a few), you can pass the data to your stored procedure using variables.

Input variables are essentially "storage" for data that you want to pass to your stored procedure. Inside your stored procedure, you will declare variables at the top of the stored procedure. How does the data get there? The data is entered in the exec statement that you use to kick off the stored procedure. We’ll discuss that in more detail in a bit.

There are two types of variables that you can create in SQL Server stored procedures:  Global and Local. Since this is for beginners, I don’t want to go crazy with too many options. We’ll stick to local variables for now. You can name a variable most anything you want, though it is best to stick with meaningful works and abbreviations. I also tend to avoid punctuation, though underscores ("_") are sometimes helpful. The only real requirement is that you begin your variable with the "@" symbol. Here are some examples:

  • @f_name

  • @fullname

  • @HomePhone

  • @ext

For every data element you want to pass, you will need to declare a variable. Declaring a variable is quite easy. You decide on a name and a datatype (integer, text, etc.), and indicate the name and datatype at the top of the procedure (below the "CREATE PROCEDURE" line). Let’s add a record to USERLIST. Remember the following:

  • "usr_id" is the primary key, and is system-generated. We won’t need to pass a value for it. 

  • "login", "pswd", "l_name", and "email" are required fields. We will have to pass values for them.

First, let’s create the header information (like the author, change log, etc.) that should be a part of every stored procedure.

Name:  usp_adduser
Description:  Adds a user
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill

Remember this?


We will put the variables in here, later

Add the "CREATE PROCEDURE" line, assigning the name "usp_adduser". Our next step is to remove the comments and declare our variables!

To start, let’s look at how our variables will fit. We will need to create a variable for every value we may need to pass. We may not pass a value to every field every time we run the stored procedure. But, we do need to address the possibility that over the life of the stored procedure, every data element may be used. The best way to address this issue is to create a variable for every column in USERLIST. To keep this example simple, we are also assuming that each of the columns can be NULL, and we will also be passing all of the variables to the stored procedure. If some of the columns cannot be NULL, or if not all of the columns will be affected, then the stored procedure and/or the exec statement have to be rewritten slightly. The list below shows the variable and the field with which it is associated.

Kaynak: Tom Oneill
Hit: 2340
Yazar: renegadealien


Siftahı yapan siz olun
Yorum yapabilmek için üye girişi yapmalısınız.