CAST or CONVERT: invalid attributes specified for type 'int'

This stored procedure was working on Sql Server 2000. However it is not working on Sql Server 2005.

How can i correct it?

The full error is :
Msg 291, Level 16, State 1, Procedure itemleri_bul, Line 32
CAST or CONVERT: invalid attributes specified for type 'int'

CREATE procedure itemleri_bul 
@StrUserID varchar(30)
AS
DECLARE
@length int, -- stritem uzunluu
@i int, -- item'in stritem deki yeri
@dwid int, 
@dur int,
@duar int,
@StackSize1 int,
@Sira int,
@Row int,
@name varchar(100),
@extname varchar(100),
@ext int
 
 
    set @i = 14*0+1
    set @length = 401
 
select @row=count(*) from userdata where struserid=@StrUserID
 
delete from itemler where struserid=@StrUserID
 
if @row>0 
  begin
 
WHILE @i < @length
Begin
 
Select @dwid=cast(cast(substring(cast(substring(strItem, @i,4) as varbinary(4)), 4, 
1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(strItem, @i,4) as 
varbinary(4)), 2, 1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) as int(4)),
@dur = cast(cast(cast( substring(strItem, @i+5, 1) as varbinary(1))+cast(substring(strItem, @i+4, 1) as 
varbinary(1)) as varbinary(2)) as smallint),
@StackSize1 = cast(cast(cast( substring(strItem, @i+7, 1) as varbinary(1))+cast(substring(strItem, @i+6, 1) as 
varbinary(1)) as varbinary(2)) as smallint),
        @StrUserID = strUserID
        From UserData
        Where strUserID = @StrUserID
 
 
 
 
insert into itemler (dwid,stacksize,durability,struserid,sira,itembasicname,extname) 
values(@dwid,@stacksize1,@dur,@StrUserID,(@i-1) / 8,@extname,@name )
 
set @i=@i+8
 
end
end
 
GO

Open in new window

shamanq11Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
It wasn't easy.  I have taken to copying the statements pasted here and breaking them down through indentation, like below (this is the code above) which helps make issues like this much more obvious.
CREATE procedure itemleri_bul 
@StrUserID varchar(30)
AS
DECLARE
    @length int, -- stritem uzunluu
    @i int, -- item'in stritem deki yeri
    @dwid int, 
    @dur int,
    @duar int,
    @StackSize1 int,
    @Sira int,
    @Row int,
    @name varchar(100),
    @extname varchar(100),
    @ext int
 
set @i = 14*0+1
set @length = 401
 
select @row=count(*) from userdata where struserid=@StrUserID
 
delete from itemler where struserid=@StrUserID
 
if @row>0 
begin
 
    WHILE @i < @length
    Begin
     
        Select @dwid=
            cast(
                cast(
                    substring(
                        cast(
                            substring(strItem, @i,4) 
                            as varbinary(4)
                        ), 4, 1) + 
                    substring(
                        cast(
                            substring(strItem, @i,4) 
                            as varbinary(4)
                        ), 3, 1) + 
                    substring(
                        cast(
                            substring(strItem, @i,4) 
                            as varbinary(4)
                        ), 2, 1) + 
                    substring(
                        cast(
                            substring(strItem, @i,4) 
                            as varbinary(4)
                        ), 1, 1) 
                    as varbinary(4)
                ) as int(4)
            ),
            @dur = 
            cast(
                cast(
                    cast(
                        substring(strItem, @i+5, 1) 
                        as varbinary(1)
                    ) + 
                    cast(
                        substring(strItem, @i+4, 1) 
                        as varbinary(1)
                    ) 
                    as varbinary(2)
                ) 
                as SMALLINT
            ),
            @StackSize1 = 
            cast(
                cast(
                    cast(
                        substring(strItem, @i+7, 1) 
                        as varbinary(1)
                    ) + 
                    cast(
                        substring(strItem, @i+6, 1) 
                        as varbinary(1)
                    ) 
                    as varbinary(2)
                ) 
                as SMALLINT
            ),
            @StrUserID = strUserID
        From UserData
        Where strUserID = @StrUserID
         
        insert into itemler (
            dwid,
            stacksize,
            durability,
            struserid,
            sira,
            itembasicname,
            extname
            ) 
        values(
            @dwid,
            @stacksize1,
            @dur,
            @StrUserID,
            (@i-1) / 8,
            @extname,
            @name 
        )
         
        set @i=@i+8
         
    end
end
 
GO

Open in new window

0
 
rob_farleyCommented:
Sorry to be a pain, but can you show some of the data you're trying to convert? That might really help understand that block of code there...
0
 
Jeff CertainCommented:
I'd start troubleshooting this by braking the casts into individual pieces that get stored in temporary variables, to see exactly which cast is failing.

That would let you examine the data for that field, to see what can't be cast.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Brendt HessSenior DBACommented:
Your statement includes a cast "as int(4)".  In SQL 2000, due to a bug in the parser, this worked.  However, the syntax should simply be "as int" - if you want a different size, specify bigint or smallint.
0
 
rob_farleyCommented:
Yes, it'll be the int(4). I scanned your code yesterday looking for this type of thing, but didn't notice it. Well done to bhess1 for noticing it.

Rob
0
 
rob_farleyCommented:
Yeah... I should've cut and pasted into something else that would let me search better... but didn't. Guess I'm slack...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.