Assignment,6-Create Procedure(2) Q- Search esign Layout References Mailings Revi
ID: 3902759 • Letter: A
Question
Assignment,6-Create Procedure(2) Q- Search esign Layout References Mailings Review View Assignment Description: For either TAL Distributors or Colonial Adventure Tours database create procedures that will insert, update and delete from one table. Both the Insert and Update procedures need to do2 column validations before they can either update or insert. The delete procedure does not need to validate. Make sure that all error messages that are outputted are correct. RESULTS 1. Create the procedures to insert, update and delete. 2. Run your procedures against the Test database to ensure there are no errors. 3. Include the SQL statement to create the procedure as well as a statement to run it in file. Include SQL statements to test that the procedure worked correctly after it is executed.Explanation / Answer
Procedure for insert, update and delete from one table:
According to the query, I take a Guid table for to perform insert, update and delete operation using stored procedure in MS SQL server.
Create a Guide table For to perform the operation:
CREATE TABLE [dbo].[TBL_GUIDE_DETAILS](
[GUIDE_NUM] [varchar](50) NOT NULL,
[LAST_NAME] [varchar](150) NOT NULL,
[FIRST_NAME] [varchar](150) NOT NULL,
[ADDRESS] [varchar](max) NULL,
[CITY] [varchar](50) NULL,
[STATE] [varchar](50) NULL,
[POSTAL_CODE] [int] NULL,
[PHONE_NUM] [varchar](50) NULL,
[HIRE_DATE] [varchar](50) NULL,
CONSTRAINT [PK_TBL_GUIDE_DETAILS] PRIMARY KEY CLUSTERED
(
[GUIDE_NUM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Here GUIDE_NUM is a unique column of TBL_GUIDE_DETAILS.
Try catch is used for error handling.
Create a procedure for insert data to Table:
For performing insert operation follow the below query, Here we check the validation for GUIDE_NUM and PHONE_NUM.
CREATE PROCEDURE [dbo].[INSERT_GUIDE_DETAILS]
-- Add the parameters for the stored procedure here
@GUIDE_NUM varchar(50),
@LAST_NAME varchar(150),
@FIRST_NAME varchar(150),
@ADDRESS varchar(max),
@CITY varchar(50),
@STATE varchar(50),
@POSTAL_CODE int,
@PHONE_NUM varchar(50),
@HIRE_DATE varchar(50),
@Msg_Out varchar(500) out
AS
BEGIN TRY
BEGIN TRAN
-- Here we check the GUIDE_NUM is exists or not
IF NOT EXISTS(SELECT * FROM [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] WHERE GUIDE_NUM=@GUIDE_NUM)
BEGIN
-- Here we check the PHONE_NUM is exists or not
IF NOT EXISTS(SELECT * FROM [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] WHERE PHONE_NUM=@PHONE_NUM)
BEGIN
INSERT INTO [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] VALUES (@GUIDE_NUM,@LAST_NAME,@FIRST_NAME,@ADDRESS,@CITY,@STATE,@POSTAL_CODE,@PHONE_NUM,@HIRE_DATE)
SET @Msg_Out='Inserted Successfully';
END
ELSE
BEGIN
SET @Msg_Out='Phone Number already exists...';
END
END
ELSE
BEGIN
SET @Msg_Out='Guide Number already exists...';
END
COMMIT TRAN
END TRY
BEGIN CATCH
SET @Msg_Out=(SELECT ERROR_MESSAGE() AS ErrorMessage);
END CATCH
FOR TO RUN THIS PROCEDURE:
DECLARE @Msg_Out varchar(max)
EXECUTE [DB_DEMO].[dbo].[INSERT_GUIDE_DETAILS] 'AM01','ROWAN','HALL','54 QUEST AVE.','WILLIAMSBURG','MA',01096,'617-666-6052','6/3/2012',@Msg_Out output
PRINT @Msg_Out
Create a procedure for update data to Table:
For performing update operation follow the below query, Here we check the validation for GUIDE_NUM and PHONE_NUM.
CREATE PROCEDURE [dbo].[UPDATE_GUIDE_DETAILS]
-- Add the parameters for the stored procedure here
@GUIDE_NUM VARCHAR(50),
@LAST_NAME varchar(150),
@FIRST_NAME varchar(150),
@ADDRESS varchar(max),
@CITY varchar(50),
@STATE varchar(50),
@POSTAL_CODE int,
@PHONE_NUM varchar(50),
@HIRE_DATE varchar(50),
@Msg_Out varchar(500) out
AS
BEGIN TRY
BEGIN TRAN
IF EXISTS(SELECT * FROM [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] WHERE GUIDE_NUM=@GUIDE_NUM)
BEGIN
DECLARE @PH varchar(50)
SET @PH= (CASE WHEN (SELECT COUNT(*) FROM [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] WHERE PHONE_NUM=@PHONE_NUM)=0 THEN '' ELSE (SELECT GUIDE_NUM FROM [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] WHERE PHONE_NUM=@PHONE_NUM) END)
IF @PH ='' OR @PH=@GUIDE_NUM
BEGIN
UPDATE [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] SET LAST_NAME=@LAST_NAME,FIRST_NAME=@FIRST_NAME,[ADDRESS]=@ADDRESS,CITY=@CITY,[STATE]=@STATE,POSTAL_CODE=@POSTAL_CODE,PHONE_NUM=@PHONE_NUM,HIRE_DATE=@HIRE_DATE WHERE GUIDE_NUM=@GUIDE_NUM
SET @Msg_Out='Updated Successfully';
END
ELSE
BEGIN
SET @Msg_Out='Phone Number Already Exist';
END
END
ELSE
BEGIN
SET @Msg_Out='Guid Number is not exist in our database';
END
COMMIT TRAN
END TRY
BEGIN CATCH
SET @Msg_Out=(SELECT ERROR_MESSAGE() AS ErrorMessage);
END CATCH
FOR TO RUN THIS PROCEDURE:
DECLARE @Msg_Out varchar(max)
EXECUTE [DB_DEMO].[dbo].[UPDATE_GUIDE_DETAILS] 'AM01','ROHAN','HALL','54 QUEST AVE.','WILLIAMSBURG','MA',11096,'617-656-6052','6/3/2012',@Msg_Out output
PRINT @Msg_Out
Create a procedure for delete data to Table:
For performing delete operation follow the below query,
CREATE PROCEDURE [dbo].[DELETE_GUIDE_DETAILS]
-- Add the parameters for the stored procedure here
@GUIDE_NUM VARCHAR(50),
@Msg_Out varchar(500) out
AS
BEGIN TRY
BEGIN TRAN
IF EXISTS(SELECT * FROM [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] WHERE GUIDE_NUM=@GUIDE_NUM)
BEGIN
DELETE FROM [DB_DEMO].[dbo].[TBL_GUIDE_DETAILS] WHERE GUIDE_NUM=@GUIDE_NUM
SET @Msg_Out='Deleted Successfully';
END
ELSE
BEGIN
SET @Msg_Out='Guid Number is not exist in our database';
END
COMMIT TRAN
END TRY
BEGIN CATCH
SET @Msg_Out=(SELECT ERROR_MESSAGE() AS ErrorMessage);
END CATCH
FOR TO RUN THIS PROCEDURE:
DECLARE @Msg_Out varchar(max)
EXECUTE [DB_DEMO].[dbo].[DELETE_GUIDE_DETAILS] 'AM01',@Msg_Out output
PRINT @Msg_Out
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.