SQL Server Using Function

Question

I have a very simple query that looks at a large table of locations and returns details about specific street. I am trying to get high - low numbers to populate another single row and have elected to use a FUNCTION to do this

CREATE FUNCTION [dbo].[GetMaxStrNo]
    (@StrFullName varchar) 
RETURNS INT
AS
BEGIN
    RETURN 
        (SELECT  
             MAX(CAST(apt_no AS INT)) 
         FROM
             location 
         WHERE
             location_name = @StrFullName 
             AND ISNUMERIC(apt_no) = 1)
END
GO

Try as I might the results come back as NULL when called from my main procedure

SET @MaxStrNo = dbo.GetMaxStrNo (@StrFullName)

Any help would be most gratefully appreciated.

Many thanks

Might be you are passing the value which doesn't exist in the table — Sankar
– Sankar, Commented Feb 21, 2017 at 14:37
StrFullName varchar is StrFullName varchar(1), so probably you don't have location_name in the database equal to the first letter of StrFullName — Denis Reznik
– Denis Reznik, Commented Feb 21, 2017 at 14:47

Gordon Linoff · Accepted Answer · 2017-02-21 14:45:07Z

1

The first obvious problem is the declaration varchar(). In SQL Server, this should always have a length:

CREATE FUNCTION [dbo].[GetMaxStrNo](
    @StrFullName varchar(max)
) RETURNS INT

The rest of your function has another problem. The isnumeric() in the where clause will not prevent an error. Use try_convert() to prevent an error:

BEGIN
    RETURN (SELECT MAX(TRY_CONVERT(int, apt_no)) 
            FROM location 
            WHERE location_name = @StrFullName and ISNUMERIC(apt_no) = 1
           );
END;

The isnumeric() in the where clause is strictly optional in this case.

answered Feb 21, 2017 at 14:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheBigChap Over a year ago

Many Thanks Gordon ... Worked first time ... All those ohours, need to get an expert or at least rewad a book :) Cheers

Collectives™ on Stack Overflow

SQL Server Using Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related