RAISERROR in a statement in the case

Question

Can you not raise errors inside the case statement in T-SQL? I always have problems with case SQL statements: /

begin try declare @i int --set @i = (select COUNT(1) from table_name) select Item_Num = CASE (select COUNT(1) from table_name) when 1 then (select Item_Num from table_name) when 0 then (raiserror('No records in database', 0, 0)) ELSE (raiserror('Multiple records in database', 0, 0)) END from table_name end try begin catch declare @errormsg nvarchar(1024), @severity int, @errorstate int; select @errormsg = error_message(), @severity = error_severity(), @errorstate = error_state(); raiserror(@errormsg, @severity, @errorstate); end catch 

Answer 1

As I said in a comment, I think it would be easier to just create a flag that you check outside of the CASE scope. Sort of:

 --- code before the TRY... BEGIN TRY DECLARE @i int -- declare a variable to act as a flag DECLARE @my_flag as int -- than change your statement to simply fill the value of the flag CASE (SELECT COUNT(1) FROM table_name) WHEN 1 THEN SET @my_flag = 1 WHEN 0 THEN SET @my_flag = 0 ELSE SET @my_flag = -1 END IF (NOT @my_flag in (-1, 0)) BEGIN SET @Item_Num = (SELECT Item_Num FROM table_name) -- consider a filter here END ELSE BEGIN IF (-1 = @my_flag) RAISERROR('too many records', 0, 0) IF (0 = @my_flag) RAISERROR('no records', 0, 0) END END TRY BEGIN CATCH --- rest of the code goes here.... 

Answer 2

Think of Case / When when working on a single piece of data. If you think so, many of your problems will disappear.

If / Then is used to control the flow of logic.

Something like this should work for you.

 declare @i int set @i = (select COUNT(1) from table_name) If @i = 1 Begin Print "1 row" End Else If @i = 0 Begin Print "no rows" End Else Begin Print "too many rows" End 

Answer 3

  With #TempTable as ( Select * from ... -- record set that determines if I should error out ) SELECT CASE WHEN COUNT(1) > 0 THEN 'Multiple records in database' ELSE 0 END AS [Value] FROM #TempTable 

A datatype type mismatch will kill this method if you try to make an error in this whole call using TSQL. Worked in my case because it was a difficult process that I needed to know was translated correctly. If my record set was> 1, then I know that I have to do this. Useful if you are using SSIS or several methods in a .NET environment.