CODEWITHSUNDEEP
sql-serverfunctiont-sqlexceptionreturn
Joel Jacobson
Joel Jacobson

Reputation: 145

T-SQL Function to Throw error

Hello there I have a Function and is introduction a variable which will bring a letter or a number, if the variable is a letter it need to cause an error and return a 0, or if is a number then return 1.

SO in T-SQL I have a Procedure that eventually will call this function to check is is a number:

IF  dbo.VALIDNUMBER(@sTxpX) != 0 AND @sTxpX IS NOT NULL

The variable @sTxpX is holding a value which is 'T' so I know it needs to return 0 from the function because is invalid to be a numeric, but Im not getting the proper function to build it, I will appreciate some help here.

CREATE FUNCTION DBO.VALIDNUMBER (@sTextStr VARCHAR(4000)) RETURNS  BIT AS
 BEGIN
DECLARE @bValidNumberStr BIT = 1; DECLARE @nTest BIGINT;

    SET @nTest = CAST(@sTextStr AS numeric(38, 10)) + 1;
    RETURN @bValidNumberStr;

EXCEPTION
    WHEN OTHERS THEN
        SET @bValidNumberStr = 0;
    RETURN @bValidNumberStr;
END;

Upvotes: 2

Views: 833

Answers (2)

rgorr
rgorr

Reputation: 380

Try this function:

CREATE function [dbo].[VALIDNUMBER]
(@strText VARCHAR(4000))
RETURNS BIT
AS
BEGIN
DECLARE @Return as bit
IF TRY_CAST(@strText AS NUMERIC(38,10)) IS NOT NULL BEGIN
    SET @Return = 1
END ELSE BEGIN
    SET @Return = 0
END

RETURN @Return

END

Upvotes: 3

Eli
Eli

Reputation: 2608

Why can't you use the built-in SQL function? It's faster, and no need for you to drive yourself crazy to come up with a solution.

In your procedure do the following: 

DECLARE @isNumber bit;

IF (ISNUMERIC(@sTextStr) = 1) 
    BEGIN 
        SET @isNumber = 1
    END
ELSE
    BEGIN
        SET @isNumber = 0

        RAISERROR(15600, 16, 20, 'That was not a number')
    END

You can pass the @isNumber variable back to the user at a later point in time.

Upvotes: 2

Related Questions