求修改一个简单的存储过程
create proc tt
@id varchar(20)
SELECT COUNT(id) AS Tcount1 FROM sfbsecondhouse WHERE companyID=@id
go
create proc tt1
@id varchar(20)
SELECT COUNT(id) AS Tcount2 FROM sfbrent WHERE companyID=@id
go
要求将两个存储过程合并为一个，并求出统计结果之和,也就是Tcount1+Tcount2
谢谢
[解决办法]
create proc tt
@id varchar(20)
as
select sum(Tcount1) from (
SELECT COUNT(id) AS Tcount1 FROM sfbsecondhouse WHERE companyID=@id
union all
SELECT COUNT(id) AS Tcount2 FROM sfbrent WHERE companyID=@id)t
go
[解决办法]
create proc tt
@id varchar(20)
as
declare @count int
SELECT @count = COUNT(id) AS Tcount1 FROM sfbsecondhouse WHERE companyID=@id
SELECT @count = @count + COUNT(id) AS Tcount2 FROM sfbrent WHERE companyID=@id
return @count
GO
[解决办法]
create proc tt
@id varchar(20)
as
select
isnull((SELECT COUNT(id) AS Tcount1 FROM sfbsecondhouse WHERE companyID=@id),0)
+
isnull((SELECT COUNT(id) AS Tcount2 FROM sfbrent WHERE companyID=@id),0)
[解决办法]
create proc tt
(
@id varchar(20)
@num int output
)
as
set NOCOUNT ON
set @num=0
SELECT COUNT(id) AS Tcount1 FROM sfbsecondhouse WHERE companyID=@id
SELECT COUNT(id) AS Tcount2 FROM sfbrent WHERE companyID=@id
set @num=Tcount1 +Tcount2
set NOCOUNT OFF
[解决办法]
create proc p_tt
@id varchar(20)
as
select sum(count1) from
(SELECT COUNT(1) as count1 FROM sfbsecondhouse WHERE companyID=@id
union all SELECT COUNT(1) FROM sfbrent WHERE companyID=@id)a
GO
