February 25, 2015 at 11:47 am
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
February 25, 2015 at 6:05 pm
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);February 26, 2015 at 2:07 am
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.
February 26, 2015 at 2:41 am
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?
February 26, 2015 at 11:26 am
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.
February 26, 2015 at 11:58 am
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?
February 26, 2015 at 12:45 pm
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply