开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