Stored Procedures  «Prev  Next»

Lesson 12Testing stored procedures
Objective Describe how to test your stored procedure.

Testing Stored Procedures

To test your stored procedures, you will simply execute them in a nonlive format and see what happens. Following are some guidelines for testing your stored procedures:
  1. Supply a wide range of values for all parameters to test the result.
  2. Use the PRINT Transact-SQL command to send messages back to the calling application at specific points in the stored procedure.
  3. Try omitting values to see what happens. You may need to create your stored procedures by using default values for one or more parameters.

XML Data Types

Much like the current VarChar data type that persists variable character values, a new XML data type persists XML documents and fragments. This type is available for variable declarations within stored procedures, parameter declarations, and return types and conversions.

Tables: Used to create and modify the design of tables, view and edit the contents of tables, and work with the indexes of the tables, permissions, and publications. Triggers, stored procedures that respond to data-modifi cation operations (insert, update, and delete), may be created and edited here.
Programmability: A large section that includes most of the development objects, stored procedures, functions, database triggers, assemblies, types, rules, defaults, Plan Guides and Sequences.
One thing that should be noted is the use of @, which is part of the variable declaration. Whenever, you declare a variable that will be used in an expression, stored procedure, or function you must prefi x it with an @ symbol.

Inserting Result Set from a Stored Procedure

The INSERT...EXEC form of the INSERT operation pulls data from a stored procedure and inserts it into a table. Behind these inserts are the full capabilities of T-SQL. The basic function is the same as that of the other insert forms. The columns must line up between the INSERT columns and the stored-procedure result set. Following is the basic syntax of the INSERT...EXEC command:

INSERT [INTO] schema.Table [(Columns)]
EXEC StoredProcedure Parameters;

Be careful, though, because stored procedures can easily return multiple record sets, in which case the INSERT attempts to pull data from each of the result sets, and the columns from every result set must line up with the insert columns.
The following code sample builds a stored procedure that returns 10 Arizona addresses from the Address table in the AdventureWorks database. When the stored procedure is in place, the sample code performs the INSERT...EXEC statement:
use tempdb
DROP PROC ListAZAddresses
AddressLine1, City, sp.StateProvinceCode, 'Sunshine', PostalCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
sp.Name = 'Arizona';

To insert the results set of the stored procedure execution into the Address table use the following:
Exec ListAZAddresses;
To verify the insert, the following SELECT statement reads the data from the Address table:
USE AdventureWorks
SELECT AddressID, City, State,
Address1, County, PostalCode
FROM dbo.Address
State = 'AZ';

Stored Procedure Result Set
Stored Procedure Result Set

In the next lesson, we will review the information covered in this module.