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

Aaron and Tibor - Thank you for your replys.
Aaron, the tbl-vs-tblname issue and data type were silly mistakes -
thank you.
Tibor, so then making the entire table name a variable (rather than
just part of it) should solve the problem, right? The below code
attempts to make this change. Now getting the following error:
"Syntax error converting the varchar value 'tblIR_LB_' to a column of
data type smallint."
CREATE PROC CreateIRTables ( @.tblname varchar(50))
AS
DECLARE @.SQL varchar(999)
SET @.SQL = 'CREATE TABLE [dbo].[' + @.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
DECLARE @.tblname varchar(50)
SET @.i = 1
WHILE (@.i <= 32)
BEGIN
SET @.tblname = 'tblIR_LB_' + @.i
EXECUTE CreateIRTables @.tblname
SET @.i = @.i + 1
ENDEXACT SAME PROBLEM, you've just moved it to a different place! You're
trying to add an integer to a string. You can't do this!

> SET @.tblname = 'tblIR_LB_' + @.i
Should be
SET @.tblname = 'tblIR_LB_' + CONVERT(VARCHAR(12),@.i)|||i think you are chasing a red herring, but I wil point out this error :)
SET @.tblname = 'tblIR_LB_' + @.i
^ @.I is a small int
SET @.tblname = 'tblIR_LB_' + CAST(@.i As varchar)
My suggestion to see what is happening .... return your @.SQL as an output
paramer and print it each time.
if it *was* giving you the error on tblIR_LB_2 then I assume tblIR_LB_1
worked? So, see what is happening by looking at the @.SQL. PRINT it as an
output variable and run the code directly.
Good luck.
John Scragg
"Butaambala" wrote:

> Aaron and Tibor - Thank you for your replys.
> Aaron, the tbl-vs-tblname issue and data type were silly mistakes -
> thank you.
> Tibor, so then making the entire table name a variable (rather than
> just part of it) should solve the problem, right? The below code
> attempts to make this change. Now getting the following error:
> "Syntax error converting the varchar value 'tblIR_LB_' to a column of
> data type smallint."
> CREATE PROC CreateIRTables ( @.tblname varchar(50))
> AS
> DECLARE @.SQL varchar(999)
> SET @.SQL = 'CREATE TABLE [dbo].[' + @.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
> DECLARE @.tblname varchar(50)
> SET @.i = 1
> WHILE (@.i <= 32)
> BEGIN
> SET @.tblname = 'tblIR_LB_' + @.i
> EXECUTE CreateIRTables @.tblname
> SET @.i = @.i + 1
> END
>