Overview

• A stored procedure is a group of SQL statements compiled into a single execution plan.
• SQL Server stored procedures return data in four ways:
  • Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
  • Return codes, which are always an integer value.
  • A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
  • A global cursor that can be referenced outside the stored procedure.
• Stored procedures assist in achieving consistent implementation of logic across applications.
• Stored procedures can also improve performance.
  • Many tasks are implemented as a series of SQL statements.
  • Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed.
  • If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server.
  • The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server.

Stored Procedure Template

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'MyProcedureName')
   DROP PROCEDURE [dbo].[MyProcedureName];
GO

CREATE PROCEDURE [dbo].[MyProcedureName]
	@p1 int = 0,
	@p2 int = 0
WITH 
/* Obfuscating the Procedure Definition */
ENCRYPTION,
/* It is helpful when the parameters supplied to the procedure will not be typical, 
and when a new execution plan should not be cached or stored in memory. */
RECOMPILE
AS
BEGIN
	SET NOCOUNT ON;
	SELECT @p1 + @p2;
END

Using OUTPUT parameters

• Use OUTPUT (or OUT) parameters to return values to the caller of the procedure.
• An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure.
• A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO

CREATE PROCEDURE Production.uspGetList 
	@Product varchar(40),
	@MaxPrice money,
	@ComparePrice money OUTPUT,
	@ListPrice money OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
	FROM Production.Product AS p
	INNER JOIN Production.ProductSubcategory AS s 
		ON p.ProductSubcategoryID = s.ProductSubcategoryID
  WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;

	SET @ListPrice = (SELECT MAX(p.ListPrice)
	  FROM Production.Product AS p
    INNER JOIN  Production.ProductSubcategory AS s 
	    ON p.ProductSubcategoryID = s.ProductSubcategoryID
		WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);

	SET @ComparePrice = @MaxPrice;
END

Execution

DECLARE @ComparePrice MONEY, @Cost MONEY;
EXECUTE Production.uspGetList '%Bikes%', 700, @ComparePrice OUTPUT, @Cost OUTPUT

Insert Stored Procedure Results Into a Temporary Table

-- Before we do this task, we have to do to enable ad-hoc distributed queries.
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

-- Insert into Temp Table
SELECT * 
INTO #TempTable
FROM OPENROWSET('SQLNCLI', 'Server=(local)\\SS14;Trusted_Connection=yes;','EXEC [DatabaseName].[dbo].[Sp_Name]')

Best Practices

• Use the SET NOCOUNT ON statement as the first statement in the body of the procedure.
  • This turns off messages that SQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed.
• Use schema names when creating or referencing database objects in the procedure.
  • It will take less processing time to resolve object names.
  • It will also prevent permission problems caused by a user’s default schema being assigned when objects are created without specifying the schema.
• Avoid using functions against columns in the WHERE and JOIN clauses.
  • Doing so makes the columns non-deterministic and prevents the query processor from using indexes.