Procedures e consultas usando o Sql Server 2000/2005

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (0)  (0)

Veja nesta dica algumas 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

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?