Creating Stored Procedures

Once you've created your data connection within Visual InterDev, you're all set to create stored procedures. To create a stored procedure, follow these steps:

1. Right-click Stored Procedures in the Data View window, and choose New Stored Procedure from the context menu. Visual InterDev will create a template stored procedure, as shown in the code following step 2. (See also Figure 15-5.)
2. Enter the code for your stored procedure into the source code editor, and click the Save button on the toolbar when you are ready to save the stored procedure.

 Create Procedure StoredProcedure1 /*     (         @parameter1 datatype = default value,         @parameter2 datatype OUTPUT     ) */ As     /* set nocount on */     return 

Figure 15-5. The source code editor within Visual InterDev showing a new stored procedure.

Stored procedures can be as simple as a single SQL SELECT statement, or they can be hundreds of lines of SQL code. Here is an example stored procedure that performs a simple SQL SELECT statement:

 Create Procedure spCompany As     SELECT * FROM Company     return 

Here is another example stored procedure that performs a SQL SELECT statement. This procedure takes an input parameter for the City name and returns only those rows from the Company table that exactly match the search criteria:

 Create Procedure spCompanybyCity     (     @city varchar(50)     ) As     SELECT *      FROM Company     WHERE city = @city     return 

If you want to return some information in addition to the resultset from the stored procedure to the calling program, you can use the OUTPUT option when you declare your parameters. The output parameter is passed by reference, so you pass the stored procedure a variable name that can point to the value of the output parameter. The following example returns the number of rows contained in the resultset:

 Create Procedure spCompanybyCity2     (     @city varchar(50) = 'Dallas',     @num_rows int OUTPUT     ) As     SELECT *     FROM Company     WHERE city = @city     SELECT @num_rows = @@ROWCOUNT     return 

The @@ROWCOUNT global variable has been used to determine the number of rows in the resultset. The value of @@ROWCOUNT refers to the last statement executed, which was the SELECT statement for the Company table. You'll also note that a default value has been used for the @city parameter so that the input defaults to 'Dallas' if there is no user input specified for this parameter.

As another example, the stored procedure shown in the following code performs an INSERT statement into a table given some parameters as input.

 Create Procedure spAddCompany     (         @company_name varchar(50),         @address1 varchar(50) = Null,         @address2 varchar(50) = Null,         @city varchar(50) = Null,         @state varchar(50) = Null,         @zip varchar(10) = Null,         @phone varchar(12) = Null,         @fax varchar(12) = Null,         @error_msg varchar(255) OUTPUT     ) As     If Len(@phone) < 10         BEGIN         SELECT @error_msg = 'Please include the area code in the phone              number.'         return -100         END     Else         BEGIN         INSERT INTO Company(company_name, address1, address2,              city, state, zip, phone, fax)         VALUES (@company_name, @address1, @address2,             @city, @state, @zip, @phone, @fax)         END     return 

The calling program can use the return code to check the success of the stored procedure. In this example, if a code of -100 is returned, the calling program can display an error message to the user. Of course, another way to handle data validation is via client-side scripting so that a round-trip to the server is not required and the user receives instantaneous feedback. Inspecting return codes from a stored procedure is useful, however, to ensure that the procedure executed successfully and that no unexpected errors were obtained during processing.

Note

---

SQL Server has several reserved return status values. These range in value from -1 through -99 to indicate various reasons for failure. When returning a user-generated return value, be sure to pick a number outside of this range. Zero is also reserved by SQL Server for successful completion of the stored procedure.

In the spAddCompany stored procedure example, the Company table uses an IDENTITY property for the company_id column (the table's primary key). Columns that have the IDENTITY property set contain system-generated values that uniquely identify each row within the table. The values typically start at 1 and increment by 1 for each row that is added. To determine the latest value of an IDENTITY column after an INSERT statement, you can use the @@IDENTITY global variable. In the spAddCompany stored procedure, we would simply add the following code after the INSERT statement:

 SELECT @company_id = @@IDENTITY 

The @company_id variable can be defined as an output parameter in the stored procedure definition to return the value to the calling program. This value can then be used when performing INSERT statements on other tables which contain the company_id column as a secondary key.