CREATE PROCEDURE yshj
@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' 的错误,是什么原因啊.谢谢指导
@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' 的错误,是什么原因啊.谢谢指导
解决方案 »
- 存储过程中如何把数字或字符变成表名
- 存储过程执行到底需要什么权限呢?
- 用一条SQL语句如何解决一张表中ID编号的问题?
- 又一个相似的学生成绩表查询问题,麻烦了!
- 关于两表记录分配问题
- 在什么情况下需要定义参照完整性呢?
- 文本类型转数字类型
- SQL Server 2000安装补丁的问题
- 数据库sql2008 r2的 怎么还原到sql2008 r1上 求怎么转换
- sql2000服务器问题
- SQL2005建立连接速度问题
- 请问SELECT * FROM ::中符号::和collation_name ::={windows_collation_name}|{sql_collation_name}中符号::=是什么意思?
@ysbh
@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
-- 测试输出了一下
declare @cmd nvarchar(4000);
declare @ysbh varchar(20)
declare @TABLENAME varchar(20)
set @TABLENAME = 'company'
set @ysbh = '123'
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'
print @cmd
-- 应该是拼字符串的问题,尝试下面这种拼法,没仔细加,如有问题,自己调整一下
@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+'''' /*
select @xjzs=sum(spl_xjzs),@zmz=sum(spl_zmz),
@ztj=sum(spl_ztj),@mbz=sum(spl_mbzjs),@djsl=sum(spl_djsl)
from [company]
where spl_bh=@ysbh
*/
@ztj numeric(19,1),@mbz int,@djsl int,
@ysbh varchar(20),@Sess nvarchar(30)',
@xjzs 类型,@zmz,@ztj ,@mbz,@djsl,@ysbh,@Sess,@ysbh varchar(20);
后面要包含@CMD中所有变量!!!还得指定类型
stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。[@params =] N'@parameter_name data_type [,...n]'字符串,其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成。n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数,则不需要 @params。该参数的默认值为 NULL。
加错地方了!
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),@ysbh varchar(20)',
@ztj numeric(19,1) OUT,@mbz int OUT,@djsl int OUT,@ysbh varchar(20) out',
@xjzs OUT,@zmz,OUT,@ztj OUT,@mbz OUT,@djsl OUT,@ysbh out; 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; 应该是这样!!!试试??
@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+''' and spl_pnum='''+@Sess+''' '
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 nvarchar(20),@Sess nvarchar(30)',
@xjzs,@zmz,@ztj ,@mbz,@djsl,@ysbh,@Sess; set nocount off 执行的时候 提示: 为过程或函数 指定的参数太多。
狂检查!!!发现了!!!
exec sp_executesql @cmd,N'@xjzs int OUT,@zmz numeric(19,1) OUT,
@ztj numeric(19,1) OUT,@mbz int OUT,@djsl int OUT,@ysbh varchar(20) out',
@xjzs OUT,@zmz OUT,@ztj OUT,@mbz OUT,@djsl OUT,@ysbh out; 多了个逗号!!
@ztj numeric(19,1) OUT,@mbz int OUT,@djsl int OUT,@ysbh varchar(20) out',
@xjzs OUT,@zmz OUT,@ztj OUT,@mbz OUT,@djsl OUT,@ysbh out;
最后@zmz out 中间多了个逗号!!