Replacing a frequently used complex test in SQL Server Query with a function

I have a test that I must regularly use in requests to see if the CPT billing code is a billable appointment. In the SQL query, the test looks like this:

where (pvp.code between '99201' and '99215'
    or pvp.code between '99221' and '99239')

      

plus a whole bunch of more ranges.

I tried to create a function by simply including that function in a function, but I got a "wrong syntax" error, not a huge surprise.

CREATE FUNCTION IsEncounter 
(
    @code varchar(20)
)
RETURNS bit
AS
BEGIN
    DECLARE @Result bit;

    SELECT @Result = @code between '99201' and '99215'
            or @code between '99221' and '99239';

    -- Return the result of the function
    RETURN @Result;

END

      

I suppose I can do something like

if (@code >= '99201' and @code <= '99215')
        or (@code >= '99221' and @code <= '99239')
    select @Result = 1
else
    select @Result = 0;

      

but I would like to know the cleanest way to do this. Thank.

+3


source to share


3 answers


Something like this should work:



CREATE FUNCTION IsEncounter 
(
   @code varchar(20)
)
RETURNS bit
AS
BEGIN
    DECLARE @Result bit

    SET @Result = CASE WHEN (@code between '99201' and '99215') or (@code between '99221' and '99239') THEN 1 ELSE 0 END

    RETURN @Result
END

      

+4


source


I think you are almost right with your UDF. I think there are only a few syntax problems. Try the following:

CREATE FUNCTION IsEncounter 
(
    @code varchar(20)
)
RETURNS bit
AS
BEGIN
    DECLARE @Result bit = 0

    IF @code between '99201' and '99215' or @code between '99221' and '99239'
    BEGIN
        SET @Result = 1
    END

    RETURN @Result

END

      



Then your WHERE clause would be simple:

where dbo.IsEncounter(p.Code)=1

      

+3


source


I moved my ranges into a table and then I do this:

CREATE FUNCTION IsEncounter ( @code VARCHAR(20) )
RETURNS BIT
AS 
    BEGIN 
        DECLARE @MatchedRanges SMALLINT ;
        DECLARE @Result BIT ;

        SELECT  @MatchedRanges = COUNT(*)
        FROM    tblRangeWhiteList
        WHERE   @code BETWEEN RangeStart AND RangeEnd

        IF @MatchedRanges > 0 
            SET @Result = 1
        ELSE 
            SET @Result = 0

        RETURN @Result 

    END 

      

+1


source







All Articles