Transact-SQLを使用したデータベース開発に役立つ13のヒント

CREATE PROCEDURE GetOrdersByAcct (@iAcctKey integer) AS
   -- Retrieve Orders for a single customer
      -- Since we’re querying the order table three times,
      -- let’s create a table variable (@tOrderList) of all
   -- order keys for the specific customer. That way, we
   -- can JOIN off the temporary order list 
   DECLARE @tOrderList TABLE (OrderKey integer)
      INSERT INTO @tOrderList 
         SELECT OrderKey FROM OrderHdr 
            WHERE AcctKey = @iAcctKey
      SELECT OH.*, AM.AcctName 
         FROM OrderHdr OH
         JOIN AcctMast    AM ON AM.AcctKey = OH.AcctKey
         JOIN @tOrderList TMP ON TMP.OrderKey = OH.OrderKey
      SELECT OD.*, PM.ProdName
         FROM OrderDtl OD
         JOIN ProdMast PM ON PM.ProdKey = OD.ProdKey
         JOIN @tOrderList TMP ON TMP.OrderKey = OD.OrderKey
      SELECT * FROM OrderNotes ON
         JOIN @tOrderList TMP ON TMP.OrderKey = ON.OrderKey
   GO

SqlDataAdapter oDa =
   new SqlDataAdapter(cSQLString, oMyConn);
oDa.Fill(DsReturn,"MyResults");
// Table 0 will contain the order headers
// Table 1 will contain the order details
// Table 2 will contain the order notes

CREATE FUNCTION dbo.GetCustomerBalance  (@iAcctKey integer, 
                        @dCutOffDate DateTime)
   -- Calculate and return balance for a Customer, 
   -- for a given point in time
   RETURNS Decimal AS  
   BEGIN
      DECLARE @nCreditAmount Decimal
      DECLARE @nDebitAmount  Decimal
      SET @nCreditAmount = (SELECT SUM(Amount) FROM Credits 
                  WHERE AcctKey = @iAcctKey
                  AND CompDate <= @dCutOffDate)
      SET @nDebitAmount = (SELECT SUM(Amount) FROM Debits
                  WHERE AcctKey = @iAcctKey
                  AND CompDate <= @dCutOffDate)
   -- depending on how data is stored, you may want to 
      -- handle the signs differently
      RETURN (ISNULL(@nCreditAmount,0) - ISNULL(@nDebitAmount,0))
   END

DECLARE @dCutOffDate DATETIME
SET @dCutOffDate = CAST(’11/30/2004’ AS DateTime)
SELECT CustID, dbo.GetCustomerBalance(CustID, @dCutOffDate)
AS CustBalance
FROM...

CREATE FUNCTION dbo.GetDate (@nStatus integer, @dDrDate DateTime
               @dWIPDate DateTime, @dCompDate DateTime) 
   RETURNS DateTime
   AS
   BEGIN
      -- This returns one of three dates, based on a status
      DECLARE @dReturnDate DateTime
      SET @dReturnDate = (SELECT CASE
                  WHEN @nStatus = 1 THEN @dDrDate
                  WHEN @nStatus = 2 THEN @dWIPDate
                  WHEN @nStatus = 3 THEN @dCompDate
                  ELSE @dDrDate
               END)
      RETURN (ISNULL(@dReturnDate,CAST(’01-01-1901’ AS DateTime))) 
   END

AND CompDate <= @dCutOffDate

AND dbo.GetDate(Status,DrDate,WIPDate,CompDate)
   <= @dCutOffDate

