如果参数中带有表名，这和普通的存储过程编写有点不同,如我们写个简单的存储过程，通过传递表名得到数据表里面的内容
CREATE PROCEDURE test2
@table nvarchar(50)
as
declare @sql char(50)
--set @sql='select * from '+@table
select @sql='select * from '+@table
exec sp_executesql @sql
GO
这样写要注意几点：
1.参数@sql必须是"ntext/nchar/nvarchar"中的任意一种类型。
2.exec后面要加上sp_executesql， sp_executesql 系统存储过程执行 Unicode 字符串,所以前面变量数据类型，要是其中的几种。
3.如果传递的参数类型有整型或是日期等等在写连接字符串的时候必须转换成Unicode字符串,也就是"ntext/nchar/nvarchar"中的一种。
下面是复杂一点的一个存储过程,有很多数据转换。
--对评价意见进行插入更新的类
--特别注意连接语句的写法和VB.NET连接语句正好相反，用单引号做外部双引号做内部还有用sp_executesql来执行。具体见他们的区别，另外遇到日期和数值型记得用cast转换
CREATE PROCEDURE UeditComment
@Table CHAR(50)='',--y表
@IDZLD int=0,
@XMID CHAR(6)='',
@SJ datetime=NULL,
@JSZY CHAR(10)='',
@JSLX CHAR(1)='',
@YJNR CHAR(200)='',
@BJ int=0,
@Table2 char(50)='',--e表评价的id表
@Table3 char(50)='',--读取视图vtll
@YDR char(6)='',
@YDLB char(1)='',
@TJZY char(10)='',
@YJID int=0
as
declare @sql NVARCHAR(4000)
if @bj=1
set @sql='INSERT INTO '+@Table +' (IDZLD,XMID,SJ,JSZY, JSLX,YJNR) VALUES ("'+cast(@IDZLD as nvarchar(20))+'","'+@XMID+'","'+cast(@SJ as nvarchar(10))+'","'+@JSZY +'","'+@JSLX+'","'+@YJNR+'")'
--set @sql='INSERT INTO '+@Table +' (IDZLD,XMID,SJ,JSZY,JSLX,YJNR) VALUES ('+@IDZLD+','+@XMID+',' +@Sj+','+@JSZY+','+@JSLX+','+@YJNR+')'
--if @Bj=2 --说明是更新
--print @sql
if @bj=3 --获得最行的评价意见专册--replace(replace(replace(a.JSLX,"0","空提"),"1","虚提"),"2","其他意见") as Jslx
set @sql='select YJID,a.jslx,a.xmid,IDZLD,IDTJ,IDJH,b.zy,(+rtrim(fzlmc)+"(版本"+ rtrim(htver)+")"+rtrim(ms)) as fzlmc,c.xm,a.SJ,a.JSZY,a.YJNR from ' +rtrim(@table) +' a,'+rtrim(@Table3) +' b,xtyhb c where
Yjid not in (select yjid from ' +rtrim(@table2) +' where ydr="' +@ydr+'" and ydlb="'+@YDLB +'") and b.ID=a.IDZLD AND a.xmid=c.ryid and b.ZY= "'+@tjzy +'"'
if @bj=4 --查询所有的评价意见
set @sql='select a.YJID,IDZLD,IDTJ,IDJH,b.zy,a.xmid, (+rtrim(fzlmc)+"(版本"+ rtrim(htver)+")"+rtrim(ms)) as fzlmc,fzlmc as fzlmc1,htver,ms,(+"("+rtrim(a.xmid)+")"+ c.xm) as xm,a.SJ,a.JSZY,Jslx,a.YJNR from ' +rtrim(@table) +' a,'+rtrim(@Table3) +' b,xtyhb c where a.xmid=c.ryid and a.idzld=b.ID order by zy,fzlmc,htver,yjid'
if @bj=5 --通过yjid来查找
set @sql='select * from ' +@table +' where yjid="'+cast(@yjid as nvarchar(10))+'"'
if @bj=6 --通过专业来查找主要是除总体、质管、调度外的人员
set @sql='select a.YJID,a.xmid,IDZLD,IDTJ,IDJH,b.zy, (+rtrim(fzlmc)+"(版本"+ rtrim(htver)+")"+rtrim(ms)) as fzlmc,fzlmc as fzlmc1,htver,ms,(+"("+rtrim(a.xmid)+")"+ c.xm) as xm,a.SJ,a.JSZY,Jslx,a.YJNR from ' +rtrim(@table) +' a,'+rtrim(@Table3) +' b,xtyhb c where a.xmid=c.ryid and a.idzld=b.ID and (b.zy="'+@JSZY + '" or a.jszy="'+@jszy +'") order by zy,fzlmc,htver,yjid'
if @bj=7 ---查询最新的评价意见针对总体、质管、调度者而言的
set @sql='select YJID,a.jslx,a.xmid,IDZLD,IDTJ,IDJH,b.zy,(+rtrim(fzlmc)+"(版本"+ rtrim(htver)+")"+rtrim(ms)) as fzlmc,c.xm,a.SJ,a.JSZY,a.YJNR from ' +rtrim(@table) +' a,'+rtrim(@Table3) +' b,xtyhb c where
Yjid not in (select yjid from ' +rtrim(@table2) +' where ydr="' +@ydr+'" and ydlb="'+@YDLB +'") and Yjid not in (select yjid from ' +rtrim(@table2) +' where ztbj="1") and b.ID=a.IDZLD AND a.xmid=c.ryid'
exec sp_executesql @sql
print @sql
--return @sql2
GO
