Procedures no Sql Server 2000 / 2005 - parte 2
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
------------------------------------------------------------------------------------------------------------------------
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)
