Lesson 12	Testing 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:

Supply a wide range of values for all parameters to test the result.
Use the PRINT Transact-SQL command to send messages back to the calling application at specific points in the stored procedure.
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
go
IF(OBJECT_ID('ListAZAddresses')) IS NOT NULL
DROP PROC ListAZAddresses
GO
CREATE PROC ListAZAddresses
AS
SELECT --TOP(10)
AddressLine1, City, sp.StateProvinceCode, 'Sunshine', PostalCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
WHERE
sp.Name = 'Arizona';

To insert the results set of the stored procedure execution into the Address table use the following:

INSERT INTO Address
Exec ListAZAddresses;

To verify the insert, the following SELECT statement reads the data from the Address table:

USE AdventureWorks
GO
SELECT AddressID, City, State,
Address1, County, PostalCode
FROM dbo.Address
WHERE
State = 'AZ';

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