troubleshooting Question

Conversion failed when converting the varchar value '2,4,6' to data type int on Multi-Select Stored Procedure

Avatar of danielolorenz
danielolorenz asked on
Microsoft SQL Server
15 Comments6 Solutions71 ViewsLast Modified:
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 INT
AS
BEGIN

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

SQL Function that returns multi-select list:
USE [BRAC_Portal]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_ParseString2Table]    Script Date: 8/8/2016 1:48:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[ufn_ParseString2Table] (@String VARCHAR(8000), @Delimiter VARCHAR(10) = ',')
/************************************************************************
PROCEDURE 	uf_ManageSecurityFlag

	ARGUMENTS	
				
	RETURNS		Table in table variable

	DESCRIPTION RETRIEVES CURRENT USER INFO

	AUTHOR	DATE		DESCRIPTION
	DOL		08/8/2016	Parses Delimited String to Table

	EXAMPLE
	SELECT CONVERT(INT,ITEM) FROM dbo.ufn_ParseString2Table ('39, 549, 324, 3556, 24, 2132, 345 ,', ',')
************************************************************************/
RETURNS @ReturnTable TABLE(Item VARCHAR(100))
AS
BEGIN

	WHILE CHARINDEX(@Delimiter,@String)>0
	BEGIN
		INSERT @ReturnTable
		SELECT SUBSTRING(@String,1,(CHARINDEX(@Delimiter,@String)-1))
		SELECT @String=RIGHT(@String,DATALENGTH(@String)-CHARINDEX(@Delimiter,@String))
 
	END
	INSERT @ReturnTable SELECT @String
	
	RETURN 
END

Thanks,

Dan
ASKER CERTIFIED SOLUTION
danielolorenz

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 6 Answers and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 6 Answers and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros