I have a set of udf's dealing that return a one column table of values
parsed from a comma delimeted string.
For example:
CREATE FUNCTION [dbo].[udf_filter_patient]
(
@patient_list varchar(2000)
)
RETURNS @patient TABLE
(
patient_id int
)
AS
BEGIN
insert into @patient
select patient_id from patient
-- parse @patient_list stuff excluded
RETURN
END
I have come up with the following two schemes to use these udfs. These
examples are obviously simplified, and I have a handful of stored procedures
that will use between 10 or more of these filters. If the two are
equivalent, I prefer Method 2 because it makes for much neater SQL when
using many filter criteria.
So my question is, will one noticebly outperform the other? Or is there a
better way in which to filter on a list of criteria?
Method 1 :
CREATE PROC sp__filter_open_bills
@patient_list varchar(2000)
AS
CREATE TABLE #patient
(
patient_id int
)
INSERT INTO #patient
SELECT
patient_id
FROM
dbo.udf_filter_patient( @patient_list )
SELECT
*
FROM
open_bills
INNER JOIN #patient on #patient.patient_id = open_bills.patient_id
GO
Method 2 :
CREATE PROC sp__filter_open_bills
@patient_list varchar(2000)
AS
SELECT
*
FROM
open_bills
WHERE
open_bills.patient_id IN ( SELECT patient_id FROM
dbo.udf_filter_patient( @patient_list ) )
GO
Thanks for the help!
Chris G