首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

请帮忙,关于存储过程改写,该如何解决

2012-01-05 
请帮忙,关于存储过程改写createprocadd_news@strsubjectnvarchar(100),@newstypenvarchar(100),@strfromnv

请帮忙,关于存储过程改写
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

热点排行