?
Solved

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

Posted on 2016-08-10
15
Medium Priority
?
43 Views
Last Modified: 2016-08-17
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
Comment
Question by:danielolorenz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 2
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 41751091
You need to check your stored procedure call. seems like thats where you are getting error
0
 

Author Comment

by:danielolorenz
ID: 41751096
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
 

Author Comment

by:danielolorenz
ID: 41751099
I am passing multi-select values on the stored procedure call through: @LOB_KEY = N'6, 2',
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 41751107
those are twi different sps, are you calling the firtst one sp_LossRunSummaryClaimantList  inside the above sp ?
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 2000 total points
ID: 41751125
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
 

Assisted Solution

by:danielolorenz
danielolorenz earned 0 total points
ID: 41751132
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
 

Author Comment

by:danielolorenz
ID: 41751134
This is a way to convert the results into an int
0
 

Author Comment

by:danielolorenz
ID: 41751138
My solution is working
0
 

Accepted Solution

by:
danielolorenz earned 0 total points
ID: 41751146
My solution worked.  The assist was correct
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 2000 total points
ID: 41751154
You still have the potential to fail.

Is TRY_CONVERT() avsilable in your mssql version?
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 2000 total points
ID: 41751160
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 2000 total points
ID: 41751190
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
 

Author Comment

by:danielolorenz
ID: 41752246
Thanks
0
 

Author Comment

by:danielolorenz
ID: 41754107
Thanks for your help
0
 

Author Closing Comment

by:danielolorenz
ID: 41759074
All of your input was excellent
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question