SQL CASE Catch and Return Error
I am building a trigger to do some calculations for me. However, I am just writing the commands to see if they work f开发者_开发技巧or right now and produce error handling. So I have written the following code.
DECLARE @strTotalAssets varchar(8000)
SELECT @strTotalAssets = (SELECT ProjectOther2 FROM
Project WHERE ProjectID = '00000:')
SELECT
CASE
WHEN RIGHT(value, 1) = 'M' THEN LEFT(value, (LEN(value)-1)) * 1000000
WHEN RIGHT(value, 1) = 'T' THEN LEFT(value, (LEN(value)-1)) * 1000
WHEN RIGHT(value, 1) > 0 THEN RETURN 'Error: You forgot to put a mutliplier Value'
ELSE 'Error'
END
FROM Split(@strTotalAssets, '|')
The problem I have is that I do not know how to exit the script and return an error. Forgive my ignorance but just starting out at a jr DBA. Hopefully from the code you can see what I am trying to do. Basically if the user forgot to put a letter value which represents a multiplier of Thousands or Millions which means the value returns only an integer then produce an error and tell the user they forgot to put a value.
As this is a trigger, returning data in the true sense isnt an option; for something thats treated in the same way as say a key violation error and returned to the client in the same fashion you can RAISERROR
:
RAISERROR('You forgot to put a mutliplier Value', 15, 121)
You're looking for GOTO Error. Here's a quick primer. I only skimmed it quickly, but it looked pretty good for what you need.
精彩评论