Stored Procedure Loop Code Fails 1

[markgrizzle]

Hi,
This piece of my SQL Server 2000 stored procedure compiles, but gives me this error when I run it?

The name 'CREATE TABLE #pivot (property CHAR(5), metric CHAR(25), field1 (INT) NOT NULL, field2 (INT) NOT NULL, field3 (INT) NOT NULL, field4 (INT) NOT NULL, field5 (INT) NOT NULL, field6 (INT) NOT NULL, field7 (INT) NOT NULL, field8 (INT) NOT NULL, field9 (INT) NOT NULL, field10 (INT) NOT NULL, field11 (INT) NOT NULL, field12 (INT) NOT NULL, field13 (INT) NOT NULL, field14 (INT) NOT NULL, fie...

declare @sql varchar(8000)
declare @fieldcounter int

set @fieldcounter = 1

set @sql = 'CREATE TABLE #pivot (property CHAR(5), metric CHAR(25)'

while @fieldcounter < datediff(day, @startdate, @enddate) + 1
begin
set @sql = @sql + ', field' + cast(@fieldcounter as varchar(3)) + ' (INT) NOT NULL'
set @fieldcounter = @fieldcounter + 1
end

set @sql = @sql + ')'
exec @sql

I also tried using an alter table satatement in the loop, and that failed also. I'm a novice tsql programmer. Can anyone make a suggestion?

Thanks,
Mark

 

[gmmastros]

Change:
exec @sql

To:

exec [!]([/!]@sql[!])[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Also, remove the parenthesis from int.

Change:

set @sql = @sql + ', field' + cast(@fieldcounter as varchar(3)) + ' [!]([/!]INT[!])[/!] NOT NULL'

To:

set @sql = @sql + ', field' + cast(@fieldcounter as varchar(3)) + ' INT NOT NULL'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markgrizzle]

Thanks George. I appreciate the help.