Iam new to this sql server arena. I have implemented a procedure which does a series of insert and update statements and all of this statements must be implemented all at once or none. But if I got error in some statements , the rest of the statements are been executed. Please suggest me a way or code snippet to achieve atomicity in a sqlserver procedure.
I got a small doubt here. Whenever an error encounters, how to display the sqlserver error message rather than displaying our custom message with raiseerror. Is there any way to get the error message from sql server if an error encounters such as foriegn key violation. (invalid references to pk in mastertable)
The problem that I see is that errors come back to the client application in a "stream", much like TDS or STDOUT provide. Transact-SQL sees the most recent error, the client sees every error. Unfortunately, the formatted error message (including things like object names) is only passed to the client, I don't think that Transact-SQL can even access it (although ADO can).