sql - "Incorrect syntax near the keyword 'where'." -
stored procedure throws error:
error sql (error 156) [sqlstate 4200] "incorrect syntax near keyword 'where'."
the stored procedure populate table using insert
statement, keep getting error when runs. i'm not sure how fix error.
here proc:
alter procedure [dbo].[buildtable] declare @querytxt varchar(max) declare @queryname varchar(50) declare @columnval int declare @countval varchar(50) declare @isfirst char(1) declare @currentdt varchar(20) declare @savdate datetime begin set @isfirst = 'y'; set @currentdt = convert(varchar(20),current_timestamp,110); declare c1 cursor select queryname, querytext subsqueries open c1 fetch next c1 @queryname, @querytxt while @@fetch_status = 0 begin --call stored proc exec informixquery @querytxt, @columnval output set @countval = ltrim(str(@columnval)) if @isfirst = 'y' begin exec ('insert tblhistory (' + @queryname + ',transdate) values(' + @countval + ',' + '''' + @currentdt + '''' + ')') set @isfirst = 'n' end else begin exec ('update tblhistory set ' + @queryname + ' = ' + @countval + ' transdate = ' + '''' + @currentdt + '''') end fetch next c1 @queryname, @querytxt end close c1 deallocate c1 end
i see there variables missing. barring that, queryname fields looks varchar column. need surround @countval quotes.
exec ('update tblhistory set ' + @queryname + ' = ''' + @countval + ''' transdate = ' + '''' + @currentdt + '''')
Comments
Post a Comment