USING LEN To test for data validation

Question

Using a stored procedure that receives an input parameter for the Vendor state. I'm trying to cause a custom error to be thrown when the state VARCHAR value is more than 2 chars in length. I'm using the LEN function to count the parameter length and to compare the value passed to the integer 2. Logically this looks right but the error is not being thrown. Any help?

USE AP
GO
CREATE PROC TEST123
@state VARCHAR(2) = NULL
AS
IF LEN(@state) <= 2
    SELECT TOP 1 VendorName
    FROM Vendors
    WHERE VendorState = @state;
    
ELSE
    THROW 50001, 'Invalid state length', 1;
GO
BEGIN TRY
USE AP
EXEC TEST123 @state = 'CAA';
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH

user_9513040 · Answer

Use RAISERROR in block of else statement and THROW in the catch block.

begin try
    -- your procedure starts
    if condition
        begin
            statement
        end
    else
        begin
            RAISERROR(#, #, '')
        end;
   -- your procedure ends
end try
begin catch
    print(ERROR_MESSAGE())
    throw;
end catch;

Structure of RAISERROR from MS SQL Server docs:

RAISERROR ( { msg_id | msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]

USING LEN To test for data validation

1 Answers