下面这个存储过程,如何使用 @TABLENAME 参数当表名,正确的写法该如何写,急等!
CREATE PROCEDURE yshj
@ysbh varchar(20),@Sess nvarchar(30),@TABLENAME nvarchar(100)
AS
declare @xjzs int,@zmz numeric(19,1),@ztj numeric(19,1),@mbz int,@djsl int
set nocount on
select @xjzs=sum(spl_xjzs),@zmz=sum(spl_zmz),@ztj=sum(spl_ztj),@mbz=sum(spl_mbzjs),@djsl=sum(spl_djsl) from @TABLENAME where spl_bh=@ysbh
set nocount off
select distinct spl_sbbh,spl_sup,@xjzs '箱件总数',@zmz '毛重总数',@ztj '体积总数',@mbz '木包装件数总数',@djsl '大件数量总数' from @TABLENAME where spl_bh=@ysbh and spl_pnum=@Sess
CREATE PROCEDURE yshj
@ysbh varchar(20),@Sess nvarchar(30),@TABLENAME nvarchar(100)
AS
declare @xjzs int,@zmz numeric(19,1),@ztj numeric(19,1),@mbz int,@djsl int
set nocount on
select @xjzs=sum(spl_xjzs),@zmz=sum(spl_zmz),@ztj=sum(spl_ztj),@mbz=sum(spl_mbzjs),@djsl=sum(spl_djsl) from @TABLENAME where spl_bh=@ysbh
set nocount off
select distinct spl_sbbh,spl_sup,@xjzs '箱件总数',@zmz '毛重总数',@ztj '体积总数',@mbz '木包装件数总数',@djsl '大件数量总数' from @TABLENAME where spl_bh=@ysbh and spl_pnum=@Sess
--****************** 动态sql语句基本语法 ******************************
------------------------------------------------------------------------------------------
1 :普通SQL语句可以用exec执行 Select * from tableName
exec('select * from tableName')
exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int, @sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls) --如何将exec执行结果放入变量中? declare @num int, @sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
@ysbh varchar(20),
@Sess nvarchar(30),
@TABLENAME nvarchar(100)
AS
set nocount on
declare @xjzs int,@zmz numeric(19,1),@ztj numeric(19,1),@mbz int,@djsl int
select @xjzs=sum(spl_xjzs),@zmz=sum(spl_zmz),
@ztj=sum(spl_ztj),@mbz=sum(spl_mbzjs),@djsl=sum(spl_djsl)
from @TABLENAME
where spl_bh=@ysbh
declare @cmd nvarchar(4000);
set @cmd=N'select distinct spl_sbbh,spl_sup,
@xjzs ''箱件总数'',@zmz ''毛重总数'',
@ztj ''体积总数'',@mbz ''木包装件数总数'',
@djsl ''大件数量总数''
from ['+@TABLENAME+N'
where spl_bh=@ysbh and spl_pnum=@Sess'; exec sp_executesql @cmd,N'@xjzs int,@zmz numeric(19,1),
@ztj numeric(19,1),@mbz int,@djsl int,
@ysbh varchar(20),@Sess nvarchar(30)',
@xjzs,@zmz,@ztj ,@mbz,@djsl,@ysbh,@Sess;
set nocount off
@ysbh varchar(20),
@Sess nvarchar(30),
@TABLENAME nvarchar(100)
AS
set nocount on
declare @xjzs int,@zmz numeric(19,1),@ztj numeric(19,1),@mbz int,@djsl int;
declare @cmd nvarchar(4000);
SET @cmd=N'select @xjzs=sum(spl_xjzs),@zmz=sum(spl_zmz),
@ztj=sum(spl_ztj),@mbz=sum(spl_mbzjs),@djsl=sum(spl_djsl)
from ['+@TABLENAME+N'
where spl_bh=@ysbh'
exec sp_executesql @cmd,N'@xjzs int OUT,@zmz numeric(19,1) OUT,
@ztj numeric(19,1) OUT,@mbz int OUT,@djsl int OUT',
@xjzs OUT,@zmz,OUT,@ztj OUT,@mbz OUT,@djsl OUT;
set @cmd=N'select distinct spl_sbbh,spl_sup,
@xjzs ''箱件总数'',@zmz ''毛重总数'',
@ztj ''体积总数'',@mbz ''木包装件数总数'',
@djsl ''大件数量总数''
from ['+@TABLENAME+N'
where spl_bh=@ysbh and spl_pnum=@Sess'; exec sp_executesql @cmd,N'@xjzs int,@zmz numeric(19,1),
@ztj numeric(19,1),@mbz int,@djsl int,
@ysbh varchar(20),@Sess nvarchar(30)',
@xjzs,@zmz,@ztj ,@mbz,@djsl,@ysbh,@Sess;
set nocount off
@ysbh varchar(20),
@Sess nvarchar(30),
@TABLENAME nvarchar(100)
AS
set nocount on
declare @xjzs int,@zmz numeric(19,1),@ztj numeric(19,1),@mbz int,@djsl int;
declare @cmd nvarchar(4000);
SET @cmd=N'select @xjzs=sum(spl_xjzs),@zmz=sum(spl_zmz),
@ztj=sum(spl_ztj),@mbz=sum(spl_mbzjs),@djsl=sum(spl_djsl)
from ['+@TABLENAME+N']
where spl_bh=@ysbh'
exec sp_executesql @cmd,N'@xjzs int OUT,@zmz numeric(19,1) OUT,
@ztj numeric(19,1) OUT,@mbz int OUT,@djsl int OUT',
@xjzs OUT,@zmz,OUT,@ztj OUT,@mbz OUT,@djsl OUT;
set @cmd=N'select distinct spl_sbbh,spl_sup,
@xjzs ''箱件总数'',@zmz ''毛重总数'',
@ztj ''体积总数'',@mbz ''木包装件数总数'',
@djsl ''大件数量总数''
from ['+@TABLENAME+N']
where spl_bh=@ysbh and spl_pnum=@Sess'; exec sp_executesql @cmd,N'@xjzs int,@zmz numeric(19,1),
@ztj numeric(19,1),@mbz int,@djsl int,
@ysbh varchar(20),@Sess nvarchar(30)',
@xjzs,@zmz,@ztj ,@mbz,@djsl,@ysbh,@Sess;
set nocount off
@ysbh varchar(20),@Sess nvarchar(30),@TABLENAME nvarchar(100)
AS
declare @xjzs int,@zmz numeric(19,1),@ztj numeric(19,1),@mbz int,@djsl int,@sql nvarchar(1000)
set @sql='select @xjzs=sum(spl_xjzs),@zmz=sum(spl_zmz),@ztj=sum(spl_ztj),@mbz=sum(spl_mbzjs),@djsl=sum(spl_djsl) from '+@TABLENAME+' where spl_bh='+@ysbh
exec sp_executesql @sql
,N'@xjzs int output,@zmz numeric(19,1) output,@ztj numeric(19,1) output,@mbz int output,@djsl int output'
,@xjzs output,@zmz output,@ztj output,@mbz output,@djsl output
set @sql='select distinct spl_sbbh,spl_sup,'+rtrim(@xjzs)+' ''箱件总数'','+rtrim(@zmz)+' ''毛重总数'','+rtrim(@ztj)+' ''体积总数'','
+rtrim(@mbz)+' ''木包装件数总数'','+rtrim(@djsl)+' ''大件数量总数'' from '+@TABLENAME+' where spl_bh=@ysbh and spl_pnum='+@Sess
exec(@sql)
go