Calling Throw within If block

Question

Post reply

Calling Throw within If block

Eugene-928407
Mr or Mrs. 500

Points: 572
More actions
May 30, 2012 at 7:47 am

#257226

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

Viewing 6 posts - 1 through 6 (of 6 total)

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

Answer 1

"The statement before the THROW statement must be followed by the semicolon (;) statement terminator."

Try putting a semicolon right before the THROW statement, like:

BEGIN CATCH

;THROW

END CATCH

_________________________________
seth delconte
http://sqlkeys.com

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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