May 30, 2012 at 7:47 am
Hi All,
I thought I'd try using throw instead of raiseerror in my application, to try to remove the dependency on sys.messages. However I'm having problems with the syntax.
I'm trying to throw the error from within and "if" block. However the only way I've been able to get it to work is with the following fairly clumsy looking syntax. I'm sure there must be a better way...
if not (@EffectiveAccess & [dbo].[fnc_access_write]()) = [dbo].[fnc_access_write]()
begin
begin try
THROW 50001,N'The current user does not have permission to access this resource.',1;
end try
begin catch
throw
end catch
return 0;
end
If I put the throw statement directly within the "begin ... end" of the "if" block then I get an error...
"Incorrect syntax near 'THROW'. Expecting CONVERSATION, DIALOG, DISTRIBUTED, or TRANSACTION."
This suggests that it's trying to combine it with the "begin" statement.
What I want to do is throw the error within a stored procedure, so that I can catch it in my application code. The above works, but it just seems like a bit of a clunky way to have to do it.
I haven't been able to find any samples where "throw" has been used this way, so I'm not sure how it's actually supposed to be done.
Thanks,
Eugene
May 30, 2012 at 7:54 am
Try putting a semicolon right before the THROW statement, like:
BEGIN CATCH
;THROW
END CATCH
_________________________________
seth delconte
http://sqlkeys.com
May 30, 2012 at 8:02 am
Here's an example without a TRY...CATCH block:
IF 1=1
BEGIN
;THROW 51000, 'The record does not exist.', 1;
END
_________________________________
seth delconte
http://sqlkeys.com
May 30, 2012 at 8:15 am
Using the semicolon has done the trick. Thanks for that. I hadn't actually seen that kind of syntax previously. I figured I needed to terminate the previous statement, but I wasn't sure how to do it.
I've changed the code to the following and it seems to work fine...
if not (@EffectiveAccess & [dbo].[fnc_access_write]()) = [dbo].[fnc_access_write]()
begin
;THROW 50001,N'The current user does not have permission to access this resource.',1;
return 0;
end
Thanks,
Eugene
June 7, 2012 at 10:00 am
Eugene-928407 (5/30/2012)
Using the semicolon has done the trick. Thanks for that. I hadn't actually seen that kind of syntax previously. I figured I needed to terminate the previous statement, but I wasn't sure how to do it.I've changed the code to the following and it seems to work fine...
if not (@EffectiveAccess & [dbo].[fnc_access_write]()) = [dbo].[fnc_access_write]()begin
;THROW 50001,N'The current user does not have permission to access this resource.',1;
return 0;
end
Thanks,
Eugene
You were right in that the syntax requires the preceding line to be terminated with a semi-colon. The way it is presented implies the line containing THROW must lead with a semi-colon. T-SQL does not currently require all lines to be terminated with a semi-colon, but there are rumors that one day it will. Some statements, e.g. MERGE and CTEs (begin with WITH), and now THROW, require the preceding line be terminated with a semi-colon. The fact that many people like to write those with a leading semi-colon is protection from any line preceding the statement being added to the code and not getting that trailing semi-colon. It is good practice to start using semi-colon to terminate all lines.
IF NOT (@EffectiveAccess & [dbo].[fnc_access_write]()) = [dbo].[fnc_access_write]()
BEGIN;
THROW 50001, N'The current user does not have permission to access this resource.', 1;
RETURN 0;
END;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2012 at 10:06 am
You were right in that the syntax requires the preceding line to be terminated with a semi-colon. The way it is presented implies the line containing THROW must lead with a semi-colon. T-SQL does not currently require all lines to be terminated with a semi-colon, but there are rumors that one day it will. Some statements, e.g. MERGE and CTEs (begin with WITH), and now THROW, require the preceding line be terminated with a semi-colon. The fact that many people like to write those with a leading semi-colon is protection from any line preceding the statement being added to the code and not getting that trailing semi-colon. It is good practice to start using semi-colon to terminate all lines.
IF NOT (@EffectiveAccess & [dbo].[fnc_access_write]()) = [dbo].[fnc_access_write]()BEGIN;
THROW 50001, N'The current user does not have permission to access this resource.', 1;
RETURN 0;
END;
Good explanation. Thanks.
_________________________________
seth delconte
http://sqlkeys.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply