December 6, 2005 at 4:44 pm
I am trying to dynamically create a table then insert data into it, so far without success. The table gets created but when the insert statement runs the following message comes up in sql server: Any suggestions would be greatly appreciated!!
Server: Msg 203, Level 16, State 2, Line 55
The name 'insert into [##temp_ServiceTotals16:15:08](chAgencyID,vchComponentName,mTotal,vchMonth,vchYear)
select chAgencyID,vchComponentName,sum(smUnitRate * flUnits) as mTotal,'July' as vchMonth,'2004' as vchYear
from maClientService maCS inner join mlComponent mlCm
on maCS.iComponentID = mlCM.iComponentID
where (dtStartDate between '07/01/2004' and '07/31/2004') and chAgencyID = '8...
The query is:
declare @chAgencyID char(10),
@FY smallint,
@Days int,
@Month varchar(50),
@Day varchar(2),
@Year char(4),
@StartDate datetime,
@EndDate datetime,
@i int,
@Date datetime
select @FY = 2005,
@Days = 1,
@Month = '6',
@StartDate = @Month + '/' + cast(@Days as varchar(2)) + '/' + cast((@FY-1)as char(4)),
@i = 1,
@chAgencyID = '850404752',
@Year = year(@StartDate)
declare @TableName nvarchar(100),
@SQL nvarchar (1000),
@TableDate varchar(20)
select @TableDate = CONVERT(VARCHAR(50),GETDATE(),108),
@TableName = '##temp_ServiceTotals' + @TableDate,
@SQL = 'CREATE TABLE ' + '[' + @TableName + ']' + '(
chAgencyID char(10) null,
vchComponentName varchar(50) null,
flUnitsfloat null,
smUnitRate smallmoney null,
mTotal float null,
vchMonth varchar(50) null,
vchYear char(4) null)'
exec (@SQL)
While @i <> 13
Begin
select @StartDate = dateadd(mm,1,@StartDate)
print @StartDate
set @Date = @StartDate
set @Date = (@Date - (day(@Date) - 1))
set @Date = dateadd(mm,1,@Date)
set @Date = dateadd(dd, -1, @Date)
set @Days = datepart(dd,@Date)
select @Month = datepart(month,@StartDate)
select @EndDate = @Month + '/' + cast(@Days as varchar(2)) + '/' + @Year
SET @SQL = 'insert into ' + '[' + @TableName + ']' +
'(chAgencyID,vchComponentName,mTotal,vchMonth,vchYear)
select chAgencyID,vchComponentName,sum(smUnitRate * flUnits) as mTotal,' + '''' + datename(month,@StartDate) + '''' + ' as vchMonth,' + ''''+ convert(nvarchar,(year(@StartDate))) + '''' + ' as vchYear
from maClientService maCS inner join mlComponent mlCm
on maCS.iComponentID = mlCM.iComponentID
where (dtStartDate between ' + '''' + convert(nvarchar,@StartDate,101) + '''' + ' and ' + ''''+ convert(nvarchar,@EndDate,101) + '''' + ')' +
' and chAgencyID = ' + '''' + @chAgencyID + ''''+
' group by chAgencyID,vchComponentName'
exec @SQL
set @StartDate = dateadd(mm,1,@StartDate)
set @i = @i + 1
End
December 7, 2005 at 6:18 am
Should there not be a space in ']' + '(
December 7, 2005 at 6:22 am
After you build your @SQL string you try to execute it by saying:
exec @SQL
but you need to enclose the @SQL variable in parentheses like you do when you create the table:
exec (@SQL)
otherwise SQL Server thinks the variable is a stored procedure name.
December 7, 2005 at 8:18 am
OOPS!, it's always the simple stuff that gets you! Thanks for catching that for me, works fine now!
April 3, 2008 at 12:38 pm
Thanks! This was VERY helpful.
May 16, 2008 at 9:47 am
placing '()' ie. exec(@str)
this really helped me brother..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply