CODEWITHSUNDEEP
sql-serverexception
LearnByReading
LearnByReading

Reputation: 1883

Exception Handling in SQL Server: Reject non-integer input

I'm trying to write a SQL commands within a stored procedure that allows me to take a VARCHAR input, evaluate to see whether it is an integer. If it is not, then it has to set a boolean value to false: So I want to write the following logic: 

IF @parameter <> an int
    SET isInt = FALSE

I read the TRY/CATCH article but I just don't know how to do it in SQL: https://msdn.microsoft.com/en-ca/library/ms175976.aspx

Upvotes: 0

Views: 216

Answers (2)

dean
dean

Reputation: 10098

If it's' 2012 or 2014, use the TRY_PARSE() function:

https://msdn.microsoft.com/en-us/library/hh213126.aspx

With earlier versions, you have to actualy try to convert it and handle the error:

declare @s varchar(10) = 'abc'
declare @isInt bit = 1

begin try
    declare @i int;
    set @i = @s
end try
begin catch
    set @isInt = 0
end catch

select @isInt

Upvotes: 2

Sachin
Sachin

Reputation: 40970

You can use ISNUMERIC function to test whether the input is numeric or not.

SELECT ISNUMERIC(@parameter) as output

As numeric value can also be bigint,smallint, decimal etc. So to compare only Integer you can try something like this (credit to this blog)

SELECT IsNumeric(@parameter + '.0e0') as integerOutput

Upvotes: 2

Related Questions