Sql Code Masterz

SQL Server buffers the output, so an error message or a result set may not appear directly at the client. In many cases, this is not an issue, but if you are running a long-running procedure, you may want to produce diagnostic messages. To have them displayed immediately in the client, you can use the WITH NOWAIT clause to the RAISERROR statement, as in this example: PRINT 'This message does not display immediately' WAITFOR DELAY '00:00:05' RAISERROR ('But this one does', 0, 1) WITH NOWAIT WAITFOR DELAY '00:00:05' PRINT 'It''s over now' Once there is a message with NOWAIT , all that is ahead of the message in the buffer is also passed to the client. Unfortunately, there is a bug in SQL Server with NOWAIT , which affects you only if you are calling a procedure through RPC (remote procedure call), so that it this case, SQL Server buffers the messages nevertheless. RPC is the normal way to call a procedure from an a

@@trancount is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless you have used the fairly exotic SAVE TRANSACTION ), and forces @@trancount to 0, regards of the previous value. When you exit a stored procedure, if @@trancount does not have the same value as it had when the procedure commenced execution, SQL Server raises error 266. This error is not raised, though, if the procedure is called from a trigger, directly or indirectly. Neither is it raised if you are running with SET IMPLICIT TRANSACTIONS ON .

@@rowcount is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error you need to save it in a local variable if you want to use the value later, since @@rowcount is set after each statement. Since with SET you can only assign variable at a time, you must use SELECT if you need to save both @@error and @@rowcount into local variables: SELECT @err = @@error, @rowc = @@rowcount (For this reason, I prefer to always use SELECT for variable assignment, despite Microsoft's recommendations to use SET.) In T-SQL it is not an error if, for instance, an UPDATE statement did not affect any rows. But it can of course indicate an error in your application, as it could be an error if a SELECT returns more that one row. For these situations, you can check @@rowcount and raise an error and set a return value, if @@rowcount is not the expected value.

Here is a typical error message you can get from SQL Server when working from Query Analyzer. Server: Msg 547, Level 16, State 1, Procedure error_demo_sp, Line 2 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'fk7_acc_cur'. The conflict occurred in database 'bos_sommar', table 'currencies', column 'curcode'. The statement has been terminated. Note: Under Tools -> Options -> Connections, I have checked Parse ODBC Message Prefixes . The error information that SQL Server passes to the client consists of several components, and the client is responsible for the final interpretation of the message. These are the components that SQL Server passes to the client. Message number – each error message has a number. You can find most of the message numbers in the table sysmessages in the master database. (There some special numbers like 0 and 50000 that do not appear there.) In this example, the message number i

Many programming languages have a fairly consistent behaviour when there is a run-time error. Common is that the execution simply terminates in case of an error, unless you have set up an exception handler that takes care the error. In other languages, some error variable is set and you have to check this variable. T-SQL is confusing, because depending on what error that occurs and in which context it occurs, SQL Server can take no less than four different actions. I first give an overview of these alternatives, followed by a more detailed discussion of which errors that cause which actions. I then discuss two special cases: trigger context and user-defined functions. The Possible Actions These are the four main possible actions SQL Server can take: Statement-termination. The current statement is aborted and rolled back. Execution continues on the next statement. Any open transaction is not rolled back. @@error is set to the number of the error. Since the statement is rolled back

All stored procedures have a return value, determined by the RETURN statement. The RETURN statement takes one optional argument, which should be a numeric value. If you say RETURN without providing a value, the return value is 0 if there is no error during execution. If an error occurs during execution of the procedure, the return value may be 0, or it may be a negative number. The same is true if there is no RETURN statement at all in the procedure: the return value may be a negative number or it may be 0. Whether these negative numbers have any meaning, is a bit difficult to tell. It used to be the case, that the return values -1 to -99 were reserved for system-generated return values, and Books Online for earlier versions of SQL Server specified meanings for values -1 to -14. However, Books Online for SQL  2000 is silent on any such reservations, and does not explain what -1 to -14 would mean. With some occasional exception, the system stored