November 28, 2018 at 10:07 am
Hi All,
I have a stored proc that when I run the SQL hard coding the parameter values it runs fine and returns records as expected. However when I run it as
exec usp_ChangeFormReport
@CFType=-1,
@rg=11,
@FromDate='01/01/2017',
@ToDate='11/26/2018',
@rc='Receive',
@CreatedByUser=-1,
@AssignedToUser='-1',
@CFStatus='All',
@NewToPMS='A',
@execFromDate=NULL,
@execToDate=NULL,
@notifyMedPoint='All',
@requireTraining='All'
It fails with an error:
Msg 245, Level 16, State 1, Procedure usp_ChangeFormReport, Line 117 [Batch Start Line 0]
Conversion failed when converting the varchar value 'No' to data type int.
Here's the Proc with the same parameters hard coded. I'm at a loss to understand let alone explain why it's failing in one case, yet runs just fine as a straight SQL script with the exact same parameters. I can't see anywhere I'm trying to pass a varchar as an int, and none of the records returned show anything unexpected./****** Object: StoredProcedure [dbo].[usp_ChangeFormReport] Script Date: 11/26/2018 10:42:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--ALTER PROCEDURE [dbo].[usp_ChangeFormReport]
--(
Declare @CFType INT = -1
Declare @RG INT = 11
Declare @FromDate Varchar (30) = '01/01/2017'
Declare @ToDate Varchar (30) = '11/26/2018'
Declare @RC Varchar (10) = 'Receive'
Declare @CreatedByUser INT = -1
Declare @AssignedToUser Varchar (20) = -1
Declare @CFStatus Varchar (20) = 'All'
Declare @NewToPMS Char(1) = 'A'
Declare @execFromDate Varchar (30) = NULL
Declare @execToDate Varchar (30) = NULL
Declare @notifyMedPoint Varchar (3) = 'ALL'
Declare @requireTraining Varchar (3) = 'ALL'
--)
--AS
-- validate date parameters
If (isdate (@execFromDate) = 0)
set @execFromDate = NULL
If (isdate (@execToDate) = 0)
set @execToDate = NULL
IF isdate ( @FromDate ) = 0
OR isdate ( @ToDate ) = 0
OR cast ( @FromDate AS Datetime ) > cast ( @ToDate AS Datetime )
OR cast ( @execFromDate AS Datetime ) > cast ( @execToDate AS Datetime )
BEGIN
SELECT NULL AS changeformid,
NULL AS isRush,
NULL AS providerNotificationDate,
NULL AS executedDate,
NULL AS notifyMedPoint,
NULL AS doesProviderRequireTraining,
NULL AS displaycontractid,
NULL AS contractentity,
NULL AS changeformtype,
NULL AS changeformstatus,
'Date Parameter Error' AS networklocname,
NULL AS routinggroup,
NULL AS receivedfromrg,
NULL AS resolutiondate,
NULL AS contractID,
NULL AS taxID,
NULL AS tat,
NULL AS isBrandNewProv,
NULL AS effectiveDate,
NULL AS agreementType,
NULL AS createdByUserName
RETURN
END
--create temp table to capture change forms that are completed per department
IF object_id ( 'tempdb..#CFcompletedlist', 'U' ) IS NOT NULL
DROP TABLE #cfcompletedlist
CREATE TABLE #cfcompletedlist
(
changeformid Int,
isRush char(1),
providerNotificationDate varchar(10),
executedDate varchar(10),
notifyMedPoint char(1),
doesProviderRequireTraining char(1),
displaycontractid Varchar (10),
contractentity Varchar (100),
changeformtype Varchar (100),
changeformstatus Varchar (20),
networklocname Varchar (1000),
--resolutionname varchar(100),
routinggroup Varchar (100),
--routinggroupuser varchar(100),
receivedfromrg Varchar (100),
resolutiondate Varchar (10),
contractID int,
taxID varchar(20),
createdByUserName varchar(100)
)
IF object_id ( 'tempdb..#CFnewassignment', 'U' ) IS NOT NULL
DROP TABLE #cfnewassignment
CREATE TABLE #cfnewassignment
(
changeformid Int,
isRush char(1),
providerNotificationDate varchar(10),
executedDate varchar(10),
notifyMedPoint char(1),
doesProviderRequireTraining char(1),
displaycontractid Varchar (10),
contractentity Varchar (100),
changeformtype Varchar (100),
changeformstatus Varchar (20),
networklocname Varchar (1000),
--resolutionname varchar(100),
routinggroup Varchar (100),
--routinggroupuser varchar(100),
receivedfromrg Varchar (100),
resolutiondate Varchar (10),
contractID int,
taxID varchar(20),
createdByUserName varchar(100),
tat Int,
isBrandNewProv char(1),
-- SUK/Ticket#63828/2010-12-09: Added parameters below
effectiveDate DATETIME,
agreementType Varchar(100)
-- end
)
IF @rc = 'Receive'
BEGIN
INSERT INTO #cfcompletedlist
SELECT DISTINCT
cf.changeformid,
IsNull(cf.isRush, 'N') AS isRush,
CONVERT(varchar(10), cf.providerNotificationDate , 101) AS providerNotificationDate,
CONVERT(varchar(10), cf.executedDate , 101) AS executedDate,
IsNull(cf.notifyMedPoint, 'N') AS notifyMedPoint,
IsNull(cf.doesProviderRequireTraining, 'N') AS doesProviderRequireTraining,
dbo.fungetcontractdisplayid ( cf.contractid ) AS displaycontractid,
contractentity,
changeformtypename,
cf.changeformstatus,
dbo.fungetcontractnetworklocations ( cf.contractid ) AS networklocname,
--ResolutionName,
rg.routinggroupname AS routinggroup,
--tu.firstname+', '+tu.lastname AS RoutingGroupUser,
rg2.routinggroupname AS receivedfromrg,
CONVERT(varchar(10), cfr.resolutiontime, 101) AS resolutiondate,
cf.contractID,
co.taxID,
tu.firstname + ' ' + tu.lastname AS createdByUserName
FROM tblchangeform cf
LEFT JOIN tblchangeformresolution cfr
ON cf.changeformid = cfr.changeformid
And cfr.resolutiontime = ( Select max(resolutiontime)
From tblchangeformresolution
Where changeformid = cfr.changeformid
and resolutionValue = cfr.resolutionValue)
LEFT JOIN tblchangeformtype cft
ON cft.changeformtypeid = cf.changeformtypeid
LEFT JOIN tblroutinggroup rg
ON rg.routinggroupid = cfr.resolutionvalue
LEFT JOIN tblroutinggroup rg2
ON rg2.routinggroupid = cfr.resolvedbygroupid
LEFT JOIN tblresolution re
ON re.resolutionid = cfr.resolutionid
LEFT JOIN tblcontracts co
ON co.contractid = cf.contractid
LEFT JOIN tblUsers tu ON tu.userid=cf.createdByUserID
WHERE
(
cf.changeformstatus = @CFStatus
OR @CFStatus = 'All'
)
AND
(
IsNull(cf.notifyMedPoint, 'N') = @notifyMedPoint
OR @notifyMedPoint = 'All'
)
AND
(
(
@execFromDate IS NOT NULL AND
@execToDate IS NOT NULL AND
cf.executedDate BETWEEN cast ( @execFromDate AS Datetime )
AND cast ( @execToDate AS Datetime )
)
OR
(
@execFromDate IS NULL OR @execToDate IS NULL
)
)
AND
(
IsNull(cf.doesProviderRequireTraining, 'N') = @requireTraining
OR @requireTraining = 'All'
)
AND cfr.resolutionid IN ( 18, 25, 27 )
AND
(
cft.changeformtypeid = @CFType
OR @CFType = - 1
)
AND cfr.resolutionvalue = @rg
AND cfr.resolutiontime BETWEEN
cast ( @FromDate AS Datetime )
AND dateadd ( DAY, 1, cast ( @ToDate AS Datetime ))
GROUP BY
cf.changeformid,
cf.changeformstatus,
cf.isRush,
cf.providerNotificationDate,
cf.executedDate,
cf.notifyMedPoint,
cf.doesProviderRequireTraining,
co.contractentity,
cft.changeformtypename,
cf.contractid,
co.taxID,
rg.routinggroupname,
rg2.routinggroupname,
tu.firstname,
tu.lastname,
cfr.resolutiontime
ORDER BY cf.changeformid, cf.contractid
INSERT INTO #cfnewassignment
SELECT a.*, tat = '', b.isBrandNewProvider,
-- SUK/Ticket#63828/2010-12-09: Added fields below
co.EffectiveDate,
at.Name as agreementType
FROM #cfcompletedlist a
LEFT JOIN tblchangeform b
ON a.changeformid = b.changeformid
-- SUK/Ticket#63828/2010-12-09: Added join below
LEFT JOIN tblcontracts co
ON co.contractid = b.contractid
LEFT JOIN dbo.tblAgreementTypes at
ON co.AgreementTypeID = at.AgreementTypeID
WHERE
(
b.createdbyuserid = @CreatedByUser
OR @CreatedByUser = - 1
) --OR @CreatedByUser=-2)
AND
(
b.isBrandNewProvider = @NewToPMS
OR @NewToPMS = 'A'
)
ORDER BY a.changeformid
SELECT distinct *
FROM #cfnewassignment
WHERE
@AssignedToUser = '-1'
OR changeformid IN
(
SELECT DISTINCT a.changeformid --,resolutionvalue
FROM #cfnewassignment a
INNER JOIN tblchangeformresolution b
ON a.changeformid = b.changeformid
WHERE resolutionid = 26
AND resolutionvalue = @AssignedToUser
)
END
ELSE
IF @rc = 'Create'
BEGIN
INSERT INTO #cfcompletedlist
SELECT DISTINCT
cf.changeformid,
IsNull(cf.isRush, 'N') AS isRush,
CONVERT(varchar(10), cf.providerNotificationDate , 101) AS providerNotificationDate,
CONVERT(varchar(10), cf.executedDate , 101) AS executedDate,
IsNull(cf.notifyMedPoint, 'N') AS notifyMedPoint,
IsNull(cf.doesProviderRequireTraining, 'N') AS doesProviderRequireTraining,
dbo.fungetcontractdisplayid ( cf.contractid ) AS displaycontractid,
contractentity,
changeformtypename AS changeformtype,
cf.changeformstatus,
dbo.fungetcontractnetworklocations ( cf.contractid ) AS
networklocname,
--ResolutionName,
rg.routinggroupname AS routinggroup,
--tu.firstname+', '+tu.lastname AS RoutingGroupUser,
NULL AS receivedfromrg,
min ( convert ( Varchar (10), cfr.resolutiontime, 101 )) AS resolutiondate, --, TAT =''
cf.contractid,
co.taxID,
tu.firstname + ' ' + tu.lastname AS createdByUserName
FROM tblchangeform cf
LEFT JOIN tblchangeformresolution cfr
ON cf.changeformid = cfr.changeformid
And cfr.resolutiontime = ( Select max(resolutiontime)
From tblchangeformresolution
Where changeformid = cfr.changeformid
and resolutionValue = cfr.resolutionValue)
LEFT JOIN tblchangeformtype cft
ON cft.changeformtypeid = cf.changeformtypeid
LEFT JOIN tblroutinggroup rg
ON rg.routinggroupid = cfr.resolvedbygroupid
LEFT JOIN tblresolution re
ON re.resolutionid = cfr.resolutionid
LEFT JOIN tblcontracts co
ON co.contractid = cf.contractid
LEFT JOIN tblUsers tu ON tu.userid = cf.createdByUserID
WHERE
(
cf.changeformstatus = @CFStatus
OR @CFStatus = 'All'
)
AND
(
IsNull(cf.notifyMedPoint, 'N') = @notifyMedPoint
OR @notifyMedPoint = 'All'
)
AND
(
(
@execFromDate IS NOT NULL AND
@execToDate IS NOT NULL AND
cf.executedDate BETWEEN cast ( @execFromDate AS Datetime )
AND cast ( @execToDate AS Datetime )
)
OR
(
@execFromDate IS NULL OR @execToDate IS NULL
)
)
AND
(
IsNull(cf.doesProviderRequireTraining, 'N') = @requireTraining
OR @requireTraining = 'All'
)
AND cfr.resolutionid = 27
AND (cft.changeformtypeid = @CFType OR @CFType = - 1 )
AND cfr.resolvedbygroupid = @rg
AND cfr.resolutiontime BETWEEN cast ( @FromDate AS Datetime )
AND dateadd ( DAY, 1, cast ( @ToDate AS Datetime ))
GROUP BY
cf.changeformid,
cf.changeformstatus,
cf.isRush,
cf.providerNotificationDate,
cf.executedDate,
cf.notifyMedPoint,
cf.doesProviderRequireTraining,
contractentity,
changeformtypename,
cf.contractid,
co.taxID,
rg.routinggroupname,
tu.firstname,
tu.lastname,
cfr.resolutiontime
ORDER BY cf.changeformid, cf.contractid
SELECT distinct
a.*,
tat = '', b.isBrandNewProvider,
-- SUK/Ticket#63828/2010-12-09: Added fields below
co.EffectiveDate,
at.Name as agreementType
FROM #cfcompletedlist a
LEFT JOIN tblchangeform b
ON a.changeformid = b.changeformid
-- SUK/Ticket#63828/2010-12-09: Added join below
LEFT JOIN tblcontracts co
ON co.contractid = b.contractid
LEFT JOIN dbo.tblAgreementTypes at
ON co.AgreementTypeID = at.AgreementTypeID
WHERE
(
b.createdbyuserid = @CreatedByUser
OR @CreatedByUser = - 1
) --OR @CreatedByUser=-2)
AND
(
b.isBrandNewProvider = @NewToPMS
OR @NewToPMS = 'A'
)
ORDER BY a.changeformid
END
ELSE
-- completed
BEGIN
INSERT INTO #cfcompletedlist
SELECT DISTINCT
cf.changeformid,
IsNull(cf.isRush, 'N') AS isRush,
CONVERT(varchar(10), cf.providerNotificationDate , 101) AS providerNotificationDate,
CONVERT(varchar(10), cf.executedDate , 101) AS executedDate,
IsNull(cf.notifyMedPoint, 'N') AS notifyMedPoint,
IsNull(cf.doesProviderRequireTraining, 'N') AS doesProviderRequireTraining,
dbo.fungetcontractdisplayid ( cf.contractid ) AS displaycontractid,
contractentity,
changeformtypename,
cf.changeformstatus,
dbo.fungetcontractnetworklocations ( cf.contractid ) AS networklocname,
--ResolutionName,
rg.routinggroupname AS routinggroup,
--tu.firstname+', '+tu.lastname AS RoutingGroupUser,
NULL AS receivedfromrg,
max ( convert ( Varchar (10), cfr.resolutiontime, 101 )) AS resolutiondate,
cf.contractid,
co.taxID,
tu.firstname + ' ' + tu.lastname AS createdByUserName
FROM tblchangeform cf
LEFT JOIN tblchangeformresolution cfr
ON cf.changeformid = cfr.changeformid
And cfr.resolutiontime = ( Select max(resolutiontime)
From tblchangeformresolution
Where changeformid = cfr.changeformid
and (
(resolutionValue = cfr.resolutionValue)
OR
(cfr.resolutionValue is null and cfr.resolutionid = 28) --completed
))
LEFT JOIN tblchangeformtype cft
ON cft.changeformtypeid = cf.changeformtypeid
LEFT JOIN tblroutinggroup rg
ON rg.routinggroupid = cfr.resolvedbygroupid
LEFT JOIN tblresolution re
ON re.resolutionid = cfr.resolutionid
LEFT JOIN tblcontracts co
ON co.contractid = cf.contractid
LEFT JOIN tblUsers tu ON tu.userid = cf.createdByUserID
WHERE
(
cf.changeformstatus = @CFStatus
OR @CFStatus = 'All'
)
AND
(
IsNull(cf.notifyMedPoint, 'N') = @notifyMedPoint
OR @notifyMedPoint = 'All'
)
AND
(
(
@execFromDate IS NOT NULL AND
@execToDate IS NOT NULL AND
cf.executedDate BETWEEN cast ( @execFromDate AS Datetime )
AND cast ( @execToDate AS Datetime )
)
OR
(
@execFromDate IS NULL OR @execToDate IS NULL
)
)
AND
(
IsNull(cf.doesProviderRequireTraining, 'N') = @requireTraining
OR @requireTraining = 'All'
)
AND
(
cfr.resolutionid IN ( 25, 28, 29 )
OR
(
cfr.resolutionid = 18
AND cf.changeformstatus = 'Canceled'
)
)
AND
(
cft.changeformtypeid = @CFType
OR @CFType = - 1
)
AND cfr.resolvedbygroupid = @rg
AND cfr.resolutiontime BETWEEN cast ( @FromDate AS Datetime )
AND dateadd ( DAY, 1, cast ( @ToDate AS Datetime ))
GROUP BY
cf.changeformid,
cf.changeformstatus,
cf.isRush,
cf.providerNotificationDate,
cf.executedDate,
cf.notifyMedPoint,
cf.doesProviderRequireTraining,
contractentity,
changeformtypename,
cf.contractid,
co.taxId,
rg.routinggroupname,
tu.firstname,
tu.lastname,
cfr.resolutiontime
ORDER BY cf.changeformid, cf.contractid
IF object_id ( 'tempdb..#CFgroupbyRG', 'U' ) IS NOT NULL
DROP TABLE #cfgroupbyrg
CREATE TABLE #cfgroupbyrg
(
changeformid Int,
routinggroupname Varchar (100),
tat Int
)
INSERT INTO #cfgroupbyrg
SELECT
changeformid,
routinggroupname,
sum ( isnull ( tad, 0 )) AS tad
FROM tblchangeformresolution a
INNER JOIN tblroutinggroup b
ON a.resolvedbygroupid = b.routinggroupid
--Where a.resolutionid IN ( 25, 27, 18 )
GROUP BY changeformid, routinggroupname
INSERT INTO #cfnewassignment
SELECT
a.*,
b.tat,
c.isBrandNewProvider,
-- SUK/Ticket#63828/2010-12-09: Added fields below
co.EffectiveDate,
at.Name as agreementType
FROM #cfcompletedlist a
INNER JOIN #cfgroupbyrg b
ON a.changeformid = b.changeformid
AND b.routinggroupname = a.routinggroup
LEFT JOIN tblchangeform c
ON c.changeformid = a.changeformid
-- SUK/Ticket#63828/2010-12-09: Added join below
LEFT JOIN tblcontracts co
ON co.contractid = c.contractid
LEFT JOIN dbo.tblAgreementTypes at
ON co.AgreementTypeID = at.AgreementTypeID
WHERE
(
c.createdbyuserid = @CreatedByUser
OR @CreatedByUser = - 1
) -- OR @CreatedByUser=-2)
AND
(
c.isBrandNewProvider = @NewToPMS
OR @NewToPMS = 'A'
)
ORDER BY a.changeformid
SELECT distinct *
FROM #cfnewassignment
WHERE
@AssignedToUser = '-1'
OR changeformid IN
(
SELECT DISTINCT a.changeformid --,resolutionvalue
FROM #cfnewassignment a
INNER JOIN tblchangeformresolution b
ON a.changeformid = b.changeformid
WHERE resolutionid = 26
AND resolutionvalue = @AssignedToUser
)
END
November 28, 2018 at 10:28 am
I don't think you're passing in a varchar, but somewhere in your data there is an implicit conversion taking place, which gets a varchar ("No") and can't cast this to an int. Is there a column that contains No values? If so, where does this join with the proc?
Can you get the execution plans and compare them?
November 28, 2018 at 11:11 am
Steve, Thanks for the reply. There are a couple of columns that return a Y or N value, but none of them are part of the parameters in the proc and they are set as varchars in the temp tables in the proc.
The only data type conversions are from Varchar to Datetime noting to or from Int.
Regarding teh execution plans, I can only get the one for the actual SQL Code. the Exec statement crashes with the conversion error before returning the execution plan.
November 28, 2018 at 11:42 am
craig.bobchin - Wednesday, November 28, 2018 11:11 AMSteve, Thanks for the reply. There are a couple of columns that return a Y or N value, but none of them are part of the parameters in the proc and they are set as varchars in the temp tables in the proc.The only data type conversions are from Varchar to Datetime noting to or from Int.
Regarding teh execution plans, I can only get the one for the actual SQL Code. the Exec statement crashes with the conversion error before returning the execution plan.
Without going through that entire proc as it's rather long I would guess that when you change the input criteria from the defaults something that now gets included in the now different query results is causing that error.
November 28, 2018 at 12:06 pm
ZZartin - Wednesday, November 28, 2018 11:42 AMcraig.bobchin - Wednesday, November 28, 2018 11:11 AMSteve, Thanks for the reply. There are a couple of columns that return a Y or N value, but none of them are part of the parameters in the proc and they are set as varchars in the temp tables in the proc.The only data type conversions are from Varchar to Datetime noting to or from Int.
Regarding teh execution plans, I can only get the one for the actual SQL Code. the Exec statement crashes with the conversion error before returning the execution plan.
Without going through that entire proc as it's rather long I would guess that when you change the input criteria from the defaults something that now gets included in the now different query results is causing that error.
I know the proc is rather long, but I'm using the exact same parameters in both cases, and there are no defaults set in the proc. So there should be no difference in the execution.
November 28, 2018 at 12:10 pm
I don't think this is parameters. Looking there likely is a distraction. I suspect there's a data error somewhere in a table, and I'm guessing that's where the issue is. This doesn't seem like a "bit" error, but rather an explicit string that's being converted in the code. It's a lot of code to go through. What I'd start to try and do is break this down, looking for a place where there are "no" values.
November 28, 2018 at 12:22 pm
Steve Jones - SSC Editor - Wednesday, November 28, 2018 12:10 PMI don't think this is parameters. Looking there likely is a distraction. I suspect there's a data error somewhere in a table, and I'm guessing that's where the issue is. This doesn't seem like a "bit" error, but rather an explicit string that's being converted in the code. It's a lot of code to go through. What I'd start to try and do is break this down, looking for a place where there are "no" values.
My first thought was a data issue and if the proc failed when run a straight SQL script, I'd have a better idea of where the data is bad. But it doesn't fail and the data that is returned is exactly as I expect it to be. It only fails when I pass the exact same parameters in the EXEC statement. That's what's killing me. The exec fails before I can get a SQL trace or execution plan.
November 28, 2018 at 1:32 pm
Something seems odd. Are you sure you have the same version of the proc code?
Maybe try this:
- Script the proc out
- change to a new name (_2)
- Before you create, copy the SET valuse you have above into the proc to override the parameters passed in. That way you know you're running these.
- Have someone double check the work and verify this is a matching set of param values.
Does this run with other values? I still think data, but I also suspect something is out of date here.
November 28, 2018 at 2:18 pm
Steve Jones - SSC Editor - Wednesday, November 28, 2018 1:32 PMSomething seems odd. Are you sure you have the same version of the proc code?Maybe try this:
- Script the proc out
- change to a new name (_2)
- Before you create, copy the SET valuse you have above into the proc to override the parameters passed in. That way you know you're running these.
- Have someone double check the work and verify this is a matching set of param values.Does this run with other values? I still think data, but I also suspect something is out of date here.
I'm positive it's the exact same code from the exact same database and server with the exact same parameters.. I'll give your suggestion a try and report back.
November 28, 2018 at 2:30 pm
Steve Jones - SSC Editor - Wednesday, November 28, 2018 1:32 PMSomething seems odd. Are you sure you have the same version of the proc code?Maybe try this:
- Script the proc out
- change to a new name (_2)
- Before you create, copy the SET valuse you have above into the proc to override the parameters passed in. That way you know you're running these.
- Have someone double check the work and verify this is a matching set of param values.Does this run with other values? I still think data, but I also suspect something is out of date here.
Same damn thing!
Msg 245, Level 16, State 1, Procedure usp_ChangeFormReport_2, Line 134 [Batch Start Line 0]
Conversion failed when converting the varchar value 'No' to data type int.
It behaves the same way regardless of what data I use as parameters. Plain SQL works, exec fails.
If it's data related, why does the SQL version not fail?
November 29, 2018 at 7:31 am
Two dates are being passed in as varchar and cast to datetime. Is it possible that the default language/region setting is different when executing the stored procedure with parameters as opposed to running the t-SQL directly? If so, you will end up using a different date range and therefore pulling in a different set of rows - at least one of those rows would be the culprit.
November 29, 2018 at 9:20 am
I have no idea why proc fails and sql works. That's why I asked to check versions. I have had versions seem to rev underneath me. Building a second proc as well, perhaps to catch something.
It's a lot of code, and my apologies that I don't have the time to sift through what it could be. That's certainly a strange "looks like data" error, but I don't quite know where to point you.
I'll post on #sqlhelp.
November 29, 2018 at 9:47 am
I didn't notice this in the chain so far but if I missed it sorry for the repeat. But you have this:
Passed in to SP@AssignedToUser='-1',
Defined variableDeclare @AssignedToUser Varchar (20) = -1
It shouldn't matter since the -1 should be converted to varchar, but I'd still check just in case.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 29, 2018 at 10:16 am
Thank you everyone for your help. I figured out the issue. As @SteveJones and I suspected it is a data issue. one of the joins in there is trying to join an INT to a varchar. An easy fix.
What really threw me is that is is working on a production server that is based on SQL 2000. We're in the midst of an upgrade to 2016 and I'm guessing that the different compilers/optimizers are responsible for it not working in some cases and working in others.
There are a few minor issues in the proc that I'm fixing as well, Datetime being passed as varchar etc...
So again thanks.
November 29, 2018 at 10:23 am
Glad you found it. I know that can be maddening.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply