I ran across this weird problem while debugging a stored procedure in Sql Server 2000.
Here's a sample stored procedure:
drop procedure p1
go
create procedure p1
as
create table #t1(
col1 int identity(1,1),
col2 datetime,
col3 int
)
select *
from #t1
return
update #t1
set col3 = col2
return
Ok, notice that I have a return statement after the first select. That is my exit point for now because I am debugging so I am not interested in the section after that. Also notice that the update statement will throw an error like so:
Disallowed implicit conversion from data type datetime to data type int, table 'tempdb.dbo.#t1_________________________________________________________________________________________________________________000100004066', column 'col3'. Use the CONVERT function to run this query.
Now, logically if I run my stored procedure, I know that I will return after the first select statement and I should not get a runtime error at all, but what really happens, is that executing the stored proc throws an error at runtime on a block of code that will never be run! Go figure!
The obvious solution is to either comment out the offending code, or to fix it first before running the SP.