October 11, 2017 at 9:35 am
New to SQL Server. Looking for help with the following design problem:
Query:
Create a procedure named 'delete_asset_log' that takes 1 input listed below:
@assetlog_id INT
Based on this input, the procedure must do the following operation:
Design Rules:
1) If id(i.e, assetlog_id) passed as input matches with id in asset_log table, then if expected_checkin_date of that id is less than today's date and status is 'Returned', then delete the log with id equal to assetlog_id passed as input.
2) If id(i.e, assetlog_id) passed as input does not match with id in asset_log table, then insert an error_msg of type varchar(200) i.e, 'Asset log id does not exist' into asset_error_log table.
My Attempt:
I'm getting a 'Runtime Error' when I attempt to execute the following:
CREATE PROCEDURE delete_asset_log
@assetlog_id INT
AS
BEGIN
IF EXISTS (SELECT * FROM asset_log
WHERE id = @assetlog_id AND expected_checkedin_date < GETDATE( ) AND status = 'Returned')
DELETE FROM asset_log
IF NOT EXISTS (SELECT * FROM asset_log
WHERE id = @assetlog_id)
INSERT INTO asset_error_log (error_msg)
VALUES ( 'Asset log id does not exist')
END
October 11, 2017 at 9:40 am
If I understand your requirements correctly, this should do the job: Notice that I added the where condition to the delete
CREATE PROCEDURE delete_asset_log
@assetlog_id INT
AS
BEGIN
IF EXISTS (SELECT * FROM asset_log WHERE id = @assetlog_id AND expected_checkedin_date < GETDATE( ) AND status = 'Returned')
BEGIN
DELETE FROM asset_log WHERE id = @assetlog_id
END
ELSE -- (No need to specify this) IF NOT EXISTS (SELECT * FROM asset_log WHERE id = @assetlog_id)
BEGIN
INSERT INTO asset_error_log (error_msg)
VALUES ( 'Asset log id does not exist')
END
END
October 11, 2017 at 10:09 am
Worked great! Thanks a ton!
October 11, 2017 at 10:09 am
dreynolds 13587 - Wednesday, October 11, 2017 9:40 AMIf I understand your requirements correctly, this should do the job: Notice that I added the where condition to the delete
CREATE PROCEDURE delete_asset_log
@assetlog_id INT
AS
BEGIN
IF EXISTS (SELECT * FROM asset_log WHERE id = @assetlog_id AND expected_checkedin_date < GETDATE( ) AND status = 'Returned')
BEGIN
DELETE FROM asset_log WHERE id = @assetlog_id
END
ELSE -- (No need to specify this) IF NOT EXISTS (SELECT * FROM asset_log WHERE id = @assetlog_id)
BEGIN
INSERT INTO asset_error_log (error_msg)
VALUES ( 'Asset log id does not exist')
ENDEND
Worked great! Thanks!
October 11, 2017 at 10:22 am
dreynolds 13587 - Wednesday, October 11, 2017 9:40 AMIf I understand your requirements correctly, this should do the job: Notice that I added the where condition to the delete
CREATE PROCEDURE delete_asset_log
@assetlog_id INT
AS
BEGIN
IF EXISTS (SELECT * FROM asset_log WHERE id = @assetlog_id AND expected_checkedin_date < GETDATE( ) AND status = 'Returned')
BEGIN
DELETE FROM asset_log WHERE id = @assetlog_id
END
ELSE -- (No need to specify this) IF NOT EXISTS (SELECT * FROM asset_log WHERE id = @assetlog_id)
BEGIN
INSERT INTO asset_error_log (error_msg)
VALUES ( 'Asset log id does not exist')
ENDEND
Have one more I'm struggling with if you don't mind taking a look. I need to use TRY...CATCH BLOCK for this one.
Query:
Create a procedure named 'insert_category' that takes 2 inputs listed below:
@category_id INT and @category_name VARCHAR
Based on this input, the procedure must do the following operation:
Design Rules:
1) If id (i.e, category_id) passed as input is UNIQUE, then insert category_id and category_name as a new record into category table.
2) If the category_id passed as input is NOT UNIQUE, then catch the exception and insert an error_msg of type varchar(200) i.e, 'Category id is not Unique' into category_error_log table.
My Attempt:
I'm getting a 'Runtime Error' when I attempt to execute the following:
CREATE PROCEDURE insert_category
@category_id INT, @category_name VARCHAR(200)
AS
BEGIN TRY
IF NOT EXISTS (SELECT * FROM category
WHERE id = @category_id
AND name = @category_name )
INSERT INTO category (id, name)
VALUES (@category_id , @category_name)
END TRY
BEGIN CATCH
INSERT INTO category_error_log (error_msg)
VALUES ( 'Category id is not Unique')
END CATCH
October 11, 2017 at 10:29 am
the only thing I can see is that you do not have the BEGIN/END block for the creation of the procedure: Compare my code below to yours.
CREATE PROCEDURE insert_category
@category_id INT, @category_name VARCHAR(200)
AS
BEGIN -- <-- you are missing this
BEGIN TRY
IF NOT EXISTS (SELECT * FROM category
WHERE id = @category_id
AND name = @category_name )
BEGIN
INSERT INTO category (id, name)
VALUES (@category_id , @category_name)
END
END TRY
BEGIN CATCH
INSERT INTO category_error_log (error_msg)
VALUES ( 'Category id is not Unique')
END CATCH
END -- <-- you are missing this
October 11, 2017 at 10:34 am
Some best practice that I use and should help you understand your code better are as follows:
1) Be sure to indent your code blocks
2) I use verbose BEGIN/END blocks within branches of code, (this helps someone else read what I am trying to do)
3) Format your queries in a consistent pattern that a reader (or you) can easily understand what is being attempted
Hope that helps
October 11, 2017 at 10:36 am
dreynolds 13587 - Wednesday, October 11, 2017 10:29 AMthe only thing I can see is that you do not have the BEGIN/END block for the creation of the procedure: Compare my code below to yours.
CREATE PROCEDURE insert_category
@category_id INT, @category_name VARCHAR(200)
AS
BEGIN -- <-- you are missing thisBEGIN TRY
IF NOT EXISTS (SELECT * FROM category
WHERE id = @category_id
AND name = @category_name )
BEGIN
INSERT INTO category (id, name)
VALUES (@category_id , @category_name)
ENDEND TRY
BEGIN CATCH
INSERT INTO category_error_log (error_msg)
VALUES ( 'Category id is not Unique')
END CATCHEND -- <-- you are missing this
BEGIN/END is not required in the definition of a stored proc.
I think that the poster is receiving a runtime error. It would be helpful to see the full text of this error message.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 11, 2017 at 10:38 am
Also, I noticed that your requirement was only to check if the category_id was unique.
In your query, you are checking if category_id and category_name together are unique. (Not sure if that will cause you a problem)
October 11, 2017 at 11:03 am
dreynolds 13587 - Wednesday, October 11, 2017 10:38 AMAlso, I noticed that your requirement was only to check if the category_id was unique.
In your query, you are checking if category_id and category_name together are unique. (Not sure if that will cause you a problem)
That was it. Needed to remove @category_name from the SELECT statement. Thanks again.
October 11, 2017 at 10:12 pm
dreynolds 13587 - Wednesday, October 11, 2017 9:40 AMIf I understand your requirements correctly, this should do the job: Notice that I added the where condition to the delete
CREATE PROCEDURE delete_asset_log
@assetlog_id INT
AS
BEGIN
IF EXISTS (SELECT * FROM asset_log WHERE id = @assetlog_id AND expected_checkedin_date < GETDATE( ) AND status = 'Returned')
BEGIN
DELETE FROM asset_log WHERE id = @assetlog_id
END
ELSE -- (No need to specify this) IF NOT EXISTS (SELECT * FROM asset_log WHERE id = @assetlog_id)
BEGIN
INSERT INTO asset_error_log (error_msg)
VALUES ( 'Asset log id does not exist')
ENDEND
The existence check is one hit on the table and the DELETE is a second. Just a performance and resource usage tip... I believe you can do it in a single hit with something like the following.
CREATE PROCEDURE delete_asset_log
@assetlog_id INT
AS
DELETE FROM dbo.asset_log
WHERE id = @assetlog_id
AND expected_checkedin_date < GETDATE( )
AND status = 'Returned'
;
INSERT INTO asset_error_log
(error_msg)
SELECT 'Asset log id '+CONVERT(VARCHAR(20),@assetlog_id)+'does not exist'
WHERE @@ROWCOUNT = 0
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply