I need some help with a MS SQL Query

Hello,

I am trying to create a stored procedure with the code below and I am getting the same error no matter what I do to the comparison of the MR.AgencyId, an int field and i.invUserLevel4 that is a varchar field and has some NULL values that need not to be compared.

Error:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'NULL' to data type int.

Code:

use txu

SELECT
MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount,
CASE WHEN ISNULL(SUM(NewApps),0) = 0 THEN 0 ELSE CONVERT(int,(100 * (ISNULL(CONVERT(decimal(5,2), SUM(SigPadCount)), 0)/CONVERT(decimal(5,2), SUM(NewApps))))) END UsagePercentage,
COUNT(i.invSerialNo) as PadsOnHand
FROM
(
SELECT D.AgencyId, AgencyName, MarketingRepId, UAV.FullName
FROM txu.dbo.AgencyData AS D WITH (NOLOCK)
INNER JOIN txu.dbo.Agencies As A WITH (NOLOCK) ON A.AgencyDataId = D.AgencyDataId
INNER JOIN txu.dbo.UserAccountsView AS UAV WITH (NOLOCK) ON UAV.UserId = D.MarketingRepId
) AS MR
INNER JOIN fn_SigPadInfoByAgent('01/01/2011','01/10/2011') AS sp
      ON sp.AgencyId = MR.AgencyId
INNER JOIN NewSPDB.dbo.tblInventory AS i ON MR.AgencyId = convert(int,i.invUserLevel4)
where i.invUserLevel4 is not null
GROUP BY MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount
richcfmlAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>the varchar value 'NULL'
means that you have a field, that is VARCHAR, with the string value 'NULL' ... which is not NULL.
please double-check the data ...
0
 
Rajkumar GsSoftware EngineerCommented:
This error may due to the NULL value for any of the SigPadCount.
Try change your CASE statement including ISNULL check

SELECT
MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount,
CASE WHEN ISNULL(SUM(ISNULL(NewApps,0)), 0) = 0 THEN 0 ELSE CONVERT(int,(100 * (ISNULL(CONVERT(decimal(5,2), SUM(ISNULL(SigPadCount, 0))), 0)/CONVERT(decimal(5,2), SUM(ISNULL(NewApps, 0)))))) END 
UsagePercentage,
COUNT(i.invSerialNo) as PadsOnHand
FROM
(
SELECT D.AgencyId, AgencyName, MarketingRepId, UAV.FullName 
FROM txu.dbo.AgencyData AS D WITH (NOLOCK) 
INNER JOIN txu.dbo.Agencies As A WITH (NOLOCK) ON A.AgencyDataId = D.AgencyDataId
INNER JOIN txu.dbo.UserAccountsView AS UAV WITH (NOLOCK) ON UAV.UserId = D.MarketingRepId
) AS MR
INNER JOIN fn_SigPadInfoByAgent('01/01/2011','01/10/2011') AS sp
      ON sp.AgencyId = MR.AgencyId
INNER JOIN NewSPDB.dbo.tblInventory AS i ON MR.AgencyId = convert(int,i.invUserLevel4)
where i.invUserLevel4 is not null
GROUP BY MR.AgencyId, AgencyName, FullName, PremiumsWrittenAmount, NewApps, SigPadCount 

Open in new window


Raj
0
 
SharathConnect With a Mentor Data EngineerCommented:
I think you have non-numerical characters in invUserLevel4 column. Run this query and check such values.
select invUserLevel4 from NewSPDB.dbo.tblInventory where ISNUMERIC(invUserLevel4) = 0

Open in new window

0
 
richcfmlAuthor Commented:
Thank you everyone for your help. AS it turns out the value of the field invUserLevel4 was set to the string NULL. Corrected the data and everything is working now.

Again thank you very much for the help and prompt response.
0
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.

All Courses

From novice to tech pro — start learning today.