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

10 Nov 2005 5:52 PM

Butaambala

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

10 Nov 2005 6:02 PM

Aaron Bertrand [SQL Server MVP]

> 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)
+ '] (

10 Nov 2005 6:02 PM

Aaron Bertrand [SQL Server MVP]

> 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.

10 Nov 2005 6:03 PM

Tibor Karaszi

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/

Show quote

"Butaambala" <broger***@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
>

10 Nov 2005 6:28 PM

Butaambala

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

10 Nov 2005 6:38 PM

Aaron Bertrand [SQL Server MVP]

EXACT 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)

10 Nov 2005 6:41 PM

John Scragg

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

Show quote

"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
>
>