Reason for Using a Stored Procedure
Approach When Writing the Stored Procedure
INSERT, UPDATE, or DELETE statement
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.