I am trying to stop execution of a SQL script when a condition is met and it is not working. At first I thought i could use RAISERROR, then it appears that only works when the moon is full and Trump says something intelligent. Then I tried RAISERROR with RETURN and that wasn't much more help..
So, it seems I should be using TRY/CATCH as RAISERROR will give differing results depending on severity levels. So, completely do not understand how to use this. I have looked up examples and tried, but each one i have tried the script does not stop executing..Per a suggestion from the web, it says to make your BEGIN statement, then BEGIN TRY and under that your condition and the END TRY and then BEGIN CATCH which it appears to reference what was in BEGIN TRY? And then END CATCH and then END.
But that doesn't seem to work, so would appreciate some help! Here is what i have that doesn't work based on trying to replicate what the web page said, though clearly I'm missing something..Note, the declarations are in the beginning of the script and so not going to declare them again..
BEGIN
BEGIN TRY
SET @NbrOfUsersGiven = (SELECT COUNT(*) FROM #USERSGIVEN)
IF @ROLE IN ('VR Champion', 'VR Project Lead', 'VR Business Analyst')
AND @NbrOfUsersGiven > 1
RAISERROR (N'Nothing Processed. You appear to have more than one user id for the role of
either VR Champion, VR Project Lead or Business Analyst', 16,1)
END TRY
BEGIN CATCH
RAISERROR (N'Nothing Processed. You appear to have more than one user id for the role of
either VR Champion, VR Project Lead or Business Analyst', 16,1)
END CATCH
END
I don't see anything in that script that would not or should not process - why do you think it isn't working?
In this code - if the @NbrOfUsersGiven is > 1 it raises an error...and then doesn't do anything else. If this is just a snippet and you have code following that - it will be executed because the severity level of the error you are raising does not stop execution.
IF (condition)
BEGIN
Print 'We are here';
return
END
< rest of code to be executed if condition is not met >
With that said - I always try to write my code so that it will branch to the code to be executed if the conditions are met instead of exiting.
IF @ROLE IN ('VR Champion', 'VR Project Lead', 'VR Business Analyst')
AND @NbrOfUsersGiven = 1
BEGIN
<code to handle true condition>
END
ELSE
BEGIN
<code to handle false condiation>
END
Yes, that is exactly what i want to know..what should i do to terminate the script at that point if the @NbrOfUsersGiven is > 1?
I have looked online and i think I have tried what is suggested, but clearly i am missing something as it is not working, my script does not cancel when @NbrOfUsersGiven is > 1. So, can you tell me what I need to put in the code that will cancel the script at that point in the execution of it?
Look at what I posted again - the answer is there...
If you need further assistance, then please post the full code as it is not clear where you expect the code to break/stop execution. Simply put...there is nothing in your code to be executed if your condition is not met...
This is essentially what you have:
IF (some condition)
{do something}
Where is the portion of the code that should be executed if (some condition) is not true? In this sample - there is no reason to break.
The raiserror - by itself - does not break out of the code unless it has a severity level that is high enough to terminate the connection. It will only raise the error to the client...
To stop execution you need to use RETURN.
To do that, it requires a BEGIN and END block so you can raise the error and issue the return as in my example(s).
To do this in a TRY/CATCH - you would not need a return, raising the error (with a severity that is high enough) the act of raising the error will branch to the catch block.
BEGIN TRY
RAISERROR('Error raised', 16, 1)
END
BEGIN CATCH
PRINT 'Transferred to catch'
END
Using TRY/CATCH - you then have the option of using THROW or even RAISERROR for a different error or you can re-throw the error.
OK, so here is the code, i don't want it to go any further than where i do the BEGIN..RAISERROR portion of the code, in other words, i do not want it to run/create the #USERS temp table if there is more than one record in #USERSGIVEN.
Note, i changed the code to what i originally had, i put the raiserror in there with a return, but that is not working..
Also, I am clearly not getting the idea behind this concept, i think i need this spelled out for me if you can..
declare @NbrOfUsersGiven as int
IF OBJECT_ID('tempdb..#USERSGIVEN') IS NOT NULL DROP TABLE #USERSGIVEN
SELECT ID_USERID
INTO #UsersGiven
FROM USERS
--if more than one user given for champion/lead/analyst kill program
SET @NbrOfUsersGiven = (SELECT COUNT(*) FROM #USERSGIVEN)
IF @ROLE IN ('VR Champion', 'VR Project Lead', 'VR Business Analyst')
AND @NbrOfUsersGiven > 1
BEGIN
RAISERROR (N'Nothing Processed. You appear to have more than one user id for the role of
either VR Champion, VR Project Lead or Business Analyst', 16,1)
RETURN --i want the code to end here if @NbrOfUsersGiven > 1
END
--THIS SHOULD NOT RUN IF @NbrOfUsersGiven > 1..
--grab users from user table..
IF OBJECT_ID('tempdb..#USERS') IS NOT NULL DROP TABLE #USERS
SELECT U.ID_UserID
INTO #USERS
FROM DIM_USERS AS U
JOIN #UsersGiven AS UG
ON U.ID_UserID = UG.ID_USERID
I put together a simple example and it works as expected:
Set Nocount On;
Declare @NbrOfUsersGiven int = 0
, @role varchar(30) = 'VR Champion';
Declare @users Table (ID_USERID int);
Insert Into @users Values (1), (2), (3), (4);
Declare @dim_users Table (ID_USERID int, UserName varchar(20));
Insert Into @dim_users Values (1, 'One'), (2, 'Two'), (3, 'Three'), (4, 'Four');
Drop Table If Exists #usersgiven;
Select u.ID_USERID
Into #usersgiven
From @users u;
Set @NbrOfUsersGiven = (Select count(*) From #usersgiven);
If @role In ('VR Champion', 'VR Project Lead', 'VR Business Analyst')
And @NbrOfUsersGiven > 1
Begin
Raiserror('Nothing Processed...', 16, 1) With nowait;
Return;
End
Raiserror('Processing users...', -1, -1) With nowait;
Drop Table If Exists #users;
Select du.ID_USERID
, du.UserName
Into #users
From @dim_users du
Join #usersgiven ug On ug.ID_USERID = du.ID_USERID;
Select *
From #users;
So, I reran the example per what i posted, and this time it worked. I must have done something different yesterday. Probably did something wrong the very first time when it should have worked - and then inadvertently corrected it, but changed the code thinking it wasn't working and whatever i changed it to (the try/catch) may not have been set up correctly..
Anyway thanks so much for your posts,I will keep in mind what you posted as well for future stuff. I am new to putting error trapping in MS SQL code and so am not aware of all the syntax and what is appropriate for what kind of error trapping..so if it doesn't work, I'm not sure how its supposed to look..and if i have made a small error, i go down rabbit holes that are completely unnecessary due to one small error in the first place..
Thanks again all for sticking with me with this..!
What has happened to you ..!! Happens to everybody ..
Especially me a lot ... ( i am sure there will be a lot of others )
Some things can help you in this ..
Like Before/During/After making changes .. Tracking your thoughts !! Write them down ..
Why you are doing .. what you are doing !!! your understanding ..
Tracking at various levels
.. all different kinds of tracking will make it very easy fro you to figure
anything out in case something like this happens !!!
Connecting the DOTS .. on paper ... people use diagrams == all sort of things
-- essentially the same things .. but people do in their own way !!
-- idea is the same
another thing is to practice 3 to 4 times before doing !!
all meta cognition stuff .. you can become expert of experts very quickly ..
the ceiling can be hit very very fast ..
