好吧,再发一篇帖子,为了给主任交差....(40分)
有这样一个存储过程:

CREATE   PROCEDURE   tingji
@mth   int
as    
if   @mth   > 12   or   @mth   <1   return
declare   @cc   varchar(300)

set   @cc= 'select   count(*)  
from     [ '+ 'dhzl '+cast(@mth   as   varchar)+ ']where   serv_state   like   "F1S "     and   exchange_i   in   (32061,540000) '
exec(@cc)
GO

执行一下是没有问题的,但我要同时查询不只一个结果,也就是要用   union   连接起来
比如,我要这样写:
CREATE   PROCEDURE   tingji
@mth   int
as    
if   @mth   > 12   or   @mth   <1   return
declare   @cc   varchar(300)

set   @cc= 'select   count(*)  
from     [ '+ 'dhzl '+cast(@mth   as   varchar)+ ']where   serv_state   like   "F1S "     and   exchange_i   in   (32061,540000)
union
select   count(*)  
from     [ '+ 'dhzl '+cast(@mth   as   varchar)+ ']where   serv_state   like   "F1N "and   exchange_i   in   (32061,540000) '
exec(@cc)
GO

------------------------
用这种方法语法检查没问题,但运行起来,是有错误的

请问我该怎么修改?

------解决方案--------------------
--try

select
sum(case when serv_state= 'F1S ' then 1 else 0 end) as F1S,
sum(case when serv_state= 'F1N ' then 1 else 0 end) as F1N
from dhzl

------解决方案--------------------
--try


CREATE PROCEDURE tingji
@mth int
as
if @mth > 12 or @mth <1 return

declare @cc varchar(8000)
set @cc= '
select count(*) from [dhzl '+cast(@mth as varchar)+ '] where serv_state like "F1S% " and exchange_i in (32061,540000)
union
select count(*) from [dhzl '+cast(@mth as varchar)+ '] where serv_state like "F1N% " and exchange_i in (32061,540000) '
exec(@cc)
GO
------解决方案--------------------
CREATE PROCEDURE tingji
@mth int
as
if @mth > 12 or @mth <1 return
declare @cc varchar(300)

set @cc= 'select count(*)
from [ '+ 'dhzl '+rtrim(@mth)+ '] where serv_state like ' 'F1S ' ' and exchange_i in (32061,540000)
union
select count(*)
from [ '+ 'dhzl '+rtrim(@mth)+ '] where serv_state like ' 'F1N ' ' and exchange_i in (32061,540000) '
exec(@cc)
GO
------解决方案--------------------
加个别名在 count(*) 后面

CREATE PROCEDURE tingji
@mth int
as
if @mth > 12 or @mth <1 return
declare @cc varchar(300)

set @cc= 'select count(*) as cnt
from [ '+ 'dhzl '+cast(@mth as varchar)+ ']where serv_state like "F1S " and exchange_i in (32061,540000)//当 serv_state 为F1S 时的记录条数
union//合并两个结果
select count(*) as cnt
from [ '+ 'dhzl '+cast(@mth as varchar)+ ']where serv_state like "F1N "and exchange_i in (32061,540000) ' //当 serv_state 为 F1N 时的记录条数
exec(@cc)
GO
---------------------------------------