Parsing Character String

I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.



Here are the function and the stored procedure wher eit occurs:




CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))

RETURNS @Results TABLE (Item nvarchar(4000))

AS



BEGIN

DECLARE @INDEX INT

DECLARE @SLICE nvarchar(4000)

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z

-- ERO FIRST TIME IN LOOP

SELECT @INDEX = 1

WHILE @INDEX !=0



BEGIN

-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE

IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)

ELSE

SELECT @SLICE = @STRING

-- PUT THE ITEM INTO THE RESULTS SET

INSERT INTO @Results(Item) VALUES(@SLICE)

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

-- BREAK OUT IF WE ARE DONE

IF LEN(@STRING) = 0 BREAK

END

RETURN

END

Procedure:


Code SnippetCREATE PROCEDURE [dbo].[RPTPatientAnalysis]

(

@stateList CHAR(2),

@employerIdList VARCHAR(4000),

@payerIdList VARCHAR(4000)

)

AS

SELECT

p.PAT_ID,

p.PAT_FirstName,

ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName,

p.PAT_LastName,

p.PAT_Gender,

CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB,

p.PAT_AddressStreet1,

ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2,

p.PAT_AddressCity,

p.PAT_AddressStateProvince,

p.PAT_AddressPostalCode,

ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress,

p.PAT_PhoneNumber,

ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name,

ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,

ISNULL(p.PAT_Comment,'') AS PAT_Comment,

ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID,

ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification,

ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName,

ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail,

ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission,

p.PAT_LastEandMCodingDateTime,

p.PAT_Desceased,

p.PAT_PCP_ID,

p.PAT_LastUpdatedDateTime,

ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType,

ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing,

ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal,

ISNULL(p.PAT_PortalID,0) AS PAT_PortalID,

ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name,

ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,

pcp.PRIMCP_ID,

ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID,

ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName,

ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname,

pcp.PRIMCP_DateDeactivated,

ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax,

ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice,

ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1,

ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2,

ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City,

ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State,

ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip,

ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1,

ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2,

ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City,

ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State,

ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip,

ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax,

pati.PATINS_InsuranceTypeID,

ISNULL(pati.PATINS_Account,'') AS PATINS_Account,

ISNULL(pati.PATINS_Group,'') AS PATINS_Group,

ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType,

ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount,

ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount,

ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays,

ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay,

ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay,

ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar,

ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan,

ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description,

ISNULL(ip.INSP_Name,'') AS INSP_Name,

ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice,

ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies,

CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate,

ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive,

CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate,

CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate


FROM dbo.patient AS p

LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID

LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID

LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID

LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1

LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID

LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID

WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,','))

AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,','))

AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,','))


Is there a faster / more efficient way to accomplish the above?

Any insight would be appreciated!!