Execute the following SQL Server T-SQL example scripts in Management Studio Query Editor to create and test a SQL Server stored procedures:

-- SQL Server create stored procedure
USE Northwind;
GO
CREATE PROCEDURE CustomerListByState   @State VARCHAR(128)
AS
  BEGIN
    SET NOCOUNT  ON
    
    SELECT   Region,
             CustomerID,
             CompanyName,
             ContactName,
             Phone
    FROM     Customers
    WHERE    Region = @State
    ORDER BY Region,
             CompanyName
  END
 
GO
 
-- Execute stored procedure script
DECLARE  @State VARCHAR(100)
SET @State = 'WA'
EXEC CustomerListByState @State
GO
/*
Region CustomerID CompanyName             ContactName       Phone
WA    LAZYK      Lazy K Kountry Store    John Steel        (509) 555-7969
WA    TRAIH      Trail's Head Gourmet    Helvetius Nagy    (206) 555-8257
WA    WHITC      White Clover Markets    Karl Jablonski    (206) 555-4112

*/

----------

-- SQL Server recursive stored procedure - SQL Server tree processing

USE [AdventureWorks]

GO

DROP PROCEDURE [dbo].sprocGetWhereProductUsed

GO

-- ALTER PROCEDURE [dbo].sprocGetWhereProductUsed -- instead of DROP/CREATE

CREATE PROCEDURE [dbo].sprocGetWhereProductUsed

                @StartProductID [INT],   -- SQL stored procedure parameter

                @CheckDate      [DATETIME]   -- SQL stored procedure parameters

AS

/*

This stored procedure is using a recursive query to return all components,

assemblies and finished products that directly or indirectly use the specified

ProductID (@ProductID) at a given date (@CheckDate).

Output parameter: none

Output column list: ComponentName,  ComponentID,     ProductAssembly,  

                    AssemblyID,     TotalQuantity,   StandardCost,

                    ListPrice,      BOMLevel,        RecursionLevel,

Author: Database Developer

Create Date: 2015.06.01

Last Update: 2016.10.23

Last Update by: DBA

*/

  BEGIN

    SET NOCOUNT  ON;

      /* Use recursive query to generate a multi-level Bill of Material

      (i.e. all level 1 components of a level 0 assembly,

      all level 2 components of a level 1 assembly) */

    -- SQL Server recursive cte - recursive query

    -- CTE name and columns

    WITH cteBOM([ProductAssemblyID],[ProductAssembly],[ComponentID],

                  [PerAssemblyQty],[StandardCost],[ListPrice],

[BOMLevel],[RecursionLevel])

         AS (SELECT b.[ProductAssemblyID],

                    p.[Name],

                    b.[ComponentID],

                    b.[PerAssemblyQty],

                    p.[StandardCost],

                    p.[ListPrice],

                    b.[BOMLevel],

                    0 -- Get the initial list (root) for the component assembly

             FROM   [Production].[BillOfMaterials] b

                    INNER JOIN [Production].[Product] p

                      ON b.[ProductAssemblyID] = p.[ProductID]

             WHERE  b.[ComponentID] = @StartProductID

                    AND @CheckDate >= b.[StartDate]

                    AND @CheckDate <= Isnull(b.[EndDate],@CheckDate)

             UNION ALL -- Processing trees in sql server

             SELECT b.[ProductAssemblyID],

                    p.[Name],

                    b.[ComponentID],

                    b.[PerAssemblyQty],

                    p.[StandardCost],

                    p.[ListPrice],

                    b.[BOMLevel],

                    [RecursionLevel] + 1 -- Join recursive member to anchor (root)

             FROM   cteBOM cte

                    INNER JOIN [Production].[BillOfMaterials] b

                      ON cte.[ProductAssemblyID] = b.[ComponentID]

                    INNER JOIN [Production].[Product] p

                      ON b.[ProductAssemblyID] = p.[ProductID]

             WHERE  @CheckDate >= b.[StartDate]

                    AND @CheckDate <= Isnull(b.[EndDate],@CheckDate))

    -- Outer (main) select from the CTE

    SELECT p.Name                                    AS Component,

           b.[ComponentID],

           b.ProductAssembly,

           b.[ProductAssemblyID]                     AS AssemblyID,

           Convert(INT,Sum(b.[PerAssemblyQty]))      AS [TotalQuantity],

           '$' + Convert(VARCHAR,b.[StandardCost],1) AS StandardCost,

           '$' + Convert(VARCHAR,b.[ListPrice],1)    AS ListPrice,

           b.[BOMLevel],

           b.[RecursionLevel]

    FROM     cteBOM b

             INNER JOIN Production.Product p

               ON p.ProductID = b.ComponentID

    GROUP BY p.Name,

             b.[ComponentID],

             b.[ProductAssembly],

             b.[ProductAssemblyID],

             b.[BOMLevel],

             b.[RecursionLevel],

             b.[StandardCost],

             b.[ListPrice]

    ORDER BY b.[BOMLevel] DESC,

             b.[ComponentID],

             b.[ProductAssemblyID]

    OPTION (MAXRECURSION 0)

  END;

GO

-- Test stored procedure

-- Get ProductID numbers for test

-- Parts are priced as $0.0

SELECT TOP ( 5 ) ProductID,

                 ProductName = Name

FROM     Production.Product

WHERE    ListPrice = 0.0

ORDER BY Newid()

GO

/* 

ProductID  ProductName

332  Freewheel

383  Hex Nut 23

478  Metal Bar 2

404  External Lock Washer 4

417  Internal Lock Washer 6

*/

-- SQL execute stored procedure

EXEC [dbo].sprocGetWhereProductUsed

  332 ,

  '2002-01-15 00:00:00.000'

GO

/* Partial results

Component   ComponentID  ProductAssembly  AssemblyID  TotalQuantity  StandardCost

Freewheel         332  LL Crankset             949         1           $77.92

Freewheel         332  ML Crankset             950         1           $113.88

Freewheel         332  HL Crankset             951         1           $179.82

LL Crankset       949  Touring-3000 Blue, 54   958         1           $461.44

LL Crankset       949  Touring-3000 Blue, 58   959         1           $461.44

LL Crankset       949  Touring-3000 Blue, 62   960         1           $461.44

LL Crankset       949  Touring-3000 Yellow, 44 961         1           $461.44

*/