Try catch block in while loop

Question

Try catch block in while loop

MaggieW

SSC Eights!

Points: 947
More actions
February 25, 2015 at 11:47 am

#298045

Dear all:
I encountered a werid bug that I can't figure it out in my stored procedure.
Here's some sample code the can represent the scenario
Create Proc sp_test
@DeptID Int
as
Begin
Declare @i int=0
Declare @Count int=(Select count(*) from Total)
while(@i<@Count)
Begin
Select @Name=Name from Total
where Row_Number=(@i+1) and DeptID=@DeptID
Begin try
Select * into #temp from Employee where Name=@Name
End try
Begin catch
If Error_Number<>'123'
Select * from #temp
End catch
Drop table #temp
SET @i=@i+1
end
End
Go
In the above code Total is a table that has employee name and its department ID and row_number info.
The above code should list all employee info that belongs to one DEpt
but after I placed a try catch block the select statement returns no records.
If I removed the try catch block it behaves correct
I am really puzzled at this point and have no clue how to fix it.
Thank you for you help!
For example If three records reside in the Total table for a certain DeptID, Let's say the three names are Mike, Tom, Frank
I expect the outPut will be
Name age salary
Mike 35 $60006
Tom 50 $75000
Frank 55 $120000
But instead I got nothing
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

MaggieW (2/25/2015)
Dear all:
I encountered a werid bug that I can't figure it out in my stored procedure.
Here's some sample code the can represent the scenario
Create Proc sp_test
@DeptID Int
as
Begin
Declare @i int=0
Declare @Count int=(Select count(*) from Total)
while(@i<@Count)
Begin
Select @Name=Name from Total
where Row_Number=(@i+1) and DeptID=@DeptID
Begin try
Select * into #temp from Employee where Name=@Name
End try
Begin catch
If Error_Number<>'123'
Select * from #temp
End catch
Drop table #temp
SET @i=@i+1
end
End
Go
In the above code Total is a table that has employee name and its department ID and row_number info.
The above code should list all employee info that belongs to one DEpt
but after I placed a try catch block the select statement returns no records.
If I removed the try catch block it behaves correct
I am really puzzled at this point and have no clue how to fix it.
Thank you for you help!
For example If three records reside in the Total table for a certain DeptID, Let's say the three names are Mike, Tom, Frank
I expect the outPut will be
Name age salary
Mike 35 $60006
Tom 50 $75000
Frank 55 $120000
But instead I got nothing

Your select only happens if the insert into #temp errors, so you shouldn't really expect any results.

It's hard to say what the solution is because I don't know why you are inserting into #temp then dropping #temp - it is strange.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sunder.bugatha Ten Centuries Points: 1227 More actions · Answer 2

There are two things to be taken care in the code :

1) Select count(*) from Total - this should be Select count(*) from Total where deptid= @deptid

2) drop table #temp should be after "End" statement of where clause.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

and more, looping

Select * into #temp from Employee where Name=@Name

will give you n*m rows for every name which has n occurences in the given department in Total and m occurences in the Employee table. Is it what you need?

MaggieW SSC Eights! Points: 947 More actions · Answer 4

Thank you for all the responses! Guys

I just figured out that I need to move the select* from #temp into the try block. I think I am just unclear how the program flows for Try catch block.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Why the cursor, the temp tables or the TRY...catch at all? This may be a dummied out scenario but as it stands, aren't you simply looking for :

Create Proc sp_test

@DeptID Int

as

select emp.* from employees emp join total tot on emp.name=total.name

where tot.deptid=@deptid

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

MaggieW SSC Eights! Points: 947 More actions · Answer 6

I just use a simple example to illustrate what I want to achieve. In reality I have a dynamic sql to openquery a MDX query and the parameterized MDX query sometimes return empty result set that will throw errors and break the code, I thought to use a Try catch block to catch the error but am not clear about the flow the block but now everything is solved. Thank you for the input!