@@ERROR problems

Question

@@ERROR problems

rgrios

Old Hand

Points: 334
More actions
November 20, 2003 at 2:08 pm

#107009

I am trying to work with @@ERROR and am having problems with a simple statement <below>
-- Begin Statement
declare @error_val int
select * from sysbutt where name ='mast'
select @error_val = @@error
--set @error_val = (@@error)
if @error_val <> 0
print 'Please select the correct table'
go
-- End Statement
When I run the statement is show that the table is not correct but does not send the print statement (error code)I am looking for. Any Ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

cmore SSCrazy Points: 2452 More actions · Answer 1

@@Error will only contain a value if your select statement actually generates a SQL error, like if there is no field named 'name'.

I think what you are wanting to do would be accomplished by @@Rowcount

philcart SSC-Forever Points: 47795 More actions · Answer 2

In your example, you won't get the output from your print statement because the error occurs in the parse and compile of the statement. So the @@ERROR variable isn't set.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

The error is too severe to allow the batch to continue, so you are erroring out of the process before your error handling statements. Perhaps you could use something like this:



IF OBJECTPROPERTY(OBJECT_ID('sysbutt'), 'istable') IS NULL

PRINT 'Please select the correct table'

ELSE

SELECT *

FROM sysbutt

WHERE Name ='mast'

--Jonathan

rgrios Old Hand Points: 334 More actions · Answer 4

Thank you for your post. Is there a way to get the parse errors also?

ThomasH SSCertifiable Points: 7437 More actions · Answer 5



/*

Two Things:

1. Exec ([dynamicSQL]) is not a "script stopping" error if it fails.

2. Adding "WHERE 1=2" causes the query plan to only return the result's "structure"

   thus no table "data" is physically read, giving very fast results even if the

   statement is correct and many rows would normally be returned.

*/

Set NoCount On

Declare @SQL VarChar(8000)



Select @SQL = 'Select * from BadTable'

Exec ('Select * Into #Temp From (' + @SQL + ') A where 1=2')

Print 'Error:' + Convert(Varchar(10), @@Error)



Select @SQL = 'Select * from SysObjects where BadColName = 1'

Exec ('Select * Into #Temp From (' + @SQL + ') A where 1=2')

Print 'Error:' + Convert(Varchar(10), @@Error)



Select @SQL = 'Select * from SysObjects where Name like ''x%'' '

Exec ('Select * Into #Temp From (' + @SQL + ') A where 1=2')

Print 'Error:' + Convert(Varchar(10), @@Error)



Print 'Script gets to here...'

Select * from BadTable

Print 'Error:' + Convert(Varchar(10), @@Error)

Print 'Script does NOT get to here...'

Once you understand the BITs, all the pieces come together

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

Soory, seems the above post does not want to Cut & Paste Nicely so...

/*

Two Things:

1. Exec ([dynamicSQL]) is not a "script stopping" error if it fails.

2. Adding "WHERE 1=2" causes the query plan to only return the result's "structure"

thus no table "data" is physically read, giving very fast results even if the

statement is correct and many rows would normally be returned.

*/

Set NoCount On

Declare @sql VarChar(8000)

Select @sql = 'Select * from BadTable'

Exec ('Select * Into #Temp From (' + @sql + ') A where 1=2')

Print 'Error:' + Convert(Varchar(10), @@Error)

Select @sql = 'Select * from SysObjects where BadColName = 1'

Exec ('Select * Into #Temp From (' + @sql + ') A where 1=2')

Print 'Error:' + Convert(Varchar(10), @@Error)

Select @sql = 'Select * from SysObjects where Name like ''x%'' '

Exec ('Select * Into #Temp From (' + @sql + ') A where 1=2')

Print 'Error:' + Convert(Varchar(10), @@Error)

Print 'Script gets to here...'

Select * from BadTable

Print 'Error:' + Convert(Varchar(10), @@Error)

Print 'Script does NOT get to here...'

Once you understand the BITs, all the pieces come together

Alamir SSCertifiable Points: 6085 More actions · Answer 7

there is no importance to use @@ERROR in Select statement

it is good to use @@ERROR in any updata , insert and delete statements

here is a good way to do this by making a transaction

Note:

- @Err is an output that will indate an error happenes (like key vaulation or deplicate key when trying to insert in PK field)

and @Err will have the sum of errors the returns ... and if @Err = 0 , so there is no errors

---------------

ALTER Procedure Anything

@Err integer output

As

Begin transaction

set @Err = 0 -- no errors

/*update statement here .. so .. */

set @Err = @Err + @@Error

/*Select statement here .. so no statement here */

if @Err = 0

commit transaction

else

Rollback transaction

return

Alamir Mohamed

Alamir_mohamed@yahoo.com

Alamir Mohamed
Alamir_mohamed@yahoo.com