January 14, 2009 at 9:10 am
I have a sproc in SQL Server 2000 that dynamically builds a sql statement and then executes it using sp_executesql.
When I execute it, however, I get an error message "Incorrect Syntax near" and then it return the first 128 characters of my SQL Statement.
If I "print @sql" the statement before it is passed into sp_executesql and then declare and put it in a nvarchar(4000) variable and pass it to sp_executesql outside the stored proc it works fine.
Here is the sproc. What am I missing?
ALTER PROCEDURE Activity AS
Declare @id as nvarchar(2)
Declare @sql as nvarchar(4000)
Declare @fld as nvarchar(7)
set @id = 3
Select @fld = colname from ae_adefs where datatypeid = 5 and appid = @id
Declare @mth as nvarchar(2)
Declare @yr as nvarchar(4)
Declare @cnt as int
Set @cnt = 12
Select @mth = month(Dateadd(month, -@cnt, getdate()))
Select @yr = year(Dateadd(month, -@cnt, getdate()))
While @cnt > 0
Begin
If @sql is null
set @sql = char(39) + 'Select ' + @id + ' as AppID,' + cast(@mth as nvarchar(2)) + ' as Month,' + cast(@yr as nvarchar(4)) + ' as DaYear, sum(numobjects) as pages_Added from ae_dt' + @id + ' where month(' + @fld + ') = ' + cast(@mth as nvarchar(2)) + ' and year(' + @fld + ') = ' + @yr
Else
set @sql = @sql + ' Select ' + @id + ' as AppID,' + cast(@mth as nvarchar(2)) + ' as Month,' + cast(@yr as nvarchar(4)) + ' as DaYear, sum(numobjects) as pages_Added from ae_dt' + @id + ' where month(' + @fld + ') = ' + cast(@mth as nvarchar(2)) + ' and year(' + @fld + ') = ' + @yr
If @cnt > 0
Begin
set @sql = @sql + ' Union All'
End
Set @cnt = @cnt - 1
set @mth = month(Dateadd(month, -@cnt, getdate()))
set @yr = year(Dateadd(month, -@cnt, getdate()))
End
set @sql = substring(@sql, 0, len(@sql) - 9) + char(39)
print @sql
Exec sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
January 14, 2009 at 10:06 am
added a few more '
--ALTER PROCEDURE Activity
--AS
Declare @id as nvarchar(2)
Declare @sql as nvarchar(4000)
Declare @fld as nvarchar(7)
set @id = 3
SET @fld = 'test'
--Select @fld = colname from ae_adefs where datatypeid = 5 and appid = @id
Declare @mth as nvarchar(2)
Declare @yr as nvarchar(4)
Declare @cnt as int
Set @cnt = 12
Select @mth = month(Dateadd(month, -@cnt, getdate()))
Select @yr = year(Dateadd(month, -@cnt, getdate()))
While @cnt > 0
Begin
If @sql is null
BEGIN
set @sql = char(39) + 'Select ' + @id + ' as AppID,'
+ cast(@mth as nvarchar(2)) + ' as Month,'
+ cast(@yr as nvarchar(4))
+ ' as DaYear, sum(numobjects) as pages_Added from ae_dt '
+ @id + ' where month(''' + @fld + ''') = '
+ cast(@mth as nvarchar(2)) + ' and year(''' + @fld + ''') = '
+ @yr
PRINT @sql
END
Else
BEGIN
set @sql = @sql + ' Select ' + @id + ' as AppID,'
+ cast(@mth as nvarchar(2)) + ' as Month,'
+ cast(@yr as nvarchar(4))
+ ' as DaYear, sum(numobjects) as pages_Added from ae_dt'
+ @id + ' where month(''' + @fld + ''') = '
+ cast(@mth as nvarchar(2)) + ' and year(''' + @fld + ''') = '
+ @yr
PRINT @sql
END
If @cnt > 0
Begin
set @sql = @sql + ' Union All'
End
Set @cnt = @cnt - 1
set @mth = month(Dateadd(month, -@cnt, getdate()))
set @yr = year(Dateadd(month, -@cnt, getdate()))
End
set @sql = substring(@sql, 0, len(@sql) - 9) + char(39)
print @sql
Exec sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
January 14, 2009 at 10:06 am
January 14, 2009 at 11:50 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply