asked on SQL Server error on select
Is there a way to handle a error on my select and write to a table if so?
For instance
If Select 4/0 errors (and it will)
Insert into myTable (col1)
values ('Error on select statement')
Or if I have an error on a HUGE select statement do the same thing.
For instance
If Select 4/0 errors (and it will)
Insert into myTable (col1)
values ('Error on select statement')
Or if I have an error on a HUGE select statement do the same thing.
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft Development
Last Comment
Jim Horn
IF EXISTS (SELECT * FROM TableName)
/* select works */
ELSE
/* select failed */
ASKER
Something strange here
Running the Select statement below only returns a error
Howerev...running that all together returns TWO record sets? I only want the one dataset.
Either the results...or the error
BEGIN TRY
SELECT CAST('<root><MissingNote>6
END TRY
BEGIN CATCH
SELECT 1
END CATCH ;
Running the Select statement below only returns a error
Howerev...running that all together returns TWO record sets? I only want the one dataset.
Either the results...or the error
BEGIN TRY
SELECT CAST('<root><MissingNote>6
END TRY
BEGIN CATCH
SELECT 1
END CATCH ;
To build on the link that tim_cs provided and everyone's TRY..CATCH comment
BEGIN TRY
SELECT 4/0
SELECT 'if this displays then error handling didnt work'
END TRY
BEGIN CATCH
Insert into myTable (no, severity, state, procedure, line, message)
SELECT ERROR_NUMBER(),ERROR_SEVER
END CATCH
BEGIN TRY
SELECT 4/0
SELECT 'if this displays then error handling didnt work'
END TRY
BEGIN CATCH
Insert into myTable (no, severity, state, procedure, line, message)
SELECT ERROR_NUMBER(),ERROR_SEVER
END CATCH
what are you running the SQL command in?
if I run just
SELECT CAST('<root><MissingNote>6
it in a management studio window
I get an error and an empty result
if I run it with try catch, I get the empty results, no error message and whatever my catch does.
if I run just
SELECT CAST('<root><MissingNote>6
it in a management studio window
I get an error and an empty result
if I run it with try catch, I get the empty results, no error message and whatever my catch does.
ASKER
guys...
There are supposed to be two result sets?
No way to just return the select OR the error...not both?
There are supposed to be two result sets?
No way to just return the select OR the error...not both?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
The RAISE_ERROR function causes the statement that invokes the function to return an error with the specified SQLSTATE (along with SQLCODE -438) and diagnostic string.
>>-RAISE_ERROR--(--sqlstat
SELECT EMPNO,
CASE WHEN EDLEVEL < 16 THEN 'Diploma'
WHEN EDLEVEL < 18 THEN ’Graduate’
WHEN EDLEVEL < 21 THEN ’Post Graduate’
ELSE RAISE_ERROR( '07001',
'EDLEVEL has a value greater than 20' )
END
FROM EMPLOYEE
or as mentioned jimhorn you can go for this....
BEGIN TRY
SELECT 4/0
END TRY
BEGIN CATCH
Insert into ERROR_TABLE(err_num,err_se
SELECT * FROM ERROR_TABLE
END CATCH
>>-RAISE_ERROR--(--sqlstat
SELECT EMPNO,
CASE WHEN EDLEVEL < 16 THEN 'Diploma'
WHEN EDLEVEL < 18 THEN ’Graduate’
WHEN EDLEVEL < 21 THEN ’Post Graduate’
ELSE RAISE_ERROR( '07001',
'EDLEVEL has a value greater than 20' )
END
FROM EMPLOYEE
or as mentioned jimhorn you can go for this....
BEGIN TRY
SELECT 4/0
END TRY
BEGIN CATCH
Insert into ERROR_TABLE(err_num,err_se
SELECT * FROM ERROR_TABLE
END CATCH
ASKER
It's looking like jimhorns answer is best and just handle it in other ways.
Will award points shortly without anyones strenous objections
Will award points shortly without anyones strenous objections
ASKER
THanks
Thanks for the grade. Good luck with your project. -Jim
Select 4/0
END TRY
BEGIN CATCH
print 'yeah it went wrong'
END CATCH