I get an error with SS2000 that I don't get with SS2005. I used the same scripts below to create all 3 items on both systems.
**************************
**********
*********
NO ERROR ON SQL SERVER 2005:
ERROR ON SQL SERVER 2000:
(1 row(s) affected)
Server: Msg 245, Level 16, State 1, Procedure fnGenerateNewBoxId, Line 32
Syntax error converting the varchar value '*' to a column of data type int.
**************************
**********
********
TABLE:
CREATE TABLE [NewBoxId] (
[ID] [int] IDENTITY (50290000, 1) NOT NULL ,
[BoxId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_
AS NULL ,
[ordernum] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_
AS NULL CONSTRAINT [DF_NewBoxId_ordernum] DEFAULT (1),
[dateCreated] [datetime] NOT NULL CONSTRAINT [DF_NewBoxId_dateCreated] DEFAULT (getdate()),
[used] [bit] NOT NULL CONSTRAINT [DF_NewBoxId_used] DEFAULT (0)
) ON [PRIMARY]
GO
**************************
**********
******
QUERY:
exec usp_GetBoxNumber 1234567
**************************
**********
********
STORED PROCEDURE
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[usp_GetBoxNumber]
@ord varchar(10)
as
declare @myIdentity int
insert into dbo.NewBoxID (ordernum, used) Values (@ord, 0)
select @myIdentity=SCOPE_IDENTITY
()
update dbo.NewBoxID set Boxid = dbo.fnGenerateNewBoxId(@my
Identity)
where ID = @myIdentity
**************************
**********
*********
FUNCTION:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fnGenerateNewBoxId]
(
@boxnumber int
)
Returns char(20)
as
Begin
declare @fullstr as varchar(20)
declare @checkdigit as varchar (20)
declare @prefix as varchar(10)
declare @work as varchar(20)
declare @I as int
declare @oddcounter as int
declare @evencounter as int
declare @cwork as int
declare @oddtotal as int
declare @eventotal as int
declare @total as int
set @oddcounter = 1
set @evencounter = 2
set @oddtotal = 0
set @eventotal = 0
set @prefix = '0000024964'
set @work = RIGHT('000000000' + cast(@boxnumber as varchar(7)),9)
set @fullstr = @prefix + @work
set @I = len(@fullstr)
while @oddcounter <= @I
begin
set @cwork = substring(@fullstr, @oddcounter, 1)
--print @oddcounter
--print @cwork
set @oddtotal = @oddtotal + cast(@cwork as int)
set @oddcounter = @oddcounter + 2
end
--select @oddtotal
while @evencounter <= @I
begin
set @cwork = substring(@fullstr, @evencounter, 1)
set @eventotal = @eventotal + cast(@cwork as int)
set @evencounter = @evencounter + 2
end
set @total = (@oddtotal * 3) + @eventotal
set @cwork = @total % 10
if @cwork = 0
begin
set @checkdigit = @cwork
end
else set @checkdigit = 10 - @cwork
set @fullstr = @fullstr + @checkdigit
Return(@fullstr)
End
--------------------------
------
Start Free Trial