Question

All of the data for your project has been inserted with the exception of the shippingMethods table. Your task is to create a new stored procedure called Insert_ShippingMethod_Data and write the necessary code to insert the shipping method data into your tables. Once you create the stored procedure, you must execute the stored procedure in order to populate the shippingMethods table. The data to be inserted into your table is below. Check task 5 for the data types of each attribute to make sure you use the correct format when inserting the data.

MS SQL server

vRate 0.5 0.5 30 1 1 shipID 101 102 103 104 201 202 203 204 301 302 303 304 company Fedex Fedex Fedex Fedex UPS UPS UPS UPS USPS USPS USPS USPS shipping Methods method fRate Standard Ground 10 Standard Air 20 Express Air Next Day Air 40 Standard Ground 15 Standard Air 25 Express Air 35 Next Day Air 45 Standard Ground 5 Standard Air 15 Express Air 30 Next Day Air 40 baseWeight 20 10 10 10 15 15 15 15 0.5 0.5 0.5 0.5 0.5 0.5 20 10 10 1 1 10

Answer 1

Stored Procedure :

CREATE PROCEDURE Insert_ShippingMethod_Data
-- Add the parameters for the stored procedure here
@shipID int,
@company varchar(50),
@method varchar(50),
@fRate int,
@vRate real,
@baseWeight int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO shippingMethods
(shipID,company,method,fRate,vRate,baseWeight)
VALUES
(@shipID,@company,@method,@fRate,@vRate,@baseWeight)
END
GO

--------------------------------------------------------------------------------------------------------------------------------------------

Record insertion by using stored procedure :

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 101, @company = 'Fedex', @method = 'Standard Ground', @fRate = 10, @vRate= 0.5,@baseWeight= 20
GO

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 102, @company = 'Fedex',@method = 'Standard Air',@fRate = 20,@vRate= 0.5, @baseWeight= 10
GO

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 103, @company = 'Fedex',@method = 'Express Air',@fRate = 30,@vRate= 1, @baseWeight= 10
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 104, @company = 'Fedex',@method = 'Next Day Air',@fRate = 40,@vRate= 1, @baseWeight= 10
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 201, @company = 'UPS',@method = 'Standard Ground',@fRate = 15,@vRate= 0.5, @baseWeight= 15
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 202, @company = 'UPS',@method = 'Standard Air',@fRate = 25,@vRate= 0.5, @baseWeight= 15
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 203, @company = 'UPS',@method = 'Express Air',@fRate = 35,@vRate= 0.5, @baseWeight= 15
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 204, @company = 'UPS',@method = 'Next Day Air',@fRate = 45,@vRate= 0.5, @baseWeight= 15
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 301, @company = 'USPS',@method = 'Standard Ground',@fRate = 5,@vRate= 0.5, @baseWeight= 20
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 302, @company = 'USPS',@method = 'Standard Air',@fRate = 15,@vRate= 0.5, @baseWeight= 10
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 303, @company = 'USPS',@method = 'Express Air',@fRate = 30,@vRate= 1, @baseWeight= 10
Go

EXECUTE [dbo].[Insert_ShippingMethod_Data]
@shipID = 304, @company = 'USPS',@method = 'Next Day Air',@fRate = 40,@vRate= 1, @baseWeight= 10
Go

-----------------------------------------------------------------------------------------------------------------------------------------------

Veiw all Records in database table stored procedure :

create Procedure Select_From_ShippingMethods
As
Begin

select * from shippingMethods;

End
Go

--------------------------------------------------------------------------------------------------------------------------------------

Exeucte above stored procedure as :

Execute
Select_From_ShippingMethods
Go

------------------------------------------------------------------------------------------------------------------------------------------