March 14, 2002 at 11:06 am
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?
March 14, 2002 at 12:44 pm
If you break this down SQl is trying to do this implicitly.
quote:
Syntax error converting the nvarchar value '000053-1' to a column of data type int.
However like in C++, SQL will assume the simplest data type which is int here so therefore try to convert to int. If however @ParentID was declared nvarchar(30) it would not have thrown an error either way. Also I have never seen this work differently in SQL 7 as opposed to SQL 2k (I feel pretty sure SQL was built in C++ is the reason why it automatically adopted this). Are you sure this worked as is under SQL 7 and that the code is not altered in any way.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 14, 2002 at 1:07 pm
Yes, I'm sure the stored procedure works in SQL 7. I tested it this morning.
And you are correct, if I change the 2nd parameter to the procedure to nvarchar, then the procedure works fine.
March 14, 2002 at 1:20 pm
I'll try it on my server tomorrow at work both 7 and 2k see what happens. There may be a setting but I have never seen it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 14, 2002 at 1:55 pm
Thanks for your replies.
If you test this, put some non-numeric data in the PO field. On SQL 7, it won't report an error. On 2000, it reports an error. SQL 7 must just ignore data conversions that it can't do.
March 15, 2002 at 5:21 am
Here is all I did.
CREATE TABLE [PO] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[PO] [int] NULL ,
[Vender_ID] [nvarchar] (30) NULL ,
CONSTRAINT [PK_PO] PRIMARY KEY CLUSTERED
(
[UID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO PO (PO, Vender_ID) VALUES (12,'NULL')
INSERT INTO PO (PO, Vender_ID) VALUES (15,'NULL')
INSERT INTO PO (PO, Vender_ID) VALUES (24,'NULL')
INSERT INTO PO (PO, Vender_ID) VALUES (NULL,'000053-1')
INSERT INTO PO (PO, Vender_ID) VALUES (NULL,'012254')
INSERT INTO PO (PO, Vender_ID) VALUES (NULL,'01254')
GO
SELECT * FROM PO WHERE Vender_ID = 12
GO
And I did confirm under SQL 7 it does not throw an error, (this would mean SQL 7 auto casted int to ((n)var)char on it's own).
However when I did this under 2000 I got the exact error you did.
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '000053-1' to a column of data type int.
This seems either like a bug or a feature that was lost or never should have been there. Not sure but you will need to cast any variables where this will occurr. I will forward to Microsoft.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 15, 2002 at 3:58 pm
@ParentID is an int.
1 is an int.
'000053-1' is not an int. The type mismatch will raise an error in SQL 2000.
Change @ParentID to a nvarchar(30) and put single quotes around the 1 and you should not have a problem.
March 16, 2002 at 6:29 am
That we know the question at this point is why SQL7 does not react the same as 2000 does?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply