I wanted to avoid the case statement. I also looked into creating table types and using sp_executesql. But that defeats the purpose of the table variable because I now...

Thanks everyone. I did find what the issue was, and that's because I was running the procedure after a SET FMTLONLY ON, which forced every if statement to run. ...

I understand the best practices and I am quite experienced with SQL server. I haven't seen this error before. Unfortunately the procedure is over 2,000 lines long and has a...

Here's a snippet of the procedure. The error is pointing to MyNotificationProcedure.

create procedure MyProc

as

begin try

-- SQL Code

;with MyRecursion

as

(

-- Anchor

union all

-- Recursion

)

select * from MyRecursion

-- SQL Code

return 0

end try

begin catch

exec MyNotificationProcedure...