Error handling issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Error handling issue

I have two issue in error handling.first is that I am writing insert statement and would like to handle the error in case insert fail and my insert statement & error code is as below Insert into mytable select column1,column2 from anothetable if @@error<>0 begin RAISERROR(‘Unable to insert’,16,1) Return -1 END Now i have issue when my "select" statement has any invalid column name then my @@error code is not getting fired.Please provide your inputs /********************************************************************************/
Second issue is related to dropping table and handling error. I have SP inside which i have wrote below code create procedure test
as
declare @sqlstring varchar(255) CREATE TABLE ##tmpnew (Column_a float, Column_b varchar(2)) set @sqlstring=’insert into ##tmpnew select column1,column2 from newtable’ exec (@sqlstring) if @@error<>0 begin RAISERROR(‘Unable to executing sql string’,16,1) Return -1 END Drop table ##tmpnew In above case if error occured while executing sql string then my table is not getting dropped and if someone execute above sp once again then SQL will display error message that "object already present". How to fix the issue. Adding drop table in error code is good option. Please provide your inputs
SQL Server MVP Erland Sommarskog has written two frequently referenced articles about error handling. You can find them here:http://www.sommarskog.se. HTH

Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

I think this looks like repetitive questionhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9541 thread. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>