SQL only a throw inside if statement

Question

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?

Solution 1

DECLARE @val NVARCHAR(50) = NULL
IF @val is null

    RAISERROR('Custom text', 16,16)

for different level check

http://msdn.microsoft.com/en-us/library/ms164086.aspx

Solution 2

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.

Solution 3

If this is for SQL Server, the intellisense syntax highlighter doesn't like it, but the code should compile and run fine. Of course, with it being a single statement, you don't need the BEGIN...END block at all:

IF (@val is null) THROW 50001, 'Custom text', 1

Comments

[Edmund G]

I still get "incorrect syntax near throw" when building with readyroll (it compiles and works correct in ssms.

[Edmund G]

Looks like the readyroll software does not like throw and gives an error when compiling but using RAISERROR works correctly.

[NReilingh]

RAISERROR should no longer be used in new code, since it will be discontinued in the future.

[Davos]

This should be the answer.

[joedotnot]

That syntax surely looks weird BEGIN; statements here END; Why is it OK to terminate the BEGIN !?

[NReilingh]

@joedotnot Blame Microsoft maybe? :-) Perhaps when the throw is the only statement, omitting BEGIN/END is preferable, and when some other statement is used before the throw, the semicolon after BEGIN is unnecessary.

[iceheaven31]

Similar to the WITH statement to create a Common Table Expression : the previous statement also needs to be terminated for WITH to work :P

[Triynko]

Apparently, it thinks END TRY BEGIN CATCH is a single statement. It won't allow a semicolon after END TRY, and displays an error "expecting END CATCH". But you can terminate the whole thing as END TRY BEGIN CATCH;

[Sql Programmer]

@NReilingh Its 2019 ending but still we are using RAISERROR