Implicit data conversion. SQL 7 vs SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Implicit data conversion. SQL 7 vs SQL 2000

Author

Topic

mvander
Starting Member

4 Posts

Posted - 2002-03-14 : 13:05:38

On a SQL 7 database the following stored procedure was defined.

CREATE PROCEDURE LoadPOs @ParentTable nvarchar(30), @ParentID int AS
If @ParentID <= 0
Begin
SELECT * FROM PO
End
Else
Begin
If @ParentTable = 'PO'
SELECT * FROM PO WHERE PO = @ParentID
If @ParentTable = 'Vender'
SELECT * FROM PO WHERE Vender_ID = @ParentID
End
GO

In the PO table, the Vender_ID field is an int and PO is nvarchar(20). Everything works fine in SQL 7.
When the database is moved to SQL 2000 and the the sp is executed looking for a PO as below.

execute dbo.loadpos 'PO',1

Then the following error happens.
Server: Msg 245, Level 16, State 1, Procedure LoadPOs, Line 9
Syntax error converting the nvarchar value '000053-1' to a column of data type int.

Is there some installation parameter that I am missing that explains the difference in behavior between SQL 7 and 2000?

mvander
Starting Member

4 Posts

Posted - 2002-03-14 : 15:38:28

It appears that SQL 7 must ignore data conversions it can't do. In the PO table, in the PO column there is non-numeric data. On SQL 2000, it does not ignore such things.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-14 : 15:54:59

Try this:

CREATE PROCEDURE LoadPOs @ParentTable nvarchar(30), @ParentID int AS
If @ParentID <= 0
Begin
SELECT * FROM PO
End
Else
Begin
If @ParentTable = 'PO'
SELECT * FROM PO WHERE PO = cast(@ParentID as varchar(20)
If @ParentTable = 'Vender'
SELECT * FROM PO WHERE Vender_ID = @ParentID
End
GO

Actually, if '000053-1' is the value of PO in your table, why would you try to compare an int to it, knowing you will never have a '-' in an int value?

Is there a PO_ID in your table?

Jeremy

Edited by - joldham on 03/14/2002 15:58:54

mvander
Starting Member

4 Posts

Posted - 2002-03-14 : 16:53:01

Jeremy,
I agree, the sp is poorly designed. You shouldn't have an int parameter looking at a column that holds non-numeric data. The recommendation I had to the developer was to change the @ParentID parameter to nvchar.
The thing is SQL 7 behaves differently than SQL 2000. So, switching databases from SQL 7 to SQL 2000 isn't a no-brainer. Lots of testing needs to happen. And what other things are checked better or differently on SQL 2000? I haven't found any documentation that states all these differences.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 17:00:56

If you look in Books Online under "compatibility issues", you'll find just about every difference between 7.0 and 2000 listed. Some of the links have tables that display the old behavior and new behavior.

Edited by - robvolk on 03/14/2002 17:01:15

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources