Scalar SQL function for checking if input value is integer number or not. If not then throw error. Help me on this.

CodePudding user response：

Please check this in SQL I believe it works for you.

DECLARE @Value NVARCHAR(100) = '1.5'
IF ISNUMERIC(@Value) = 0
BEGIN 
    RAISERROR('Value must be integer or decimal',16,1)
END
ELSE
BEGIN
    SELECT @Value
END

Result

1. Integer

2. Decimal

3. String -> Error occurred in string.

Just add the above logic into your script it's work.

Note: In this, you can handle decimals and integers.

Please check this updated for you

CREATE FUNCTION [dbo].[Numeric_validation] 
(
    @P_InputValues NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
BEGIN
    DECLARE @Value NVARCHAR(MAX) = NULL
    IF @P_InputValues LIKE '%[^0-9]%'
    BEGIN 
        RETURN CAST('Value must be integer' AS INT);
    END
    ELSE
    BEGIN
        SET @Value = @P_InputValues
    END
RETURN   @Value;
END
SELECT [dbo].[Numeric_validation] ('10')
SELECT [dbo].[Numeric_validation] ('10.50')
SELECT [dbo].[Numeric_validation] ('TEST')

CodePudding user response：

the fucntion will check if input value is integer or not ..if not then it will throw error. ALTER FUNCTION [dbo].[Numeric_validation] ( @P_InputValues NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) BEGIN

RETURN NULL; END GO i have to put the logic within this