|
Good to hear, you're welcome 
|
|
|
|
|
Hello everyone,
If in a store procedure, it is using SET XACT_ABORT ON (auto rollback transaction when there is error). Currently, I just rely on the output of the store procedure (when there is any errors inside) to handle success/error at client side (e.g. ADO.Net client), now I want to add RAISEERROr statement to throw the error outside to client. But I can not think of how to add RAISEERROR and fill in the related parameters for this function. Does anyone have any ideas?
Here is my pseudo code.
SET XACT_ABORT ON;
-- DML operations
commit;
thanks in advance,
George
|
|
|
|
|
|
Thanks J4amieC,
My question is different. 
Whether RAISEERROR must be used in catch block? Any ideas or comments?
regards,
George
|
|
|
|
|
Using RAISERROR[^].
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 )
|
|
|
|
|
Thanks Hamid!
I like this link you recommended!
regards,
George
|
|
|
|
|
Hi all,
I have a table whick have got 2 fields [id - info], for example :
id info
1 A
2 B
3 C
...
Now, in my program, there is an array of id in program, ex : [1, 3, ...]. Now, I want to query the table to get the corresponding array of objects[id-info] like that : [[1, A], [3, C]...]. I knew some ways, but I think theys are still optimized, as following, 2 ways I knew :
- foreach array of id, get info correspoding the id, then add to the result array => each id, I have to query the DB 1 time => not good.
- generate a big select query with WHERE Condition is like (where id = 1 or id = 2 ...) => query DB 1 time, but the condition expression is complex to verify for DB.
- generate a big select query with Where condition is like (where id in[1, 2, 3]) => similair to 2rd way, not very good.
I think there are still better way, could you help me.
Currently, I am using C# with MSSQL, but I will change to MySQL in several months.
Thanks in advance.
|
|
|
|
|
You could modify the in list to a XML query and use that, but since you're changing ty MySQL that most likely won't work in there. Using IN (value1, value2, ...) is supported by all DBMS although each DBMS may have restrictions in the amount of values. However, that's a working solution and the optimizer tries to find the best permutation for execution.
If the in list is large, this could be a modeling issue, but without anymore info about the problem, it's hard to say.
|
|
|
|
|
Yeah, I am agree, using IN expression is the best way in those three ways. But, in algorithm view, if we can convert the array of Id to a temporary table which have only field "Id", then we inner join two table on "Id". I think if the conversion is possible, that is the best solution, I have not much experience on SQL and programming, I still hope find a better way.
|
|
|
|
|
That's true. If you would have the data in db (in some table format) that would be the best for the query itself. However, how would you get the data to the temporary table. In worst scenario you add it row by row. Little better if you can add them simultaneously, but still you're creating a table and populating it and both of those are time and resource consuming operations. So in overall even if the last query gets simplified, the overall throughput may be worse.
This is exactly why I mentioned the modeling of the db. If you could prevent those dynamic lists or make them significantly smaller, it would have a great impact to your query. But in order to do that, you would have to find common "feature" from the values, which you should then store in lookup tables. I can't say if this is possible at all or how it would be implemented in your case since it would require a whole lot of information about the issue.
One possibility you could use is to create a procedure which gets an array (or user defined table) of values and then uses a those values in join using the original query. I wrote a brief article on that: http://www.codeproject.com/KB/database/TableValuedParameters.aspx[^] but the downside is that it's SQL Server specific.
|
|
|
|
|
Hello everyone,
I am not sure my following code must be put between "begin catch" and "end catch"? Or they could be in any where in a store procedure -- e.g. outside "begin catch" and "end catch"?
IF @@ERROR <> 0
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END -- end of IF @@ERROR <> 0
thanks in advance,
George
|
|
|
|
|
1.
Read the documentation on SQL Books Online about BEGIN CATCH ... END CATCH
edit: I know you're too bone-idle to search the internets, so here you go: http://technet.microsoft.com/en-us/library/ms175976.aspx[^]
2.
Try it out for yourself, observe the behaviour, feel free to ask a question about a specific aspect of the behaviour observed if you need to.
|
|
|
|
|
Thanks J4amieC,
The link looks great! But does not answer all of my questions.
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
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
