Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the design of stored procedure parameters.
-- 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
------------
No comments:
Post a Comment