Tuesday, January 27, 2015

Các tham số của Stored Procedure .

-- Stored procedure definition with input/output parameters - QUICK SYNTAX
USE AdventureWorks2008;
GO
CREATE PROCEDURE sprocSalesByQuarter
                @StartYear  INT,
                @TotalSales MONEY  OUTPUT
AS
  BEGIN -- sproc definition 
    SET NOCOUNT  ON -- turn off rows affected messages
    
    SELECT @TotalSales = SUM(SubTotal)
    FROM   Sales.SalesOrderHeader
    WHERE  OrderDate >= DATEADD(YY,@StartYear-1900,'19000101')
    
    SELECT   YEAR = YEAR(OrderDate),
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                      WHEN DATEPART(QQ,OrderDate) = 1 THEN SubTotal
                                          END),1),'') AS 'Q1',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                      WHEN DATEPART(QQ,OrderDate) = 2 THEN SubTotal
                                          END),1),'') AS 'Q2',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                      WHEN DATEPART(QQ,OrderDate) = 3 THEN SubTotal
                                          END),1),'') AS 'Q3',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                      WHEN DATEPART(QQ,OrderDate) = 4 THEN SubTotal
                                          END),1),'') AS 'Q4'
    FROM     Sales.SalesOrderHeader soh
    WHERE    OrderDate >= DATEADD(YY,@StartYear-1900,'19000101')
    GROUP BY YEAR(OrderDate)
    ORDER BY YEAR(OrderDate);
    RETURN @@ROWCOUNT;
  END -- sproc definition
GO
-- Execute stored procedure with INPUT/OUTPUT parameters
DECLARE @TotSales money
EXEC sprocSalesByQuarter 2003, @TotSales OUTPUT
/* YEAR     Q1          Q2                Q3                Q4
2003  6,679,873.80      8,357,874.88      13,681,907.05     13,291,381.43
2004  11,398,376.28     14,379,545.19     50,840.63   */
SELECT 'Total Sales since 2003'=@TotSales
/* Total Sales since 2003
      67839799.2669  */
------------
-- T-SQL Stored Procedure with single input parameter - Basic Syntax
USE AdventureWorks2008;
GO
CREATE PROCEDURE uspProductByColor @pColor VARCHAR(32)
AS
  BEGIN
    SET nocount  ON
    IF @pColor IS NULL
      SELECT   Productid,
               ProductName = Name,
               Productnumber,
               ListPrice,
               Color
      FROM     Production.Product
      ORDER BY Color, Name
    ELSE
      SELECT   Productid,
               ProductName = Name,
               ProductNumber,
               ListPrice,
               Color
      FROM     Production.Product
      WHERE    Color = @pColor
      ORDER BY Color, Name
  END
GO

-- T-SQL execute stored procedure
EXEC uspProductByColor  'Yellow'
/* ProductID      Name                   ProductNumber 
797               Road-550-W Yellow, 38  BK-R64Y-38 
798               Road-550-W Yellow, 40  BK-R64Y-40 
799               Road-550-W Yellow, 42  BK-R64Y-42 
.... 
*/
------------ 
-- SQL stored procedure parameters
-- SQL stored procedure parameters default values - SQL Server stored procedure parameters
USE AdventureWorks;
GO

CREATE PROCEDURE uspVendorsByLocation
                @City    VARCHAR(30)  = NULL,
                @State   VARCHAR(30)  = NULL,
                @Country VARCHAR(50)  = NULL
AS
  BEGIN
    SELECT   V.VendorID,
             V.Name  AS Vendor,
             A.City,
             SP.Name AS State,
             CR.Name AS Country
    FROM     Purchasing.Vendor AS V
             JOIN Purchasing.VendorAddress AS VA
               ON VA.VendorID = V.VendorID
             JOIN Person.Address AS A
               ON A.AddressID = VA.AddressID
             JOIN Person.StateProvince AS SP
               ON SP.StateProvinceID = A.StateProvinceID
             JOIN Person.CountryRegion AS CR
               ON CR.CountryRegionCode = SP.CountryRegionCode
    WHERE    (A.City = @City
               OR @City IS NULL)
             AND (SP.Name = @State
                   OR @State IS NULL)
             AND (CR.Name = @Country
                   OR @Country IS NULL)
    ORDER BY Country,
             State,
             City,
             Vendor
  END

GO

-- Execute stored procedure by supplying all parameters
EXEC uspVendorsByLocation 'Los Angeles','California','United States'
-- Select from stored procedure
-- SQL Server openquery
SELECT * FROM OPENQUERY (DELLSTAR,
'EXEC AdventureWorks.dbo.uspVendorsByLocation ''Los Angeles'',
''California'',''United States''')
/*
VendorID    Vendor                  City        State       Country
27          Capital Road Cycles     Los Angeles California  United States
4           Comfort Road Bicycles   Los Angeles California  United States*/
*/

-- Execute stored procedure
-- Full list - no filtering
EXEC uspVendorsByLocation
SELECT * FROM OPENQUERY (DELLSTAR,
'EXEC AdventureWorks.dbo.uspVendorsByLocation')

-- (104 row(s) affected)
/* Partial results
VendorID    Vendor                        City        State       Country
88          Greenwood Athletic Company    Lemon Grove Arizona     United States
100         Holiday Skate & Cycle         Lemon Grove Arizona     United States
97          Northwind Traders             Phoenix     Arizona     United States
38          Allenson Cycles               Altadena    California  United States
48          Gardner Touring Cycles        Altadena    California  United States
*/

-- Execute stored procedure by supplying the City parameter
EXEC uspVendorsByLocation 'Berkeley'
EXEC uspVendorsByLocation Berkeley
SELECT * FROM OPENQUERY (DELLSTAR,
'EXEC AdventureWorks.dbo.uspVendorsByLocation ''Berkeley''')

/*
VendorID    Vendor                  City        State       Country
76          Cruger Bike Company     Berkeley    California  United States
98          Trikes, Inc.            Berkeley    California  United States
*/

-- Execute stored procedure by supplying the State parameter
EXEC uspVendorsByLocation NULL,'California'
SELECT * FROM OPENQUERY (DELLSTAR,
'EXEC AdventureWorks.dbo.uspVendorsByLocation NULL,''California''')

-- (39 row(s) affected)

-- Execute stored procedure by supplying the Country parameter
EXEC uspVendorsByLocation NULL,NULL,'United States'
SELECT * FROM OPENQUERY (DELLSTAR,
'EXEC AdventureWorks.dbo.uspVendorsByLocation NULL,NULL,''United States''')

-- (104 row(s) affected) -- entire set i.e. all vendors are U.S. based
------------ 

Source : sqlusa

No comments:

Post a Comment