1.Create a stored procedure named spWhichTable that accepts a column name and re
ID: 3579887 • 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
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 Procedure spDateRange
@DateMin varchar(20) = Null,
@DateMax varchar(20) = Null
As
SET NOCOUNT ON
If @DateMin Is Null Or @DateMax Is Null
Return 1
If ISDATE(@DateMin) = 0 Or ISDATE(@DateMax) = 0
Return 2
If CAST(@DateMin as datetime) >= CAST(@DateMax as datetime)
Return 3
Select InvoiceNumber,
InvoiceDate,
InvoiceTotal,
Balance
From Invoices
Where InvoiceDate BETWEEN CAST(@DateMin as datetime) AND CAST(@DateMax as datetime)
Order By InvoiceDate
Return 0
3.A space should be between top & 1.
Select top 1 @result = FROM Invoices order by InvoiceDueDate
4.CREATE TRIGGER trg_Invoices_ShippingLabels_Update
ON Invoices
AFTER UPDATE
AS
Insert into shippinglabels (vendorName,vendoraddress1,vendoraddress2,vendorcity,vendorstate,vendorzip)
SELECT VendorName,VendorAddress1,VendorAddress2
,VendorCity,VendorState,VendorZip
FROM Inserted as I
Inner JOIN Deleted as D
on i.invoiceid=d.invoicesid
Inner Join Vendors as V
on I.invoiceid=v.Invoiceid
Where i.paymentTotal <> d.PaymentTotal
5.
CREATE TABLE TestUniqueNulls
(
RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL
)
GO
Create Trigger NoDuplicates
On TestUniqueNulls
After Insert, Update AS
Begin
IF
(Select Count(Distinct A.NoDupName)
From TestUniqueNulls AS A join TestuniqueNulls AS B
On A.NoDupName = B.noDupName) > 1
Begin
RollBack Tran
RaisError ('Duplicate value', 11, 1)
End
End
GO
3.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.