ITKeyword,专注技术干货聚合推荐

注册 | 登录

经典的sql存储过程 用了临时表变量

wangdetian168 分享于

2021腾讯云限时秒杀,爆款1核2G云服务器298元/3年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

推荐:sql server 存储过程中使用变量表,临时表的分析(续)

      最近,我有一朋友,对我说他的数据库中的很多存储过程,执行都是超时.让我替他看看是什么原因.我一看,原来他的存储过程中用了很多的临时表与变量表.于是我跟

create proc UInfo(@Uid varchar(36))
as
begin
declare @ResultDt table(cname varchar(64),acount int,ccount int,scount int)
set @Uid='simao'
declare  @tempcount int
insert into @ResultDt select
'商铺 ',(select count(1) from ShopOffice where UID=@Uid),(select count(1) from ShopOffice where SCommand=1 and UID=@Uid),(select count(1) from ShopOffice where NStatus=3 and UID=@Uid)
insert into @ResultDt select
'写字楼',(select count(1) from OfficeBuilding where UID=@Uid),(select count(1) from OfficeBuilding where SCommand=1 and UID=@Uid),(select count(1) from OfficeBuilding where NStatus=3 and UID=@Uid)
insert into @ResultDt select
'工业地产',(select count(1) from Industrial where UID=@Uid),(select count(1) from Industrial where SCommand=1 and UID=@Uid),(select count(1) from Industrial where NStatus=3 and UID=@Uid)
insert into @ResultDt select
'大型项目',(select count(1) from Project where UID=@Uid),(select count(1) from Project where SCommand=1 and UID=@Uid),(select count(1) from Project where NStatus=3 and UID=@Uid)
insert into @ResultDt select
'生意转让',(select count(1) from Bussiness where UID=@Uid),(select count(1) from Bussiness where SCommand=1 and UID=@Uid),(select count(1) from Bussiness where NStatus=3 and UID=@Uid)
insert into @ResultDt select
'合计',sum(acount),sum(ccount),sum(scount) from @ResultDt
select * from @ResultDt
end <script type="text/javascript"><!-- google_ad_client = "pub-1985112462197986"; /* 新广告 336x280, 创建于 10-8-20 */ google_ad_slot = "6915893334"; google_ad_width = 336; google_ad_height = 280; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script><script type="text/javascript"><!-- google_ad_client = "pub-1985112462197986"; /* 336x280, 创建于 10-6-25 */ google_ad_slot = "9255958887"; google_ad_width = 336; google_ad_height = 280; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

推荐:SQL Server(存储过程)临时表与滞后名称解析

    开发中,我们经常用Create Procedure命令创建存储过程,而在创建过程时实际发生的是,Query Analyzer检查其语法,检查完毕并正确后将其插入系统表syscomment

create proc UInfo(@Uid varchar(36)) as begin declare @ResultDt table(cname varchar(64),acount int,ccount int,scount int) set @Uid='simao' declare  @tempcount int insert into @Resul

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。