CREATE FUNCTION dbo.CsvToTable ( @cList varchar(8000)) 
   -- Converts a comma-delimited list of integer keys to a TABLE
   -- Used to take a variable list of selections               
   -- (accounts, products, etc.) and put them into a TABLE that can
   -- be used in a subsequent JOIN
   RETURNS @IntKeyTable TABLE
      (IntKey INT)
   AS
   BEGIN
      DECLARE @nPosition INT
      DECLARE @cTempValue VARCHAR(8000) 
      DECLARE @nIntKey int
      SET @cList = RTRIM(@cList) + ’,’
      -- So right now we might have  ’1111,2222,’
      -- (Careful if the CSV already ended with a comma,
      -- you’ll wind up with an extra 0 in the key table)
      -- see if comma exists in list
      -- (use PATINDEX to return pattern position within a string
      WHILE PATINDEX(’%,%’ , @cList) <> 0     
      BEGIN
         -- get the position of the comma
         SELECT @nPosition =  PATINDEX(’%,%’ , @cList)    
         -- get the key, from beginning of string to the comma
         SELECT @cTempValue = LEFT(@cList, @nPosition - 1)   
         SET @nIntKey = CAST(@cTempValue AS INT)
         -- Write out to the Keys table (convert to integer)
         INSERT INTO @IntKeyTable 
            VALUES (@nIntKey)      
         -- wipe out the value we just inserted  
         SELECT @cList = STUFF(@cList, 1, @nPosition, ’’)     
      END
      RETURN
   END

CREATE PROCEDURE GetOrdersByMultipleAccts 
         @cAcctList varchar(8000) AS
   -- Retrieve Orders for a single customer
      DECLARE @tOrderList TABLE (OrderKey integer)
      INSERT INTO @tOrderList 
         SELECT OrderKey FROM OrderHdr OH
            JOIN dbo.CsvToTable(@cAcctList) IK
               ON IK.IntKey = OH.AcctKey
      SELECT OH.*, AM.AcctName 
         FROM OrderHdr OH
         JOIN AcctMast    AM ON AM.AcctKey = OH.AcctKey
         JOIN @tOrderList TMP ON TMP.OrderKey = OH.OrderKey
      SELECT OD.*, PM.ProdName
         FROM OrderDtl OD
         JOIN ProdMast PM ON PM.ProdKey = OD.ProdKey
         JOIN @tOrderList TMP ON TMP.OrderKey = OD.OrderKey
      SELECT * FROM OrderNotes ON
         JOIN @tOrderList TMP ON TMP.OrderKey = ON.OrderKey
   GO

-- Search anywhere in the column
SELECT * FROM Applicants WHERE
   Skills LIKE ’%XP%’ 
-- Search where skills begins with XP
SELECT * FROM Applicants WHERE
   Skills LIKE ’XP%’
-- Search where skills ends with XP
SELECT * FROM Applicants WHERE
   Skills LIKE ’%XP’

CREATE PROCEDURE GetAgingResults (@cAcctList varchar(8000), 
            @dAgeDate DateTime, @nGroupOption int)
   AS
   SELECT CASE WHEN @nGroupOption = 1 THEN CT.Description
            WHEN @nGroupOption = 2 THEN AM.Description
    END AS GroupDesc,
   -- Set up CASE statements for the aging brackets
       SUM(CASE WHEN CDate BETWEEN @dAgeDate-30 AND @dAgeDate       
      THEN CBalance ELSE 0 END) AS Age30 ,
       SUM(CASE WHEN CDate BETWEEN @dAgeDate-60 AND @dAgeDate-31  
      THEN CBalance ELSE 0 END) AS Age60 ,
       SUM(CASE WHEN CDate BETWEEN @dAgeDate-90 AND @dAgeDate-61  
         THEN CBalance ELSE 0 END) AS  Age90 ,
       SUM(CASE WHEN CDate BETWEEN @dAgeDate-120 AND @dAgeDate-91  
         THEN CBalance ELSE 0 END) AS  Age120 ,
       SUM(CASE WHEN CDate < @dAgeDate - 120 
         THEN CBalance ELSE 0 END) AS  AgeGT120, 
       SUM(CASE WHEN CDate > @dAgeDate
               THEN CBalance ELSE 0 END) AS NotAged,
       SUM(CBalance) AS TotBalance
   FROM OpenBalances OB
      JOIN dbo.CsvToTable(@cAcctList) IK   
        ON IK.IntKey = OB.AcctKey   
      JOIN Category CT 
         ON CT.CatCode = OB.CatCode   
   JOIN AcctMast AM
         ON AM.AcctKey = OB.AcctKey
      GROUP BY 
      CASE WHEN @nGroupOption = 1 THEN CT.Description
              WHEN @nGroupOption = 2 THEN AM.Description 
   END
      ORDER BY TotBalance DESC

SELECT J.JobID, SUM(H.Hours) AS TotHours,
   SUM(C.Costs) AS TotCosts
FROM JobMast J
   LEFT JOIN JobHours H ON H.JobID = J.JobID
   LEFT JOIN JobCosts C ON C.JobID = J.JobID
GROUP BY J.JobID

SELECT J.JobID,
   (SELECT SUM(H.Hours) FROM JobHours H
      WHERE H.JobID = H.JobID) AS TotHours,
   (SELECT SUM(C.Costs) FROM JobCosts C
      WHERE C.JobID = H.JobID) AS TotCosts
FROM JobMast J
GROUP BY J.JobID

SELECT J.JobID, H.TotHours, C.TotCost
   FROM JobMast J
   LEFT JOIN (SELECT JobID, SUM(Hours) AS TotHours
              FROM JobHours
              GROUP BY JobID) H
   ON H.JobID = J.JobID
   LEFT JOIN (SELECT JobID, SUM(Costs) AS TotCosts
              FROM JobCosts
              GROUP BY JobID) C
   ON C.JobID = J.JobID

-- JobMast  (JobID)
-- JobHours (JobID, EmpKey, Hours, DateWorked)
-- JobCosts (JobID, Costs)
-- EmpRate  (EmpKey, EffDate, WorkRate)
-- Use subquery to create derived tables, to sum/group hours 
-- and costs
-- Inside the subquery for hours, perform another subquery
-- to match up an employee’s work date with the rate for that day
-- Uses EmpRate twice (ER1 and ER2, once to apply the correct rate
-- and a second time in a subquery to get the rate based on date
SELECT J.JobID, H.TotLabor, H.TotHours, C.TotCost 
   FROM JobMast J
   LEFT JOIN 
       (SELECT JobID, SUM(JH.Hours*ER1.WorkRate) AS TotLabor,
                SUM(JH.Hours) AS TotHours
              FROM JobHours JH
         JOIN EmpRate ER1 ON ER1.EmpKey = JH.EmpKey
         WHERE ER1.EffDate = (SELECT MAX(EffDate) FROM
            EmpRate ER2 WHERE EffDate <= DateWorked
            AND ER2.EmpKey = JH.EmpKey)
         AND DateWorked >= EffDate
              GROUP BY JobID) H 
   ON H.JobID = J.JobID
   LEFT JOIN (SELECT JobID, SUM(Costs) AS TotCosts
              FROM JobCosts
              GROUP BY JobID) C 
   ON C.JobID = J.JobID

DECLARE @tAcctKeys TABLE (IntKey int)
INSERT INTO @tAcctKeys
   SELECT IntKey = IntKey FROM
      CsvToTable(@cAcctKeyList)
-- now you can use @tAcctKeys in any subsequent
-- joins in the stored procedure

-- neither line will work with table variables
INSERT @tTable EXEC <sp_mystoredproc>
SELECT * INTO @tTable FROM <mytable>

CREATE FUNCTION dbo.GetEndOfWeek
(@dDate DateTime)
-- Converts date to the Saturday date for the week
RETURNS DateTime AS 
BEGIN
   DECLARE @dRetDate DateTime
   SET @dRetDate =
      @dDate + ( 7-DATEPART(weekday,@dDate))
   RETURN @dRetDate
END
SELECT dbo.GetEndOfWeek(OrderDate) AS WeekEnding,
   SUM(Amount) AS WeekAmount
   FROM OrderHdr
   GROUP BY dbo.GetEndOfWeek(OrderDate)

-- Change first day of week from default of
-- Sunday (7) to Monday (1)
SET DATEFIRST 1

DECLARE @nRowCount integer
   DECLARE @dReportDate DATETIME
   DECLARE @nDollars decimal(13,2)
   SET @nRowCount = 156
   SET @dReportDate = GETDATE()
   SET @nDollars = 156134.11
   DECLARE @cDateString char(20)
   DECLARE @cRowCount char(20)
   DECLARE @cMessage varchar(8000)
   SET @cRowCount = LTRIM(RTRIM(CONVERT(int,@nRowCount,20)))
   SET @cDateString = LTRIM(RTRIM(CONVERT(datetime,@dReportDate,20)))
   SET @cMessage = ’Generated ’ + @cRowCount +
                ’ Employee Checks on ’  + @cDateString +
          ’ for a total amount of $’ + 
                CAST(@nDollars AS Varchar(20))
   SELECT @cMessage

CREATE TRIGGER Upd_Client ON dbo.Client
FOR UPDATE
AS
   UPDATE Client
      SET LastUpdate = GETDATE()
   FROM Client C
      JOIN Inserted I
       ON I.PrimaryKey = C.PrimaryKey

CREATE TRIGGER Upd_Price ON dbo.Price
   FOR  UPDATE  
   AS
      DECLARE @dLastUpdate DATETIME
      SET @dLastUpdate = GETDATE()
         -- Set the last Update for rows updated
      UPDATE Price 
         SET LastUpdate = @dLastUpdate
      FROM Price P
         JOIN Inserted I
          ON I.PrimaryKey = P.PrimaryKey 
         -- Write out to the Audit Log, for rows where Price changed 
      INSERT INTO AuditLog (TableName, PrimaryKey, LastUpdate, 
                             ColName, OldValue, NewValue, UserKey)
             SELECT ’PRICE’ AS TableName, I.PrimaryKey, 
                    @dLastUpdate AS LastUpdate, ’Price’ AS ColName,
                    CONVERT(CHAR(20),D.Price) AS OldValue, 
                    CONVERT(CHAR(20),I.Price) AS NewValue, 
                    I.UserKey
             FROM Inserted I
             JOIN Deleted D ON D.PrimaryKey = I.PrimaryKey
             WHERE I.Price <> D.Price
      -- Now check for any changes to the description
      INSERT INTO AuditLog (TableName, PrimaryKey, LastUpdate, 
                             ColName, OldValue, NewValue, UserKey)
             SELECT ’PRICE’ AS TableName, I.PrimaryKey, 
                    @dLastUpdate AS LastUpdate, ’Descr’ AS ColName,
                    D.Descr AS OldValue, 
                    I.Descr AS NewValue, 
                    I.UserKey
             FROM Inserted I
             JOIN Deleted D ON D.PrimaryKey = I.PrimaryKey
             WHERE I.Descr <> D.Descr

-- Must use Unicode data
DECLARE @cSQLSyntax nvarchar(2000)
DECLARE @cTableName varchar(20)
SET @cTableName = ’EMPLOYEE’
SET @cSQLSyntax = N’SELECT * FROM ’ + @cTableName  + ’ WHERE 
      EmployeeID = 1’ 
EXEC sp_executesql @cSQLSyntax
-- Return a column into an output variable
-- Query must only contain one row, else an error will occur
-- (query could also be a SUM that returns a scalar value)
DECLARE @cValue nVarChar(30)
DECLARE @cColumnName varchar(20)
SET @cColumnName = ’firstname’
SET @cSQLSyntax = N’ SELECT @cValue = ’ +  @cColumnName + ’   FROM 
   Employee where employeeid = 1’
EXECUTE SP_EXECUTESQL @cSQLSyntax, N’@cValue nVarChar(30) OUTPUT’,
    @cValue OUTPUT
SELECT @cValue

-- Return a list of table names
SELECT DISTINCT Table_Name FROM
   Northwind.INFORMATION_SCHEMA.COLUMNS
   ORDER BY Table_Name
-- Return a list of columns
-- (perform a SELECT * to see a full list)
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,
      CHARACTER_MAXIMUM_LENGTH FROM
   Northwind.INFORMATION_SCHEMA.COLUMNS
   ORDER BY Table_Name
-- You could combine the capability of LIKE
-- to find columns with a particular search
-- pattern