Procedures e consultas usando o Sql Server 2000/2005
USE Northwind
DECLARE @retCode int
DECLARE @custID nchar(5), @empID int
DECLARE @orderDate datetime, @shipperID int
DECLARE @Details varchar(1000)
SET @shipperID=2
SET @custID='SAVEA'
SET @empID=4
SET @orderDate = '2/3/2002'
SET @Details="32 10 0.25 47 8 0.20"
SET @Details=@Details + " 75 5 0.05 76 15 0.10"
EXECUTE @retCode = NewOrder @custID, @empID, @orderDate,
@shipperID, @Details
PRINT @retCode
------------------------------------------------------------------------------------------------------------------------
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AddCustomer')
DROP PROCEDURE AddCustomer
GO
CREATE PROCEDURE AddCustomer
@custID nchar(5), @custName nvarchar(40),
@custContact nvarchar(30), @custTitle nvarchar(30),
@custAddress nvarchar(60), @custcity nvarchar(15),
@custRegion nvarchar(15), @custPostalCode nvarchar(10),
@custCountry nvarchar(15),
@custPhone nvarchar(24), @custFax nvarchar(24)
AS
DECLARE @ErrorCode int
INSERT Customers (CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region,
PostalCode, Country, Phone, Fax)
VALUES (@custID, @custName, @custContact,
@custTitle, @custAddress,
@custCity, @custRegion, @custPostalCode, @custCountry,
@custPhone, @custFax)
SET @ErrorCode=@@ERROR
IF (@ErrorCode = 0)
RETURN (0)
ELSE
RETURN (@ErrorCode)
------------------------------------------------------------------------------------------------------------------------
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AllInvoices')
DROP PROCEDURE AllInvoices
GO
CREATE PROCEDURE AllInvoices
AS
SELECT CompanyName, Orders.OrderID, ProductName,
UnitPrice=ROUND([Order Details].UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2)
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID And
[Order Details].OrderID = Orders.OrderID And
Orders.CustomerID=Customers.CustomerID
ORDER BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID
------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AvgBookRoyalties')
DROP PROCEDURE AvgBookRoyalties
GO
CREATE PROCEDURE AvgBookRoyalties
@avgRoy float OUTPUT
AS
SET NOCOUNT ON
EXECUTE MakeTitlesCursor
OPEN Sales
DECLARE @TitleID char(6), @Title varchar(80), @TitlePrice float, @TitleSales int
DECLARE @Low int, @Hi int, @Royalty int
DECLARE @currentTitle varchar(80)
DECLARE @previousLow int
DECLARE @BookRoyalties float
DECLARE @TotalRoyalties float, @TitleCount int
SET @previousLow = 0
SET @BookRoyalties = 0
SET @TotalRoyalties = 0
SET @TitleCount = 0
FETCH NEXT FROM Sales INTO @TitleID, @Title, @TitleSales, @TitlePrice,
@Low, @Hi, @Royalty
SET @currentTitle=@Title
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Title <> @currentTitle
BEGIN
SET @TotalRoyalties = @TotalRoyalties + @BookRoyalties
SET @TitleCount = @TitleCount + 1
SET @currentTitle = @Title
SET @previousLow = 0
SET @BookRoyalties = 0
END
IF @TitleSales < @hi
BEGIN
IF @Low <= @TitleSales
SET @BookRoyalties = @BookRoyalties + @TitlePrice *
(@TitleSales - @previousLow)* @Royalty / 100.0
END
ELSE
BEGIN
SET @BookRoyalties = @BookRoyalties + @TitlePrice *
(@Hi - @previousLow ) * @Royalty / 100.0
SET @previousLow = @hi
END
FETCH NEXT FROM Sales INTO @TitleID, @Title, @TitleSales, @TitlePrice,
@Low, @Hi, @Royalty
END
SET @TotalRoyalties = @TotalRoyalties + @BookRoyalties
SET @TitleCount = @TitleCount + 1
CLOSE Sales
DEALLOCATE Sales
SET @avgRoy = @TotalRoyalties / @TitleCount
