|
Begin/End catch was added in Sql Server 2005 and is kind of handy to avoid having to check the status of @error at multiple points in your stored proc.
The code you've pasted looks like it would belong in a catch. A common pattern looks something like this:
begin transaction
begin try
...
...
...
commit transaction
end try
begin catch
rollback transaction
raiserror(ERROR_MESSAGE(), ERROR_SEVERITY())
end catch
You don't need to check @error any more - that is implied by reaching the catch block. Can't see any point in checking @@trancount either - either the commit or the rollback will get hit.
Regards,
Rob Philpott.
|
|
|
|
|
Thanks Rob!
Your code sample looks great! But it covers how to handle and raise exception to client (e.g. an ADO.Net client program in my scenario) when there is transaction and try/catch.
But my scenario is different. Some store procedure I am maintaining is simple, just a wrap of a single insert/delete/update statement, and they are not in a transaction -- they also do not wrapped in try/catch.
In this situation, what is your suggested solution to return meaningful error information to client (e.g. an ADO.Net client program in my scenario)?
regards,
George
|
|
|
|
|
Hey George,
Well, if I understand it right and that you have a simple stored proc which is just a single insert/update/delete etc. you won't be needing a transaction as the command will either succeed or fail, so no rollback will be needed.
In this case, from code, you can just call the stored proc directly. Should it fail (FK constraint for instance) this will be raised as an SQLException in the code. This comes for free, you don't need to do anything to get this.
If you've got a batch of commands which you want to work atomically (either all complete successfully or none do (rollback)) you'll want to wrap the sql in a transaction in the proc (as originally posted).
There is the ability to create transactions on the Connection object in code, but personally I wouldn't do this. Transactions should always be as small as possible, and you can't really control the scope as effectively.
Hope that makes some sort of sense.
Regards,
Rob Philpott.
|
|
|
|
|
Thanks Rob,
I have verified your solution works. A further question, must we use raiseerror inside catch block? Could it be used outside catch block?
regards,
George
|
|
|
|
|
I don't think so - not 100% to be honest. Why would you do this though? Surely you would only want to raise an error in the event that an exception has been raised and caught in the catch block?
Raising it elsewhere doesn't make much sense to me.
Regards,
Rob Philpott.
|
|
|
|
|
George_George wrote: am not sure my following code must be put between "begin catch" and "end catch"?
Why not? It simplifies error handling and provides an easy way to do some stuff in an error and exit cleanly.
I suppose that the @trancount condition is based on our previous conversation about transaction scope. If that's true the implementation isn't quite what I suggested (the original suggestion was, if transaction is started outside the scope, don't rollback).
|
|
|
|
|
Hi Mika!
1.
"If that's true the implementation isn't quite what I suggested (the original suggestion was, if transaction is started outside the scope, don't rollback)." -- in my situation, all transaction are started at server. I think you mean if transaction are started at client, it is client's responsibility to rollback transaction?
2.
I think it is always better to implement transaction at server side, not client side, correct?
(I think the benefit is, keep transparent to client side.)
3.
I know using unified try/catch error handling is good. But my scenario is different. Some store procedure I am maintaining is simple, just a wrap of a single insert/delete/update statement, and they are not in a transaction -- they also do not wrapped in try/catch.
In this situation, what is your suggested solution to return meaningful error information to client (e.g. an ADO.Net client program in my scenario)?
regards,
George
|
|
|
|
|
George_George wrote: I think you mean if transaction are started at client, it is client's responsibility to rollback transaction
Not exactly. What if the transaaction was started by another procedure which then calls this one.
George_George wrote: I think it is always better to implement transaction at server side, not client side, correct?
This would be a matter of opinion and architecture decision.
George_George wrote: In this situation, what is your suggested solution to return meaningful error information to client
Depending on the situtation, I would return the original error or use RAISERROR to add more descriptive information.
|
|
|
|
|
See Using TRY...CATCH in Transact-SQL[^].
Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )
|
|
|
|
|
Hi Guys,
Does any body tel me How to COnvert Column in to rows
Say Examples my Table contains
A B C D
1 1 3 5
2 5 6 7
I want this to be shown as
columnNames Value1 value2
A 1 2
B 1 5
C 3 6
D 5 7
any body assist me on this??
|
|
|
|
|
|
Thanks Buddy and could YOU tel me the best URL or PDF to study abt SQL SERVER REPORTING SERVICES
|
|
|
|
|
Vignesh Krishnan wrote: Thanks Buddy
You're welcome
Vignesh Krishnan wrote: the best URL or PDF to study abt SQL SERVER REPORTING SERVICES
Don't know what's best, but you could start from here: Tutorials (Reporting Services)[^]
|
|
|
|
|
Hi
I have records like this
Say
Tme_On /dial /connect /month
1 1 1 jan
2 3 4 Dec
3 3 5 nov
I want this to be shown as
Total Jan Dec Nov
Tme_On 1 2 3
Dial 1 3 3
Connect 1 4 5
Can any one Help me ...
|
|
|
|
|
Like Mika said use pivots. If you can't even have a look at it and at least pattern from a sample solution to fit your problem then I think you're in the wrong industry... Or is the codez urgentz?
----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list
|
|
|
|
|
I have tried it and i have got it.dont think wrong on person those who was unknown to u swjam.
|
|
|
|
|
|
Infinite loop?
|
|
|
|
|
I strongly encourage you to read the usage examples and test different variations using your tables. But to get you to start: For example, to get the first row of your data, you would fetch tme_on and month. I added an inline view since I don't have your table but you would replace that with a select to your table. The same idea goes with the other rows.
select 'tme_on' as heading, [jan],[dec], [nov]
from (
select 1 as tme_on, 'jan' as [month]
union
select 2 as tme_on, 'dec' as [month]
union
select 3 as tme_on, 'nov' as [month]
) alias
pivot (
sum(tme_on)
for [month] in ([jan],[dec], [nov])
) as resulttable
|
|
|
|
|
The Query inside my MS Access database looks like the following:
Code:
SELECT
dbo_MyTab1.MyCol4, dbo_MyTab2.MyCol3
FROM
dbo_MyTab2 INNER JOIN dbo_MyTab1
ON dbo_MyTab2.MyCol2 = dbo_MyTab1.MyCol2
WHERE
(((dbo_MyTab1.MyCol1)=0)
AND dbo_MyTab1.MyCol3 NOT LIKE '%Expr%')
AND ((dbo_MyTab2.MyCol4)=False)));
Given that in the backend database:
Code:
MyTab1.MyCol1 is (bit, not null)
MyTab2.MyCol4 is (Flag(bit), not null)
The problem is that when I execute the query, it prompts a dialog box asking for the value of MyTab1.MyCol1.
There should be none of this dialog pop-up. Anyone encountered this before?
Hope this helps make the problem clearer?
----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list
|
|
|
|
|
i got 2 tables Reports_tbl and Manger_tbl
Reports_tbl
Manager DepID NumOfEmp_Reports
1 1 2
1 2 5
1 3 1
2 1 13
2 2 10
2 3 0
3 1 1
3 2 2
3 3 9
Manager_tbl
ManagerID ManagerName
1 Sam
2 Joseph
3 Paul
assuming that DeptID as Fin =1, Mng =2 and Sec =3 because i have to show Name of departments and NumOfEmp_Reports to sam,joeph,and paul
i have written query
select m.ManagerName, r.NumOfEmp_Reports as Fin ,r.NumOfEmp_Reports as Mng ,r.NumOfEmp_Reports as Sec
from Manager_tbl as m
Inner join Rerports_tbl as r
on m.managerId =r.mangaerId
the result of this query is as
ManagerName Fin Mng Sec
Sam 2 2 2
Sam 5 5 5
sam 1 1 1
Joseph 13 13 13
Joseph 10 10 10
Joseph 0 0 0
Paul 1 1 1
Paul 2 2 2
Paul 9 9 9
but i have to diplay result as following
ManagerName Fin Mng Sec
Sam 2 5 1
Joseph 13 10 0
Paul 1 2 9
thnx in adv for any kind help.
regards learner
|
|
|
|
|
If I interpreted yhe question correctly, you could use PIVOT[^]
|
|
|
|
|
thanks for your kind help.
|
|
|
|
|
|
Hi,
My stored procedure returning XML data (I am using FOR XML Auto ) . I want to save the same as XML File from that procedure itself . I am using SQL Server 2005 .
Any Idea to implement
Thanks in advance !
|
|
|
|