Table 2: Approaches for Translating Queries into Stored Procedures

Reason for Using a Stored Procedure

Approach When Writing the Stored Procedure


Insert the INSERT, UPDATE, or DELETE statement into the stored procedure. Include a column list for an INSERT statement.

SELECT statement with an Order By clause

Insert the SELECT statement into the stored procedure.

SELECT statement with a Transform/Pivot clause

Implement the equivalent functionality in a stored procedure or the client application.

Query with parameters

Insert the parameters in the stored procedure. Translate the data type.

Query that references other queries

For queries that you converted to SQL views, don't change the references. For queries that you translated into stored procedures, change the references. Follow the path of the references and keep a stack of query names. Working from the deepest reference up, insert an equivalent SELECT statement that puts its results into a temporary table. Change the references from the original query to the temporary table, which requires you to set the Select Into/Bulk Copy option for the database. This approach might not work with SQL Server versions earlier than 6.5.