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
32 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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 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 48

Assisted Solution

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

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

Assisted Solution

by:PortletPaul
PortletPaul earned 500 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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

789 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