1. Create a stored procedure named spWhichTable that accepts a column name and r

ID: 3697211 • Letter: 1

Question

1. Create a stored procedure named spWhichTable that accepts a column name and returns the name of the table or tables that have a column by that name. Code a statement that calls the procedure.

2. Create a stored procedure named spDateRange that accepts two parameters, @DateMin and @DateMax, with data type varchar and default value null. If called with no parameters or with null values, the procedure should return an error message describing the syntax. If called with non-null values, validate the parameters. Test that the literal strings are valid dates and test that @DateMin is earlier than @DateMax. If the parameters are valid, return a result set that includes the InvoiceNumber, InvoiceDate, InvoiceTotal, and Balance for each invoice for which the InvoiceDate is within the date range, sorted with earliest invoice first.

3. Create a scalar-valued function named fnUnpaidInvoiceID that returns the InvoiceID of the earliest invoice with an unpaid balance. Test the function in the following SELECT statement: SELECT VendorName, InvoiceNumber, InvoiceDueDate,

InvoiceTotal - CreditTotal - PaymentTotal AS Balance

FROM Vendors JOIN Invoices

ON Vendors.VendorID = Invoices.VendorID

WHERE InvoiceID = dbo.fnUnpaidInvoiceID()

4. Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels. The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows:

CREATE TABLE ShippingLabels

(VendorName varchar(50),

VendorAddress1 varchar(50),

VendorAddress2 varchar(50),

VendorCity varchar(50),

VendorState char(2),

VendorZipCode varchar(20))

5. A column that accepts null values but has a unique constraint can only have a single row with a null value. Write a trigger that prohibits duplicates, except for nulls. Use the following table. If an INSERT or UPDATE statement creates a duplicate value in the NoDupName column, roll back the statement and return an error message.

CREATE TABLE TestUniqueNulls

(RowID int IDENTITY NOT NULL,

NoDupName varchar(20) NULL)

Explanation / Answer

2.
/*Caleb Meador meadorjc at gmail.com*/
create proc spDateRange
   @DateMin varchar(50) = null,
   @DateMax varchar(50) = null
as
if @DateMin is null or @DateMax is null
   throw 50001, 'The DateMin and DateMax parameters are required.', 1;
if not(ISDATE(@DateMin) = 1 and ISDATE(@DateMax) = 1)
   throw 50001, 'The format is not valid. Please use mm/d//yyyy.', 1;
if CAST(@DateMin as date) > cast(@DateMax as date)
   throw 50001, 'The DateMin parameter must be earlier than DateMax),', 1;

select InvoiceNumber, InvoiceDate, InvoiceTotal,
InvoiceTotal - CreditTotal - PaymentTotal as BalanceDue
from Invoices
where InvoiceDate between cast(@DateMin as datetime) and cast(@DateMax as datetime)
order by InvoiceDate

3.
USE AP;
GO

CREATE FUNCTION fnUnpaidInvoiceID()
RETURNS int
BEGIN
    RETURN
    (SELECT MIN(InvoiceID)
     FROM Invoices
     WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 AND
           InvoiceDueDate =
     (SELECT MIN(InvoiceDueDate)
      FROM Invoices
      WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0));
END;

USE AP;
GO

CREATE TRIGGER Invoices_UPDATE_Shipping
    ON Invoices
    AFTER INSERT, UPDATE
AS
    INSERT ShippingLabels
    SELECT VendorName, VendorAddress1, VendorAddress2,
           VendorCity, VendorState, VendorZipCode
    FROM Vendors JOIN Inserted
      ON Vendors.VendorID = (SELECT VendorID FROM Inserted)
    WHERE Inserted.InvoiceTotal - Inserted.PaymentTotal
        - Inserted.CreditTotal = 0;

5.
USE AP;
GO

CREATE TRIGGER NoDuplicates
ON TestUniqueNulls
AFTER INSERT, UPDATE AS
BEGIN
   IF
    (SELECT COUNT(*)
     FROM TestUniqueNulls JOIN Inserted
       ON TestUniqueNulls.NoDupName = Inserted.NoDupName) > 1
   BEGIN
    ROLLBACK TRAN;
   THROW 50001, 'Duplicate value.', 1;
   END;
END;

1.
CREATE PROCEDURE spWhichTable @ColumnName VARCHAR(50)
AS

SELECT A.[Name] AS [TableName]
FROM [dbo].[sysobjects] A INNER JOIN [dbo].[syscolumns] B
ON A.[ID] = B.[ID] AND
A.[XType] = 'U' AND
B.[Name] = @ColumnName

Navigate

1. Create a stored procedure named insert_glaccount that lets a user add a new r

1. Create a struct MenuItem containing fields for name (a string) and price (a f

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

1. Create a stored procedure named spWhichTable that accepts a column name and r

Question

Explanation / Answer

Related Questions

Navigate