3. Create a stored procedure named spDateRange that accepts two parameters, @Dat

Question

3. Create a stored procedure named spDateRange that accepts two parameters, @DateMin and @DateMax, with data type varchar and defult value null. If call with no parameters or with null values, raise an erroe that describes the invalid symtax. If called with non-null values, valiate 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 invoicefor which the InvoiceDate is within the date range, sorted with earliest invoice first.

4. Code a call to the stored porcedure created in exercise 3 that returns invoices with an invoiceDate between April 10 and April 20, 2008. This call should also catch any errors that are raised by the procedure and print the error number and description.

Explanation / Answer

CREATE PROC sp_DateRange
@DateMin varchar = NULL
@DateMax varchar = NULL

If (@DateMin is null)
begin
Raiserror('Please enter the MinimumDate ',16,1)
Return

If (@DateMax is null)
begin
Raiserror('Please enter the MaximumDate ',16,1)
Return
If (@DateMin < @DateMax)
begin
Raiserror('Minimum Date can%u2019t be later then maximum date ',16,1)
Return

If @DateMin < @DateMax
Begin
(Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS Balance
From Invoices
Order By InvoiceDate
END

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

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