Create a stored procedure named spWhichTablc that accepts a column name and retu

ID: 3696467 • Letter: C

Question

Create a stored procedure named spWhichTablc 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 Create a stored procedure named spDatcRangc 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 create a scalar-valued function named fnUnpaidlnvoiceID that returns the InvoicelD of the earliest invoice with an unpaid balance Test the function in the following SELECT statement SELECT VendorName, InvoiceNumber, InvoiceDueDate, InvoieTotal - CreditTotal - paymentTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorlD WHERE InvoicelD = dbo.fnUnpaidlnvoicelD() 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), VendorAddressl varchar(50), VendorAddress2 varchar(50), VendorCity varchar(50), VendorState char(2), VendorZipCode varchar(20)) 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 TestUniqueNulIs (RowID int IDENTITY NOT NULL, NoDupName varchar(20) NULL)

Explanation / Answer

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

2.
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;

4.
/*Caleb Meador meadorjc at gmail.com*/
create trigger Invoices_UPDATE_Shipping
   on Invoices
   after insert, update
as
   insert ShippingLabels
   select VendorName, VEndorAddress1, VendorAddress2,
   VendorCity, VendorState, VendorZipCode
   from Vendors join inserted /*inserted means 'seen a change'*/
   on vendors.vendorID = (select VendorID from inserted)
   where inserted.InvoiceTotal - inserted.CreditTotal - inserted.PaymentTotal = 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;

Navigate

Create a stored procedure named spDateRange that accepts two parameters, @DateMi

Create a stored procedure that can be used to add a student to the school and a

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

Create a stored procedure named spWhichTablc that accepts a column name and retu

Question

Explanation / Answer

Related Questions

Navigate