Stored Proc fails as Exec but the SQL that makes it up runs fine

  • 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

  • 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?

  • 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.

  • craig.bobchin - Wednesday, November 28, 2018 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.

    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.

  • ZZartin - Wednesday, November 28, 2018 11:42 AM

    craig.bobchin - Wednesday, November 28, 2018 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.

    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.

  • 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.

  • Steve Jones - SSC Editor - Wednesday, November 28, 2018 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.

    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.

  • 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.

  • Steve Jones - SSC Editor - Wednesday, November 28, 2018 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.

    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.

  • Steve Jones - SSC Editor - Wednesday, November 28, 2018 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.

    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?

  • 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.

  • 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.

  • 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 variable
    Declare   @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]

  • 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.

  • Glad you found it. I know that can be maddening.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply