4. (30 points) Please write a stored function named fHW2 4 XXXX based on the Gue

Question

4. (30 points) Please write a stored function named fHW2 4 XXXX based on the Guest table. Your program should take one argument city and it should meet the following 3 test cases. 4A. 10 points) If the given city is in the 4B. (10 points) If the given city is empty Guest table, please return text with names for all or NULL, please display "Please input a valid city." guests whose address includes the given city. mysql select HW2 4 xxxx as output; mysql> select flw2 4 xxxx(London') as output mysql select fHW2 4 xxxx as output (NULL I output l output l Please input a valid city. I I John Kay, Mike Ritchie l I (10 points) If the given city is not in the Guest table, please display "No result found 4C. mysql select fHW2 4 xxxx ('Union') as output; I output I No result found

Explanation / Answer

Stored Procedure:

CREATE FUNCTION [dbo].[fHW2_4_XXXX]
(
    @City NVARCHAR(MAX)
)
BEGIN
--- Test case 2
IF(@city is null || @city = '')
BEGIN
   Select 'Please input a valid city.' as Output;
END
ELSE IF(@city is not null)
BEGIN
declare @guest varchar (200),@guestcount int 0
set @guest = ''
set @guestcount = select count(*) from dreamhome.Guest Where guestaddress like '%' + @City + '%';

--- Test case 1
if(@guestcount > 0)
BEGIN
   select @guest =
    case when @guest = ''
    then guestname
    else @guest + coalesce(',' + guestname, '')
    end
from dreamhome.Guest Where guestaddress like '%' + @City + '%';
  
Select @guest as Output
END
--- Test case 3
ELSE
   BEGIN
     'No Result Found' as Output;
   END
END

END

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

---

---

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