0

Here is the query that I am trying to run 

DECLARE @str VARCHAR(10)
DECLARE @index int
DECLARE @SQL VARCHAR(100)
DECLARE @SQL2 VARCHAR(300)
DECLARE @SQL3 VARCHAR(400)

SET @str = 'DB'
SET @index = 0

WHILE @index < 100
BEGIN
    SET @SQL = 'use ' + @str + CASE 
                   WHEN @index < 10 THEN '00'
                   WHEN @index < 100 THEN '0'
                   ELSE ''
                   END + CAST(@index as VARCHAR)
    EXEC(@SQL)
    GO

    CREATE TABLE Persons
    (
        PersonID int,
        LastName varchar(255),
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255)
    );

    set @index = @index + 1
END

which I think should create a table per database in the databases named DB000 to DB099 but I get 3 syntax errors they are posted below

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ')'.

Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@index".

Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@index".

Can someone help me out in making this work correctly? Or at least point me in the right direction?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ALupus
  • 3
  • 1
  • 1
    I believe you also need at add the length of the VARCHAR in your CAST. See [link](http://technet.microsoft.com/en-us/library/aa226054(v=sql.80).aspx) – mungea05 Jul 25 '14 at 18:07
  • Good practice but not really needed in this case as long as you are not planning to exceed the default length of 30. – TMNT2014 Jul 25 '14 at 18:18

1 Answers1

0

This should work if you need this one table to be created in databases named DB000 to DB099:

       DECLARE @str VARCHAR(10)
       DECLARE @index int
       DECLARE @SQL VARCHAR(100)
       DECLARE @SQL2 VARCHAR(300)
       DECLARE @SQL3 VARCHAR(400)


       SET @str = 'DB'
       SET @index = 0

       WHILE @index < 100
       BEGIN
       SET @SQL = @str + CASE 
               WHEN @index < 10 THEN '00'
               WHEN @index < 100 THEN '0'
               ELSE ''
       END + CAST(@index as VARCHAR)
       PRINT @SQL
       -- EXEC(@SQL)

       SET @SQL2 = 'CREATE TABLE ' + @SQL + '..Persons
         (
          PersonID int,
          LastName varchar(255),
          FirstName varchar(255),
          Address varchar(255),
          City varchar(255)
          );'
        EXEC(@SQL2)

       set @index = @index + 1
       END
hsd
  • 56
  • 4