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
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 to share
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 to share