"ianh" wrote:

> I am moving a database from SQL7 to SQL2000. One of the tables is
> structuredas follows :
>
> Create table TestEmployeeDetails(
> EmployeeNumber  varchar(20),
> EmployeeFirstName varchar(200),
> EmployeeLastName varchar(200)
> )
>
> (Don't ask me why the designer wanted 400 characters in total for a name,
> but I've left it at that for the moment to try and understand the problem
> I've been getting ...)
>
> Below is a stripped down version of a Stored Procedure that was also copied
> across from SQL 7
>
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER  PROCEDURE TestEmployee_prc
> (
>     @EmployeeFirstName VARCHAR(200),
>     @EmployeeLastName  VARCHAR(200)
> )
> AS
>
> BEGIN
>     SELECT
>         a.EmployeeNumber,
>         a.EmployeeFirstName,
>         a.EmployeeLastName
>     FROM
>         TestEmployeeDetails a
>     WHERE
>         a.EmployeeFirstName LIKE @EmployeeFirstName
>     AND
>         a.EmployeeLastName LIKE @EmployeeLastName
>     AND
>         a.EmployeeNumber > 0
> END
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> (Again - I realise that varchar(200) is ridiculously big for a first and
> last name, but let that go for the moment ...)
>
> If I now call the SP as follows, all is ok :
>
>    exec TestEmployee_prc 'Jo%', 'Smith'
>
> However, if I call it as follows, I get an error :
>
>    exec TestEmployee_prc '%', 'Smith'
>
>    Server: Msg 245, Level 16, State 1, Procedure TestEmployee_prc, Line 13
>    Syntax error converting the varchar value 'xxxx' to a column of data type
> int.
>
> The strange thing is, if I modify the declaration of the parameter as
> follows :
>
>    @EmployeeFirstName  varchar(100)
>
> Then miraculously it works fine.
>
> I'm able to resolve this easily, by simply modifying the parameter
> declaration - but I'm intrigued to know why this should solve the problem and
> why the problem should arise in the first place.
>
> Can anyone throw any light on this ?

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:6DD95965-C5CC-4C36-B465-B10ACF5BD1D9@microsoft.com...
> The problem is not with the first or last name. The problem is with the
> logical expression:
>
>> a.EmployeeNumber > 0
>
> because [EmployeeNumber] is varchar and in the expression you are
> comparing
> to an integer value and because integer datatype has higher precedence
> than
> varchar then the values from the column will be implicitly converted to
> integer. For sure there should be an EmployeeNumber that can not be
> converted
> to int where:
>
> ...
> where
>    a.EmployeeFirstName LIKE '%'
>    AND a.EmployeeLastName LIKE 'Smith'
>
> use isnumeric() function to detect that row, if is not enough, then use
> Aaron's function isReallyInteger().
>
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> select *
> from TestEmployeeDetails
> where
>    a.EmployeeFirstName LIKE '%'
>    AND a.EmployeeLastName LIKE 'Smith'
>    ans isReallyInteger(EmployeeNumber) = 0
> go
>
>
> AMB
>
> "ianh" wrote:
>
>> I am moving a database from SQL7 to SQL2000. One of the tables is
>> structuredas follows :
>>
>> Create table TestEmployeeDetails(
>> EmployeeNumber  varchar(20),
>> EmployeeFirstName varchar(200),
>> EmployeeLastName varchar(200)
>> )
>>
>> (Don't ask me why the designer wanted 400 characters in total for a name,
>> but I've left it at that for the moment to try and understand the problem
>> I've been getting ...)
>>
>> Below is a stripped down version of a Stored Procedure that was also
>> copied
>> across from SQL 7
>>
>>
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>>
>> ALTER  PROCEDURE TestEmployee_prc
>> (
>> @EmployeeFirstName VARCHAR(200),
>> @EmployeeLastName  VARCHAR(200)
>> )
>> AS
>>
>> BEGIN
>> SELECT
>> a.EmployeeNumber,
>> a.EmployeeFirstName,
>> a.EmployeeLastName
>> FROM
>> TestEmployeeDetails a
>> WHERE
>> a.EmployeeFirstName LIKE @EmployeeFirstName
>> AND
>> a.EmployeeLastName LIKE @EmployeeLastName
>> AND
>> a.EmployeeNumber > 0
>> END
>>
>> GO
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>>
>>
>> (Again - I realise that varchar(200) is ridiculously big for a first and
>> last name, but let that go for the moment ...)
>>
>> If I now call the SP as follows, all is ok :
>>
>>    exec TestEmployee_prc 'Jo%', 'Smith'
>>
>> However, if I call it as follows, I get an error :
>>
>>    exec TestEmployee_prc '%', 'Smith'
>>
>>    Server: Msg 245, Level 16, State 1, Procedure TestEmployee_prc, Line
>> 13
>>    Syntax error converting the varchar value 'xxxx' to a column of data
>> type
>> int.
>>
>> The strange thing is, if I modify the declaration of the parameter as
>> follows :
>>
>>    @EmployeeFirstName  varchar(100)
>>
>> Then miraculously it works fine.
>>
>> I'm able to resolve this easily, by simply modifying the parameter
>> declaration - but I'm intrigued to know why this should solve the problem
>> and
>> why the problem should arise in the first place.
>>
>> Can anyone throw any light on this ?

"Steve Kass" wrote:

> Alejandro,
>
> I agree with almost everything you said, except for this one bit:
>
> > integer. For sure there should be an EmployeeNumber that can not be
> > converted
> > to int where:
> >
> > ...
> > where
> >    a.EmployeeFirstName LIKE '%'
> >    AND a.EmployeeLastName LIKE 'Smith'
> >
>
> I think there is a row with EmployeeNumber = 'xxxx' that does *not*
> satisfy a.EmployeeLastName LIKE 'Smith', because if I understand
> correctly, the poster is saying that this call
>
> exec TestEmployee_prc '%', 'Smith'
>
> produces no error if the declared size of the varchar is changed.
> This suggests to me that the longer varchar length causes a
> different query plan to be used, which applies the filter on EmployeeNumber
> earlier.  When the declared length is 100, the row is excluded by the
> filter on LastName, avoiding the conversion error.
>
> In any case, your advice is fine, and since the actual non-convertible
> value is shown in the error ('xxxx'), the offending row can be found by
> searching for rows where the EmployeeNumber value is 'xxxx'.
>
> Steve Kass
> Drew University
>
> "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
> news:6DD95965-C5CC-4C36-B465-B10ACF5BD1D9@microsoft.com...
> > The problem is not with the first or last name. The problem is with the
> > logical expression:
> >
> >> a.EmployeeNumber > 0
> >
> > because [EmployeeNumber] is varchar and in the expression you are
> > comparing
> > to an integer value and because integer datatype has higher precedence
> > than
> > varchar then the values from the column will be implicitly converted to
> > integer. For sure there should be an EmployeeNumber that can not be
> > converted
> > to int where:
> >
> > ...
> > where
> >    a.EmployeeFirstName LIKE '%'
> >    AND a.EmployeeLastName LIKE 'Smith'
> >
> > use isnumeric() function to detect that row, if is not enough, then use
> > Aaron's function isReallyInteger().
> >
> > What is wrong with IsNumeric()?
> > http://www.aspfaq.com/show.asp?id=2390
> >
> > select *
> > from TestEmployeeDetails
> > where
> >    a.EmployeeFirstName LIKE '%'
> >    AND a.EmployeeLastName LIKE 'Smith'
> >    ans isReallyInteger(EmployeeNumber) = 0
> > go
> >
> >
> > AMB
> >
> > "ianh" wrote:
> >
> >> I am moving a database from SQL7 to SQL2000. One of the tables is
> >> structuredas follows :
> >>
> >> Create table TestEmployeeDetails(
> >> EmployeeNumber  varchar(20),
> >> EmployeeFirstName varchar(200),
> >> EmployeeLastName varchar(200)
> >> )
> >>
> >> (Don't ask me why the designer wanted 400 characters in total for a name,
> >> but I've left it at that for the moment to try and understand the problem
> >> I've been getting ...)
> >>
> >> Below is a stripped down version of a Stored Procedure that was also
> >> copied
> >> across from SQL 7
> >>
> >>
> >> SET QUOTED_IDENTIFIER OFF
> >> GO
> >> SET ANSI_NULLS ON
> >> GO
> >>
> >> ALTER  PROCEDURE TestEmployee_prc
> >> (
> >> @EmployeeFirstName VARCHAR(200),
> >> @EmployeeLastName  VARCHAR(200)
> >> )
> >> AS
> >>
> >> BEGIN
> >> SELECT
> >> a.EmployeeNumber,
> >> a.EmployeeFirstName,
> >> a.EmployeeLastName
> >> FROM
> >> TestEmployeeDetails a
> >> WHERE
> >> a.EmployeeFirstName LIKE @EmployeeFirstName
> >> AND
> >> a.EmployeeLastName LIKE @EmployeeLastName
> >> AND
> >> a.EmployeeNumber > 0
> >> END
> >>
> >> GO
> >> SET QUOTED_IDENTIFIER OFF
> >> GO
> >> SET ANSI_NULLS ON
> >> GO
> >>
> >>
> >> (Again - I realise that varchar(200) is ridiculously big for a first and
> >> last name, but let that go for the moment ...)
> >>
> >> If I now call the SP as follows, all is ok :
> >>
> >>    exec TestEmployee_prc 'Jo%', 'Smith'
> >>
> >> However, if I call it as follows, I get an error :
> >>
> >>    exec TestEmployee_prc '%', 'Smith'
> >>
> >>    Server: Msg 245, Level 16, State 1, Procedure TestEmployee_prc, Line
> >> 13
> >>    Syntax error converting the varchar value 'xxxx' to a column of data
> >> type
> >> int.
> >>
> >> The strange thing is, if I modify the declaration of the parameter as
> >> follows :
> >>
> >>    @EmployeeFirstName  varchar(100)
> >>
> >> Then miraculously it works fine.
> >>
> >> I'm able to resolve this easily, by simply modifying the parameter
> >> declaration - but I'm intrigued to know why this should solve the problem
> >> and
> >> why the problem should arise in the first place.
> >>
> >> Can anyone throw any light on this ?
>
>
>

"Steve Kass" wrote:

> Alejandro,
>
> I agree with almost everything you said, except for this one bit:
>
> > integer. For sure there should be an EmployeeNumber that can not be
> > converted
> > to int where:
> >
> > ...
> > where
> >    a.EmployeeFirstName LIKE '%'
> >    AND a.EmployeeLastName LIKE 'Smith'
> >
>
> I think there is a row with EmployeeNumber = 'xxxx' that does *not*
> satisfy a.EmployeeLastName LIKE 'Smith', because if I understand
> correctly, the poster is saying that this call
>
> exec TestEmployee_prc '%', 'Smith'
>
> produces no error if the declared size of the varchar is changed.
> This suggests to me that the longer varchar length causes a
> different query plan to be used, which applies the filter on EmployeeNumber
> earlier.  When the declared length is 100, the row is excluded by the
> filter on LastName, avoiding the conversion error.
>
> In any case, your advice is fine, and since the actual non-convertible
> value is shown in the error ('xxxx'), the offending row can be found by
> searching for rows where the EmployeeNumber value is 'xxxx'.
>
> Steve Kass
> Drew University
>
> "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
> news:6DD95965-C5CC-4C36-B465-B10ACF5BD1D9@microsoft.com...
> > The problem is not with the first or last name. The problem is with the
> > logical expression:
> >
> >> a.EmployeeNumber > 0
> >
> > because [EmployeeNumber] is varchar and in the expression you are
> > comparing
> > to an integer value and because integer datatype has higher precedence
> > than
> > varchar then the values from the column will be implicitly converted to
> > integer. For sure there should be an EmployeeNumber that can not be
> > converted
> > to int where:
> >
> > ...
> > where
> >    a.EmployeeFirstName LIKE '%'
> >    AND a.EmployeeLastName LIKE 'Smith'
> >
> > use isnumeric() function to detect that row, if is not enough, then use
> > Aaron's function isReallyInteger().
> >
> > What is wrong with IsNumeric()?
> > http://www.aspfaq.com/show.asp?id=2390
> >
> > select *
> > from TestEmployeeDetails
> > where
> >    a.EmployeeFirstName LIKE '%'
> >    AND a.EmployeeLastName LIKE 'Smith'
> >    ans isReallyInteger(EmployeeNumber) = 0
> > go
> >
> >
> > AMB
> >
> > "ianh" wrote:
> >
> >> I am moving a database from SQL7 to SQL2000. One of the tables is
> >> structuredas follows :
> >>
> >> Create table TestEmployeeDetails(
> >> EmployeeNumber  varchar(20),
> >> EmployeeFirstName varchar(200),
> >> EmployeeLastName varchar(200)
> >> )
> >>
> >> (Don't ask me why the designer wanted 400 characters in total for a name,
> >> but I've left it at that for the moment to try and understand the problem
> >> I've been getting ...)
> >>
> >> Below is a stripped down version of a Stored Procedure that was also
> >> copied
> >> across from SQL 7
> >>
> >>
> >> SET QUOTED_IDENTIFIER OFF
> >> GO
> >> SET ANSI_NULLS ON
> >> GO
> >>
> >> ALTER  PROCEDURE TestEmployee_prc
> >> (
> >> @EmployeeFirstName VARCHAR(200),
> >> @EmployeeLastName  VARCHAR(200)
> >> )
> >> AS
> >>
> >> BEGIN
> >> SELECT
> >> a.EmployeeNumber,
> >> a.EmployeeFirstName,
> >> a.EmployeeLastName
> >> FROM
> >> TestEmployeeDetails a
> >> WHERE
> >> a.EmployeeFirstName LIKE @EmployeeFirstName
> >> AND
> >> a.EmployeeLastName LIKE @EmployeeLastName
> >> AND
> >> a.EmployeeNumber > 0
> >> END
> >>
> >> GO
> >> SET QUOTED_IDENTIFIER OFF
> >> GO
> >> SET ANSI_NULLS ON
> >> GO
> >>
> >>
> >> (Again - I realise that varchar(200) is ridiculously big for a first and
> >> last name, but let that go for the moment ...)
> >>
> >> If I now call the SP as follows, all is ok :
> >>
> >>    exec TestEmployee_prc 'Jo%', 'Smith'
> >>
> >> However, if I call it as follows, I get an error :
> >>
> >>    exec TestEmployee_prc '%', 'Smith'
> >>
> >>    Server: Msg 245, Level 16, State 1, Procedure TestEmployee_prc, Line
> >> 13
> >>    Syntax error converting the varchar value 'xxxx' to a column of data
> >> type
> >> int.
> >>
> >> The strange thing is, if I modify the declaration of the parameter as
> >> follows :
> >>
> >>    @EmployeeFirstName  varchar(100)
> >>
> >> Then miraculously it works fine.
> >>
> >> I'm able to resolve this easily, by simply modifying the parameter
> >> declaration - but I'm intrigued to know why this should solve the problem
> >> and
> >> why the problem should arise in the first place.
> >>
> >> Can anyone throw any light on this ?
>
>
>