Given the database table schema below, write a stored procedure called SP Showwx

Question

Given the database table schema below, write a stored procedure called SP Showwx Alert. This stored procedure will accept two arguments. The first argument will be the county (represented by the counties' field) and the second argument will be the event type (represented by the 'event type' field). Each of these arguments will use the SQL LIKE' clause so that a user of this stored procedure can enter a partial county name and a partial event type. The student can implement this by using the source below as an example WHERE data like CONCAT C'% arg1. The stored procedure will return log dt, event type, and expire time from the table below based on the parameters passed into the stored procedure. You will create this stored procedure in a text file that can be run using the MysOL source' command. This file will be named SP showwxAlert.txt and will be included in a ZIP file Be sure to account for the proper handling of the delimiter in the script file as well as the proper use of the database

Explanation / Answer

use noaa_wx_alerts

IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'SP_ShowWXAlert')
   DROP PROCEDURE dbo.SP_ShowWXAlert
GO
CREATE procedure [dbo].SP_ShowWXAlert
@Countries Varchar(150),
@event_type varchar(60)

AS
BEGIN

Select log_dt,event_type,expire_time from wx_alerts
Where countries like CONCAT('%',arg1,'%')
   And event_type like CONCAT('%',arg1,'%')
   and countries = @Countries
   and event_type = @event_type

   end

Exec SP_ShowWXAlert '<CountriesName>','<Event_type>'

Explanation:

1. Use command is used to select the database name , here we select noaa_wx_alerts database name as per question.
2. Line 2 check the stored procedure is already exist in database or not if already exist than first need to delete and than create new Stored procedure.
3. creating the stored procedure by passing the two parameters, i). @Countries and ii) @event_type than selecting the column as per question.
4. 'exec' clause is used to execute the stored procedure. we need to pass <CountriesName> & <Event_type> than execute the sp.

Note: We need to create Job scheudle by using the above stored procedure and give the file name as SP_ShowWXAlert.txt and correct path where data is stored like E:

DECLARE @cmd varchar(1000)
SET @cmd = 'bcp "EXEC dbo.SP_ShowWXAlert" queryout "c:sharesSP_ShowWXAlert.txt" -c -UTF8 -T -Slocalhost'
EXEC master..xp_cmdshell @cmd

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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