Menu
I am adding some validation to a couple of stored procedures and need to check if some of the variables are not null (they are populated earlier in the stored procedure).
I have been trying to add a "throw" inside an if statement like below:
IF (@val is null) BEGIN THROW 50001, 'Custom text', 1 END This causes a syntax error on the "throw" as it is looking for other code inside the if statement prior to the throw but I only need it to perform the throw inside the if statement.
I need to keep the stored procedure as light as possible to keep it as fast as possible to execute.
Does anyone have any ideas?
291
Yes, PL/SQL allows us to nest if statements within if-then statements. i.e, we can place an if then statement inside another if then statement. if (condition1) then -- Executes when condition1 is true if (condition2) then -- Executes when condition2 is true end if; end if; SQL.
SQL Server CASE statement is equivalent to the IF-THEN statement in Excel. The CASE statement is used to implement the logic where you want to set the value of one column depending upon the values in other columns. The SQL Server CASE Statement consists of at least one pair of WHEN and THEN statements.
The following example shows how to use the THROW statement to raise the last thrown exception again. USE tempdb; GO CREATE TABLE dbo. TestRethrow ( ID INT PRIMARY KEY ); BEGIN TRY INSERT dbo. TestRethrow(ID) VALUES(1); -- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
New applications should use THROW instead of RAISERROR. Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.
The syntax error is showing up because the previous statement hasn't been terminated. The other answers will work, but in order to do it this way you can either throw a semicolon right before the THROW, or get in the habit of terminating all statements with semicolons.
IF (@val is null) BEGIN ;THROW 50001, 'Custom text', 1 END or
IF (@val is null) BEGIN; THROW 50001, 'Custom text', 1; END; You may have noticed that:
IF (@val is null) THROW 50001, 'Custom text', 1 ... will also work, and this is because SQL Server knows that the next thing to come after an IF statement is always a new T-SQL statement.
It is perhaps worth noting that Microsoft has stated that the T-SQL language in the future will require semicolons after each statement, so my recommendation would be to start building the habit now.
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
