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

Open in new window


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

Open in new window


Thanks,

Dan
danielolorenzAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
danielolorenzConnect With a Mentor Author Commented:
My solution worked.  The assist was correct
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You need to check your stored procedure call. seems like thats where you are getting error
0
 
danielolorenzAuthor Commented:
My Stored Procedure call:

USE [BRAC_Portal]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_LossRunSummary]
		@SECURITY_ID = N'S-1-5-21-3783409357-2204173064-303048338-41641',
		@ACCOUNT_NBR = N'0000134023',
		@UNDERWRITING_PROGRAM_NBR = N'0013',
		@CLAIM_STATUS = N'All',
		@LOB_KEY = N'6, 2',
		@POLICY_YR = 2015,
		@INCURRED_AMOUNT = 0,
		@BEFORE_DATE = N'8/9/2016'

SELECT	'Return Value' = @return_value

GO

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
danielolorenzAuthor Commented:
I am passing multi-select values on the stored procedure call through: @LOB_KEY = N'6, 2',
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
those are twi different sps, are you calling the firtst one sp_LossRunSummaryClaimantList  inside the above sp ?
0
 
PortletPaulConnect With a Mentor Commented:
see: line 29 of the first code block of the question

                  AND LOB_KEY IN (dbo.ufn_ParseString2Table(@LOB_KEY,',')) -- (@LOB_KEY)

Your function does not alter the data type, it just places each value between the delimiter into a table

so N'6, 2',

becomes a VARCHAR COLUMN
'6'
' 2'

assuming [LOB_KEY] is an integer
you are therefore IMPLICITLY CONVERTING the result of the function into integers so that they can be compared

perhaps try removing any leading spaces by including LTRIM() into your function
0
 
danielolorenzConnect With a Mentor Author Commented:
I am looking into a function call solution right now: AND LOB_KEY IN (SELECT CONVERT(INT,ITEM) FROM dbo.ufn_ParseString2Table (@LOB_KEY, ','))
0
 
danielolorenzAuthor Commented:
This is a way to convert the results into an int
0
 
danielolorenzAuthor Commented:
My solution is working
0
 
PortletPaulConnect With a Mentor Commented:
You still have the potential to fail.

Is TRY_CONVERT() avsilable in your mssql version?
0
 
PortletPaulConnect With a Mentor Commented:
For example if the parameter was

N ('6, TWO')

convert ('TWO',int) will fail

But try_convert() would not fail but it would return a null. So you should also ensure null isnt retrned by your function
0
 
PortletPaulConnect With a Mentor Commented:
One other thing. While you can send that function an NVARCHAR parameter value you are only retruning VARCHAR. So that might also lead to error if you really need NVARCHAR in the result.
0
 
danielolorenzAuthor Commented:
Thanks
0
 
danielolorenzAuthor Commented:
Thanks for your help
0
 
danielolorenzAuthor Commented:
All of your input was excellent
0
All Courses

From novice to tech pro — start learning today.