Conversion failed when converting the varchar value '2,4,6' to data type int on Multi-Select Stored Procedure
I am getting an error when passing in a multi-select string consisting of multiple parameters.
The multi-select values are being passed in through @LOB_KEY AS VARCHAR(100)
and used here: AND LOB_KEY IN (dbo.ufn_ParseString2Table(@LOB_KEY,',')) -- (@LOB_KEY)
(See Below)
The stored procedure:
ALTER PROCEDURE [dbo].[sp_LossRunSummaryClaimantList] @SECURITY_ID AS VARCHAR(50), @ACCOUNT_NBR AS VARCHAR(40), @UNDERWRITING_PROGRAM_NBR AS VARCHAR(50), @CLAIM_STATUS AS VARCHAR(50), @LOB_KEY AS VARCHAR(100), @POLICY_YR AS INT --@POLICY_YR AS INTASBEGIN-- EXEC [dbo].[sp_LMCClaimListAll5Year] 'S-1-5-21-3783409357-2204173064-303048338-183906', '0000011003', '0011' IF EXISTS (SELECT * FROM dbo.PORTAL_USER u JOIN dbo.PORTAL_USER_PERMISSION up ON up.USER_KEY = u.USER_KEY WHERE u.SECURITY_ID = @SECURITY_ID AND up.ACCOUNT_NBR IN (@ACCOUNT_NBR,'ALL') AND up.UNDERWRITING_PROGRAM_NBR = @UNDERWRITING_PROGRAM_NBR) BEGIN SELECT LineOfBusiness = LOB_NM, PolicyYear = POLICY_YR, ClaimStatus = CLAIM_LAST_STATUS, ClaimNumber = ISNULL(CLAIM_LEGACY_NBR, CLAIM_NBR), ClaimantNumber = CLAIMANT_NBR, ClaimantName = CLAIMANT_NM, LossDate = LOSS_DT, Paid = SUM(GROSS_LOSS_PAID + GROSS_EXPENSE_PAID) + SUM(WC_GROSS_PAID) , Reserves = SUM(LOSS_RESERVE + EXPENSE_RESERVE) + SUM(WC_RESERVE) , TotalIncurredAmount = SUM(GROSS_LOSS_PAID + GROSS_EXPENSE_PAID + LOSS_RESERVE + EXPENSE_RESERVE + SUBROGATION + SALVAGE + DEDUCTIBLE_RECOVERY + RECOVERIES_OTHER) + SUM(WC_GROSS_PAID + WC_RESERVE + WC_RECOVERIES), Recoveries = SUM(SUBROGATION + SALVAGE + DEDUCTIBLE_RECOVERY + RECOVERIES_OTHER) + SUM(WC_RECOVERIES) FROM dbo.AGG_MEMBER_CENTER WHERE ACCOUNT_NBR = @ACCOUNT_NBR AND UNDERWRITING_PROGRAM_NBR = @UNDERWRITING_PROGRAM_NBR AND LAST_STATUS IN (@CLAIM_STATUS) AND LOB_KEY IN (dbo.ufn_ParseString2Table(@LOB_KEY,',')) -- (@LOB_KEY) AND POLICY_YR IN (@POLICY_YR) --(dbo.ufn_ParseString2Table(@POLICY_YR,',')) -- (@POLICY_YR) GROUP BY LOB_NM, POLICY_YR, ISNULL(CLAIM_LEGACY_NBR, CLAIM_NBR), CLAIM_LAST_STATUS, CLAIMANT_NBR, CLAIMANT_NM, LOSS_DT ORDER BY 1 ASC , 2 DESC, 4 DESC; END