Improve company productivity with a Business Account.Sign Up

x
?
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
?
49 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 50

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 50

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 50

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 50

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

588 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