[bHow to assign the Error Messages to a variable ( Urgent Help needed)

[13415]

I want to asssign the error messages generate inside the stored proedure to a local variable. Please suggest some way.

Here is an example
SELECT * FROM ThisObjectDoesNotExist

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'ThisObjectDoesNotExist'.

SQL server will show this mesage in SQL analyser , I want to assign this message directly to a variable.

Thanking you

 

[Hal10K]

Use @@Error and @@RowCount. The extract of the books online below says that using their method, only the last error of a stored procedure can be shown, but I have in other similar situations, coded an error word, e.g.

/* from the SQL2K BOL, as modified by Hal */
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
@MaxQuantity INT OUTPUT
AS
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0 -- H10K: 0 is no errors

-- Do a SELECT using the input parameter.
SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm

-- Save any non-zero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @ErrorSave + 1 -- H10K: you lose the error code
-- Set a value in the output parameter.
SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]

-- Save any non-zero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @ErrorSave + 2 -- H10K: you lose the error code, but get multiple errors reported

-- Returns 0 if neither SELECT statement had
-- an error, otherwise returns the last error.
RETURN @ErrorSave

The bitmapping of errors by powers of 2 can be extended by mapping other ranges to the integer. If you need more complex error reporting, look at the OUTPUT parameter for stored procedures.
-hal


Other verbage from the SQL2K BOL:
The @@ERROR system function returns 0 if the last Transact-SQL statement executed successfully; if the statement generated an error, @@ERROR returns the error number. The value of @@ERROR changes on the completion of each Transact-SQL statement.

Because @@ERROR gets a new value when every Transact-SQL statement completes, process @@ERROR in one of two ways:

Test or use @@ERROR immediately after the Transact-SQL statement.


Save @@ERROR in an integer variable immediately after the Transact-SQL statement completes. The value of the variable can be used later.
@@ERROR is the only part of a Microsoft® SQL Server™ 2000 error available within the batch, stored procedure, or trigger that generated the error. All other parts of the error, such as its severity, state, and message text containing replacement strings such as object names, are returned only to the application in which they can be processed using the API error handling mechanisms. Also, @@ERROR is raised only for errors, not for warnings; therefore, batches, stored procedures, and triggers do not have visibility to any warnings that may have occurred.

A common use of @@ERROR is to indicate the success or failure of a stored procedure. An integer variable is initialized to 0. After each Transact-SQL statement completes, @@ERROR is tested for being 0, and if it is not 0, it is stored in the variable. The procedure then returns the variable on the RETURN statement. If none of the Transact-SQL statements in the procedure had an error, the variable remains at 0. If one or more statements generated an error, the variable holds the last error number. This is a simple stored procedure with this logic:

/* Example from above omitted -- H10K */

There are situations when @@ERROR can be used with @@ROWCOUNT. In the following example, @@ERROR is used to determine if a constraint violation error occurred, and @@ROWCOUNT is used to determine the number of rows modified by the UPDATE statement, if any rows were successfully changed.

BEGIN TRAN
UPDATE Northwind.dbo.Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID IN (1, 2, 5, 6)

IF @@ERROR = 547
PRINT 'A CHECK CONSTRAINT violation occurred'
IF @@ROWCOUNT = 0
PRINT 'No rows updated.'
ELSE
PRINT STR(@@ROWCOUNT) + ' rows updated.'
COMMIT -- Commits rows successfully updated.
GO