Please help! My SQL statement doesn't work..

[alecken]

It should be simple but this is the first time for me trying to use store
procedure, can someone help correcting my syntax? I'm trying to do some looping
in my store procedure: declare @loopnumber int declare @dbname varchar
select @loopnumber = 1 select @dbname = 'MYDB_' + CAST(@loopnumber) While
@loopnumber < 10 BEGIN /* SQL query here */ I put a create DB statement here
select @loopnumber = @loopnumber + 1 END I got error from SQL server saying
that: Server: Msg 1035, Level 15, State 10, Line 5 Incorrect syntax near
'CAST', expected 'AS'. Server: Msg 170, Level 15, State 1, Line 11 Line 11:
Incorrect syntax near '@dbname'. The strange thing is, when I run this query,
1 database get created despite of the error and it is not looping as expected.
The database name supposed to be MYDB_1, MYDB_2 etc instead I got 1 complete
database with all the tables created but the name of the new database is
@dbname How can I make this loop work? it seems so simple, I've check SQL book
and I have no syntax error as far as looping What I'm not sure of is how to
convert numeric datatype to nvarchar, is using CAST( ) not allowed? Please
help!!!!!!

[dmuniz]

I am not to familiar with MY SQL but in SQL Server the problem would be that
the line select @dbname = 'MYDB_' + CAST(@loopnumber) should be select
@dbname = 'MYDB_' + CAST(@loopnumber as varchar(20)) The following should work
as well select @dbname = 'MYDB_' + CAST(@loopnumber as varchar)