请帮忙，关于存储过程改写
create proc add_news
@strsubject nvarchar(100),
@newstype nvarchar(100),
@strfrom nvarchar(100),
@author nvarchar(100),
@editor nvarchar(100),
@newskey nvarchar(100),
@content ntext,
@ptime datetime,
@filepath nvarchar(100)
as
insert into news(strsubject,newstype,strfrom,author,editor,newskey,content,ptime,filepath)
values(@strsubject,@newstype,@strfrom,@author,@editor,@newskey,@content,@ptime,@filepath)
GO
这是一个添加新闻的存储过程,我想把news换成一个通用的,如@tablename,
怎么修改，谢谢
[解决办法]
insert into news(strsubject,newstype,strfrom,author,editor,newskey,content,ptime,filepath)
values(@strsubject,@newstype,@strfrom,@author,@editor,@newskey,@content,@ptime,@filepath)
->
exec(
'insert into '+@tablename+
'(strsubject,newstype,strfrom,author,editor,newskey,content,ptime,filepath)
values( '+@strsubject+ ', '+@newstype+ ', '+@strfrom+ ', '+@author+ ', '+@editor+ ', '+@newskey+ ', '+@content+ ', '+@ptime+ ', '+@filepath+ ') ')
[解决办法]
--try
create proc add_news
(
@strsubject nvarchar(100),
@newstype nvarchar(100),
@strfrom nvarchar(100),
@author nvarchar(100),
@editor nvarchar(100),
@newskey nvarchar(100),
@content ntext,
@ptime datetime,
@filepath nvarchar(100)
)
as
declare @sql nvarchar(4000)
set @sql= 'insert into [ '+@tablename+ '](strsubject,newstype,strfrom,author,editor,newskey,content,ptime,filepath) '
set @sql+= 'values( ' ' '+@strsubject+ ' ' ', ' ' '+@newstype+ ' ' ', ' ' '+@strfrom+ ' ' ', ' ' '+@author+ ' ' ', ' ' '+@editor+ ' ' ', ' ' '+
@newskey+ ' ' ', ' ' '+@content+ ' ' ', ' ' '+@ptime+ ' ' ', ' ' '+@filepath+ ' ' ') '
exec(@sql)
GO
[解决办法]
使用動態SQL語句
create proc add_news
@tablename nvarchar(100),
@strsubject nvarchar(100),
@newstype nvarchar(100),
@strfrom nvarchar(100),
@author nvarchar(100),
@editor nvarchar(100),
@newskey nvarchar(100),
@content ntext,
@ptime datetime,
@filepath nvarchar(100)
as
Begin
Declare @S Varchar(8000)
Select @S = ' insert into ' + @tablename + '(strsubject,newstype,strfrom,author,editor,newskey,content,ptime,filepath)
values( ' ' ' + @strsubject + ' ' ', ' ' ' + @newstype + ' ' ', ' ' ' + @strfrom + ' ' ', ' ' ' + @author + ' ' ', ' ' ' + @editor + ' ' ', ' ' ' + @newskey + ' ' ', ' ' ' + @content + ' ' ', ' ' ' + Convert(Varchar(20), @ptime, 120) + ' ' ', ' ' ' + @filepath + ' ' ') '
EXEC(@S)
End
GO
