Passing value in a function without quote T-SQL / SQL Server 2012?

Question

I need assistance with a function in SQL Server 2012 that I created to check for the input value. If the functions detects a numeric - return 0, if detect character return 1.

But I get 2 different result for the same number passing it with quote and without quote.

select dbo.IS_ALIEN('56789')

returns 0

select dbo.IS_ALIEN(56789)

returns 1 (I need to return 0)

This is my function:

ALTER FUNCTION [dbo].[IS_ALIEN] 
    (@alienNAIC CHAR(1))
RETURNS NUMERIC(10,0) 
AS
BEGIN
    DECLARE @nNum NUMERIC(1,0);

    BEGIN
        SET @NnUM = ISNUMERIC(@alienNAIC)
    END
    BEGIN
        IF @nNum = 1 
            RETURN 0;
        END

        RETURN 1;
    END

Same concept for:

select  dbo.IS_ALIEN('AA-11990043')

returns 1

or

select  dbo.IS_ALIEN(NULL)

returns 1 (I need it to return 0)

I'm using Oracle function reference (below code is just reference from old database):

create or replace FUNCTION "IS_ALIEN" 
  (  alienNAIC IN char )
  RETURN  NUMBER IS
  nNum number;
BEGIN
    SELECT MOD(alienNAIC, 2) into nNum FROM dual;
    return 0;
EXCEPTION
WHEN INVALID_NUMBER THEN
    return 1;
END;

But T-SQL function doesn't allow make exception of error. So I try to converted as much closer.

Answer 1

I suggest you use something like this (I've trimmed it down somewhat):

ALTER FUNCTION [dbo].[IS_ALIEN](@alienNAIC NVARCHAR(10))
RETURNS INT -- NOTE: You could also return tinyint or bit
AS
BEGIN
    IF ISNUMERIC(@alienNAIC) = 1 
        RETURN 0;

    RETURN 1;
END

The trouble with what you were trying is that there's an implicit cast to CHAR(1), the result of which is definitely not numeric as @Joel pointed out:

SELECT CAST(0 As CHAR(1)) -- returns character '0', ISNUMERIC(0) = 1
SELECT CAST(9 As CHAR(1)) -- returns character '9', ISNUMERIC(0) = 1
SELECT CAST(12345 As CHAR(1)) -- any number over 9 returns character '*', ISNUMERIC(12345) = 0

It's an odd implicit casting case I hadn't seen before. By making the parameter an NVARCHAR (assumes possible future double-byte input), strings will be correctly checked and integers passed in will be implicitly cast as NVARCHAR, and the ISNUMERIC check will succeed.

EDIT Re-reading the question and comments, it looks like you want to identify a particular string syntax to determine if something is an "alien" or not. If you're comfortable changing business logic to fix what apparently is a poor legacy implementation, you could consider something like this instead:

ALTER FUNCTION [dbo].[temp](@alienNAIC NVARCHAR(10))
RETURNS INT -- NOTE: You could also return tinyint or bit
AS
BEGIN
    IF @alienNAIC like 'AA-%' AND ISNUMERIC(RIGHT(@alienNAIC, LEN(@alienNAIC) - 3)) = 1
        RETURN 1; -- notice this is now 1 instead of 0, we're doing a specific check for 'AA-nnnnn...'

    RETURN 0;
END

Note that this should be thoroughly tested against legacy data if it's ever to interact with it - you never know what rubbish data a poorly written legacy system has left behind. Fixing this could well break other things. If you do make this change, document it well.