This question already has an answer here:
I have a 500+ lines stored proccedure which I am writting and testing using the Microsoft SQL Management studio. Today I encounter the following 'strange' case. My query (in pseudocode) look like the following
create table #TempTable1 (Name varchar(30)) -- Some queries involving #TempTable1 drop table #TempTable1 -- Some queries declare MyCursor cursor fast_forward for select ...... open MyCursor fetch next from MyCursor into @CursorValue while @@fetch_status = 0 begin create table #TempTable1 (Name varchar(30)) -- Some queries involving #TempTable1 drop table #TempTable1 fetch next from MyCursor into @CursorValue end close MyCursor deallocate MyCursor
My query fails with an error in the second
There is already an object named '#TempTable1' in the database.
I know that due to query optimizations the commands are not running in the sequence I am typing them. However this error still seems strange to me. I can put some
go commands to force the execution, but this will create some other problems in my code e.g. I will have to redeclare some variables e.t.c. Renaming the tables will solve the problem but it will harm a little the readabillity of the code. So it is more 'curiocity' than an 'actual problem'.
Any suggestions on why this is happening and how I can avoid it?