(4 points for code and screenshots) Create a procedure that accepts product ID a
ID: 3881090 • Letter: #
Question
(4 points for code and screenshots) Create a procedure that accepts product ID as a parameter and returns the name of the product from ProduetTable table. Add exception handling to catch if product ID is not in the table. SET SERVEROUTPUT ON: execute p_product_infornation(302) execute p product infornation(299) execute p_product_intornation (0) Script Output x >Query Result x 4 Task completed io.oo8 seconds anonymous block coapleted Product nane tor product Id 302 is Uind-Up Uater Swimners anonymous block coupleted Product nane for product Id 299 is Chest anony ous block completed No Data found for SELECT on product Id Use ProductTable created earlier in the module. Provide code and screenshot showing successful execution and results. You have to provide at least two test cases.Explanation / Answer
/*creted table*/
create table product_table(id int,name nvarchar(20))
/*inserting values to test procedure*/
insert into product_table(id,name)values(1,'abc'),(2,'cde')
/*viewing the table */
select * from product_table
/* creating a procedure */
create procedure proc_find_pnmae_res_id(@x int,@y nvarchar(20) output)
as
begin
if exists(select name from product_table where id=@x)
begin
declare @z nvarchar(50)
select @z=name from product_table where id=@x
set @y=@z
end
else
begin
print'id not matched with productname'
end
end
/*executing procedure with positive case*/
declare @a nvarchar(50)
exec proc_find_pnmae_res_id 2,@a output
print @a
/* output is cde*/
/*executing procedure with negative case*/
declare @a nvarchar(50)
exec proc_find_pnmae_res_id 4,@a output
print @a
/* output is id not matched with productname*/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.