DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect a SQL Server stored procedure?

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

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE