|
Execute the following
Microsoft 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 [AdventureWorks2008]
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
*/
Related link:
SQL Server Stored Procedures for Beginners
|
