[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45
  • Last Modified:

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
0
danielolorenz
Asked:
danielolorenz
  • 9
  • 4
  • 2
6 Solutions
 
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
 
danielolorenzAuthor Commented:
I am passing multi-select values on the stored procedure call through: @LOB_KEY = N'6, 2',
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Aneesh RetnakaranDatabase AdministratorCommented:
those are twi different sps, are you calling the firtst one sp_LossRunSummaryClaimantList  inside the above sp ?
0
 
PortletPaulCommented:
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
 
danielolorenzAuthor 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
 
danielolorenzAuthor Commented:
My solution worked.  The assist was correct
0
 
PortletPaulCommented:
You still have the potential to fail.

Is TRY_CONVERT() avsilable in your mssql version?
0
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 9
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now