存储过程语句执行问题？高手请进。
declare @sql varchar(8000)
declare @hj int
set @sql='select sum(人数) from rsb '
set @hj=exec(@sql)
insert into zzzb values('0101','合计’,@hj)
错误出现在这一句中，set @hj=exec(@sql)
怎么才能赋值经变量，我想在一个表中求一个字段的和，然后在将这个值插到另一张表中。
问题点数：100、回复次数：26Top
1 楼txlicenhe（马可）回复于 2003-11-01 09:09:27 得分 30
dim cn as new adodb.connection
dim rs as new adodb.recordset
cn.open ...
declare @sql varchar(8000)
declare @hj int
set @sql='select sum(人数) from rsb '
set @hj=exec(@sql)
rs.open @sql,cn
cn.exec("insert into zzzb values('0101','合计’," + rs.fields(0) + ")")
Top
2 楼txlicenhe（马可）回复于 2003-11-01 09:18:05 得分 0
dim cn as new adodb.connection
dim rs as new adodb.recordset
cn.open ...
declare @sql varchar(8000)
declare @hj int
set @sql='select sum(人数) from rsb '
rs.open @sql,cn
cn.exec("insert into zzzb values('0101','合计’," + rs.fields(0) + ")")Top
3 楼DTWUJP（建平.net）回复于 2003-11-01 09:28:55 得分 0
我这几句是封闭在存储过程中的。让大家误会了。Top
4 楼saucer（思归）回复于 2003-11-01 09:38:14 得分 30
try
declare @sql nvarchar(4000)
declare @hj int
set @sql='select @hj=sum(人数) from rsb '
EXEC sp_executesql @sql,
N'@hj INT OUTPUT',
@hj OUTPUT
insert into zzzb values('0101','合计’,@hj)
Top
5 楼yoki（小马哥--鬓微霜，又何妨）回复于 2003-11-01 09:51:35 得分 0
同意 思归 大虾
呵呵Top
6 楼yoki（小马哥--鬓微霜，又何妨）回复于 2003-11-01 09:55:49 得分 0
或者
declare @sql varchar(8000)
set @sql='declare @hj int select sum(人数) from rsb '
set @sql=@sql+' insert into zzzb values('0101','合计’,@hj)'
exec(@sql)Top
7 楼txlicenhe（马可）回复于 2003-11-01 10:02:21 得分 0
同意思归
参考：
http://expert.csdn.net/Expert/topic/2364/2364046.xml?temp=.829693
[交流]动态SQL语句Top
8 楼DTWUJP（建平.net）回复于 2003-11-01 10:29:13 得分 0
CREATE PROCEDURE wjp_queryzyrs AS
begin
set nocount on
DELEte from dt_zyrsb
insert dt_zyrsb
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(InHospInfo.InSerial) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%' and InHospInfo.OutFlag=0 and len(InHospInfo.InSerial)>7
GROUP BY Depart.DepCode,Depart.DepDes
union
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(Depart.urid) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%'
GROUP BY Depart.DepCode,Depart.DepDes
order by depart.depcode
delete from dt_zyrsb where depcode in (select depcode from dt_zyrsb group by depcode having count(depcode)>1) and 住院人数=0
declare @rssql nvarchar(4000)
declare @hj int
set @rssql='select @hj=sum(住院人数) from dt_zyrsb'
EXEC sp_executesql @rssql,
N'@hj INT OUTPUT',
@hj OUTPUT
insert into dt_zyrsb values( '01020z','合计',@hj)
declare @sql varchar(8000)
set @sql = 'select ''住院人数'' as 科室名称 '
select @sql = @sql + ',sum(case 科室名称 when '''+科室名称+''' then 住院人数 end) ['+ reverse(substring(reverse(科室名称),3,len(科室名称))) +']'
from (select distinct 科室名称 from dt_zyrsb) as a
select @sql = @sql+ 'from dt_zyrsb'
exec(@sql)
set nocount off
end
GO
几天来，在各位高手指点下，完成了这个存储过程，由于第一次学写存储过程，错误不少，请大家帮看看，现在加了思归大侠的语句后又得不到数据了，查询分析器只报"警告: 聚合或其它 SET 操作消除了空值。",没有显示数据，但不加思归大侠的语句，可以得到正确的数据。请各位高手指点。
在vb中的语句：
Dim cmd As adodb.Command
Set cmd = New adodb.Command
cmd.ActiveConnection = CnnToData
cmd.CommandTimeout = 30
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "wjp_queryzyrs"
Dim rst As adodb.Recordset
Set rst = cmd.Execute
Set Grid.DataSource = rstTop
9 楼palmkey（原水）回复于 2003-11-01 10:37:04 得分 0
declare @hj int
select @hj=sum(人数) from rsb '
insert into zzzb values('0101','合计’,@hj)
即可Top
10 楼yoki（小马哥--鬓微霜，又何妨）回复于 2003-11-01 10:40:42 得分 0
declare @rssql nvarchar(4000)
declare @hj int
set @rssql='select @hj=sum(住院人数) from dt_zyrsb'
EXEC sp_executesql @rssql,
N'@hj INT OUTPUT',
@hj OUTPUT
insert into dt_zyrsb values( '01020z','合计',@hj)
改为：
insert into dt_zyrsb values select '01020z','合计',sum(住院人数) from dt_zyrsb
Top
11 楼yoki（小马哥--鬓微霜，又何妨）回复于 2003-11-01 10:51:09 得分 0
或者将exec(@sql)放到set nocount off 后看看Top
12 楼DTWUJP（建平.net）回复于 2003-11-01 11:02:40 得分 0
yoki(小马哥) ：
都试了，还是得不到正确的数据显示，我改为这样
set nocount off
insert into dt_zyrsb select '01020z','合计',sum(住院人数) from dt_zyrsb
set nocount on
declare @sql varchar(8000)
set @sql = 'select ''住院人数'' as 科室名称 '
select @sql = @sql + ',sum(case 科室名称 when '''+科室名称+''' then 住院人数 end) ['+ reverse(substring(reverse(科室名称),3,len(科室名称))) +']'
from (select distinct 科室名称 from dt_zyrsb) as a
select @sql = @sql+ 'from dt_zyrsb'
exec(@sql)
set nocount off
end
查询分析器只报：
“警告: 聚合或其它 SET 操作消除了空值。
（所影响的行数为 1 行）
”
没有显示数据。
如果将insert into dt_zyrsb select '01020z','合计',sum(住院人数) from dt_zyrsb注释后，就可以正确显示数据了。奇怪
Top
13 楼DTWUJP（建平.net）回复于 2003-11-01 11:05:54 得分 0
但存储过程 已经执行了。表中的数据已改变。但取不到数据显示在vsflexgrid表格中。我计算后将现在的表dt_zyrsb的行列进行反转。Top
14 楼zjcxc（邹建）回复于 2003-11-01 11:49:57 得分 0
--使用错误,改为:
declare @sql nvarchar(8000)
declare @hj int
set @sql='select @hj=sum(人数) from rsb '
exec sp_executesql @sql,N'@hj int out',@hj out
insert into zzzb values('0101','合计’,@hj)Top
15 楼pengdali（）回复于 2003-11-01 12:06:52 得分 40
set nocount on
insert dt_zyrsb select '01020z','合计',sum(住院人数) from dt_zyrsb
declare @sql varchar(8000)
set @sql = 'select ''住院人数'' as 科室名称 '
select @sql = @sql + ',sum(case 科室名称 when '''+科室名称+''' then 住院人数 else 0 end) ['+ reverse(substring(reverse(科室名称),3,len(科室名称))) +']'
from (select distinct 科室名称 from dt_zyrsb) as a
select @sql = @sql+ 'from dt_zyrsb'
exec(@sql)
set nocount off
end
Top
16 楼pengdali（）回复于 2003-11-01 12:08:46 得分 0
CREATE PROCEDURE wjp_queryzyrs AS
begin
set nocount on
DELEte from dt_zyrsb
insert dt_zyrsb
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(InHospInfo.InSerial) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%' and InHospInfo.OutFlag=0 and len(InHospInfo.InSerial)>7
GROUP BY Depart.DepCode,Depart.DepDes
union
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(Depart.urid) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%'
GROUP BY Depart.DepCode,Depart.DepDes
order by depart.depcode
delete from dt_zyrsb where depcode in (select depcode from dt_zyrsb group by depcode having count(depcode)>1) and 住院人数=0
declare @rssql nvarchar(4000)
declare @hj int
set @rssql='select @hj=sum(住院人数) from dt_zyrsb'
EXEC sp_executesql @rssql,
N'@hj INT OUTPUT',
@hj OUTPUT
insert into dt_zyrsb values( '01020z','合计',@hj)
declare @sql varchar(8000)
set @sql = 'select ''住院人数'' as 科室名称 '
select @sql = @sql + ',sum(case 科室名称 when '''+科室名称+''' then 住院人数 else 0 end) ['+ reverse(substring(reverse(科室名称),3,len(科室名称))) +']'
from (select distinct 科室名称 from dt_zyrsb) as a
select @sql = @sql+ 'from dt_zyrsb'
exec(@sql)
set nocount off
end
GOTop
17 楼pengdali（）回复于 2003-11-01 12:13:49 得分 0
---------帮你优化如下：
CREATE PROCEDURE wjp_queryzyrs AS
begin
set nocount on
select * into #dt_zyrsb from (
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(InHospInfo.InSerial) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%' and InHospInfo.OutFlag=0 and len(InHospInfo.InSerial)>7
GROUP BY Depart.DepCode,Depart.DepDes
union all
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(Depart.urid) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%'
GROUP BY Depart.DepCode,Depart.DepDes
) tem
order by depcode
delete #dt_zyrsb where depcode in (select depcode from #dt_zyrsb group by depcode having count(*)>1) and 住院人数=0
insert #dt_zyrsb select '01020z','合计',sum(住院人数) from #dt_zyrsb
--这里如果有用exec动态的需要：
--exec('insert #dt_zyrsb select ''01020z'',''合计'',sum(住院人数) from #dt_zyrsb')
declare @sql varchar(8000)
set @sql = 'select ''住院人数'' as 科室名称 '
select @sql = @sql + ',sum(case 科室名称 when '''+科室名称+''' then 住院人数 else 0 end) ['+ reverse(substring(reverse(科室名称),3,len(科室名称))) +']'
from (select distinct 科室名称 from #dt_zyrsb) as a
select @sql = @sql+ 'from #dt_zyrsb'
exec(@sql)
set nocount off
end
GOTop
18 楼DTWUJP（建平.net）回复于 2003-11-01 12:14:34 得分 0
主要是不能显示数据，请帮我优化一下这个存储过程。Top
19 楼pengdali（）回复于 2003-11-01 12:15:48 得分 0
1、不用固定表，应为它有并发问题。
2、executesql是多余
3、加入else 0 去掉 “警告: 聚合或其它 SET 操作消除了空值。 问题。Top
20 楼DTWUJP（建平.net）回复于 2003-11-01 12:21:28 得分 0
大力，我主要是想将结果返回后用表格控件显示数据，但现在取不到数据，实际上存储过程已正确执行了。不过，临时表是个好主意。谢谢，set cocount on 也用了，还是不管用。Top
21 楼yoki（小马哥--鬓微霜，又何妨）回复于 2003-11-01 12:30:03 得分 0
我的意思是这样：
set nocount on
。
。
。
set nocount off
exec(@sql)
Top
22 楼DTWUJP（建平.net）回复于 2003-11-01 13:18:25 得分 0
yoki(小马哥) ：仍不行，警告: 聚合或其它 SET 操作消除了空值。没有数据返回begin
set nocount on
select * into #dt_zyrsb from (
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(InHospInfo.InSerial) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%' and InHospInfo.OutFlag=0 and len(InHospInfo.InSerial)>7
GROUP BY Depart.DepCode,Depart.DepDes
union all
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(Depart.urid) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%'
GROUP BY Depart.DepCode,Depart.DepDes
) tem
order by depcode
delete #dt_zyrsb where depcode in (select depcode from #dt_zyrsb group by depcode having count(*)>1) and 住院人数=0
insert #dt_zyrsb select '01020z','合计',sum(住院人数) from #dt_zyrsb
--这里如果有用exec动态的需要：
--exec('insert #dt_zyrsb select ''01020z'',''合计'',sum(住院人数) from #dt_zyrsb')
declare @sql varchar(8000)
set @sql = 'select ''住院人数'' as 科室名称 '
select @sql = @sql + ',sum(case 科室名称 when '''+科室名称+''' then 住院人数 else 0 end) ['+ reverse(substring(reverse(科室名称),3,len(科室名称))) +']'
from (select distinct 科室名称 from #dt_zyrsb) as a
select @sql = @sql+ 'from #dt_zyrsb'
set nocount off
exec(@sql)
end
Top
23 楼DTWUJP（建平.net）回复于 2003-11-01 13:31:51 得分 0
begin
set nocount on
--查找各科室的住院人数，然后插入到表格dt_zyrsb中，
select * into #dt_zyrsb from (
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(InHospInfo.InSerial) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%' and InHospInfo.OutFlag=0 and len(InHospInfo.InSerial)>7
GROUP BY Depart.DepCode,Depart.DepDes
union all
SELECT depart.depcode ,Depart.DepDes AS '科室名称', Count(Depart.urid) AS '住院人数'
FROM Depart left outer JOIN InHospInfo ON Depart.DepCode = InHospInfo.DepCode
WHERE depart.depcode like '01020%'
GROUP BY Depart.DepCode,Depart.DepDes
) tem
order by depcode
--由于表中有重复记录，删除表中重复记录。
delete #dt_zyrsb where depcode in (select depcode from #dt_zyrsb group by depcode having count(*)>1) and 住院人数=0
--记录全院总住院人数,问题出在这里，如果将注释后，就可以在VB中显示数据。
insert #dt_zyrsb select '01020z','合计',sum(住院人数) from #dt_zyrsb
--进行行列转换
declare @sql varchar(8000)
set @sql = 'select ''住院人数'' as 科室名称 '
select @sql = @sql + ',sum(case 科室名称 when '''+科室名称+''' then 住院人数 else 0 end) ['+ reverse(substring(reverse(科室名称),3,len(科室名称))) +']'
from (select distinct 科室名称 from #dt_zyrsb) as a
select @sql = @sql+ 'from #dt_zyrsb'
set nocount off
--执行生成的sql语句，将记录集返回，
exec(@sql)
end
由于第一次写存储过程，大概思路是这样的，但不能返回记录供VB显示结果集。
Top
24 楼DTWUJP（建平.net）回复于 2003-11-01 13:41:35 得分 0
#dt_zyrsb的表结构为：
科室代码 科室名称 住院人数
1 内科 2
。
。
。
100 合计 300
经行列转换后变为：
科室名称 内科 外科 妇科 。。。。 合计
住院人数 2 3 。。。。。 300
Top
25 楼yoki（小马哥--鬓微霜，又何妨）回复于 2003-11-07 10:50:51 得分 0
干脆加一句
--进行行列转换
declare @sql varchar(8000)
set @sql = 'select ''住院人数'' as 科室名称 '
select @sql = @sql + ',sum(case 科室名称 when '''+科室名称+''' then 住院人数 else 0 end) ['+ reverse(substring(reverse(科室名称),3,len(科室名称))) +']'
from (select distinct 科室名称 from #dt_zyrsb) as a
select @sql = @sql+ ' into #TEMP from #dt_zyrsb'
--执行生成的sql语句，将记录集返回，
exec(@sql)
set nocount off
select * from #TEMP '加它，另外@sql中加into #TEMP
endTop
26 楼DTWUJP（建平.net）回复于 2003-11-09 15:16:55 得分 0
真他妈的见鬼了，今天无意中运行了一下，尽然出了结果，我可哪也没有修改Top
