BAFFLED! Why won't this code work?!?!

Hello,
The below code is intended to create a sp, then loop through a set of
values, each time running the sp with the new value. The CREATE PROC
executes fine, but I get an error when attempting the loop: "Invalid
column name 'tblIR_LB_2'."
Your help appreciated!
CREATE PROC sp_CreateIRTables ( @.tbl smallint)
AS
DECLARE @.SQL varchar(999)
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
[dteDate] [smalldatetime] NULL ,
[sglSTDEV(upper)] [float] NULL ,
[sglSTDEV(lower)] [float] NULL ,
[intMA] [smallint] NULL ,
[dblPL] [float] NULL ,
[dblSTDEV] [float] NULL ,
[dblIR] [float] NULL ,
[intTotalDays] [int] NULL ,
[intExposure] [int] NULL ) ON [PRIMARY]'
EXEC (@.SQL)
DECLARE @.i smallint
SET @.i = 1
WHILE (@.i <= 32)
BEGIN
EXECUTE sp_CreateIRTables @.i
SET @.i = @.i + 1
END> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
DON'T USE sp_ PREFIX!

> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
Is it @.tbl or @.tblname? Pick one.|||> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
@.tblname is, for some reason, a SMALLINT, not any kind of string.
First, I suggest not using this kind of unmanageable scheme.
But to solve your issue in the short term, you can convert to a character
type, e.g.
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + CONVERT(VARCHAR(12), @.tblname)
+ '] (|||You can't have a variable in the table name of a CREATE TABLE unless you use
dynamic SQL to create
the table. Why not create a temp table instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Butaambala" <brogers75@.yahoo.com> wrote in message
news:1131645127.077192.239150@.f14g2000cwb.googlegroups.com...
> Hello,
> The below code is intended to create a sp, then loop through a set of
> values, each time running the sp with the new value. The CREATE PROC
> executes fine, but I get an error when attempting the loop: "Invalid
> column name 'tblIR_LB_2'."
> Your help appreciated!
>
> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
> AS
> DECLARE @.SQL varchar(999)
> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
> [dteDate] [smalldatetime] NULL ,
> [sglSTDEV(upper)] [float] NULL ,
> [sglSTDEV(lower)] [float] NULL ,
> [intMA] [smallint] NULL ,
> [dblPL] [float] NULL ,
> [dblSTDEV] [float] NULL ,
> [dblIR] [float] NULL ,
> [intTotalDays] [int] NULL ,
> [intExposure] [int] NULL ) ON [PRIMARY]'
> EXEC (@.SQL)
>
> DECLARE @.i smallint
> SET @.i = 1
> WHILE (@.i <= 32)
> BEGIN
> EXECUTE sp_CreateIRTables @.i
> SET @.i = @.i + 1
> END
>