有人帮忙写了个存储过程,执行的结果是一张表。但是存储结果不能查询,想发满哪位高手帮忙该成视图或是函数之类的,能查询结果的类型(需要传参)。谢谢,如下:
CREATE proc SelfComIDContent
@comId int
as
select c.*,(c.selfReal/c.dutyShould)as selfRate, p.pName from
(select a.selfID,a.idxNum,a.pID,a.idxID,a.perid,a.selfReason,a.selflevel,a.selfif,idxitem,idxss,dutycharge ,dutyShould as dutyShould,
(select [dbo].[f_selfComIDNow](a.idxNum,@comId))as selfReal
from [dbo].[selfcheck] a inner join [dbo].[dutydis] b on a.dutyID=b.dutyID join indexs i on a.idxid=i.idxid where a.pid in (select pID from person where comID=@comID))c join person p on c.pID=p.pId
where c.perID=(select top 1 perid from period order by perID desc) order by idxid asc
GO
CREATE proc SelfComIDContent
@comId int
as
select c.*,(c.selfReal/c.dutyShould)as selfRate, p.pName from
(select a.selfID,a.idxNum,a.pID,a.idxID,a.perid,a.selfReason,a.selflevel,a.selfif,idxitem,idxss,dutycharge ,dutyShould as dutyShould,
(select [dbo].[f_selfComIDNow](a.idxNum,@comId))as selfReal
from [dbo].[selfcheck] a inner join [dbo].[dutydis] b on a.dutyID=b.dutyID join indexs i on a.idxid=i.idxid where a.pid in (select pID from person where comID=@comID))c join person p on c.pID=p.pId
where c.perID=(select top 1 perid from period order by perID desc) order by idxid asc
GO
解决方案 »
- 如何写这样的SQL语句?
- sql 查询语句问题
- SQL语句问题
- 存储过程问题??
- SQLServer2005 服务器运行一个月后,突然sa访问不到数据库
- 如何写一条sql语句,从ms sql server中挑出一个BOOLEAN型的常量False
- 存储过程遇到的问题。Output parameters have not yet been processed. Call getMoreResults().
- 如何批量修改某个字段的值的精确度
- 郁闷,继续开帖求一段存储过程的写法,只有40分,全出!求好心人指点!
- 关于触发器的问题 ,高手请帮忙
- 用户在前台界面可自己选择添加,修改,显示,计算列名.这功能怎么实现的
- sql 2000打了sp4补丁后出现问题~~!
CREATE function fn_SelfComIDContent(@comId int)
returns table
as
return(
select c.*,(c.selfReal/c.dutyShould)as selfRate, p.pName from
(select a.selfID,a.idxNum,a.pID,a.idxID,a.perid,a.selfReason,a.selflevel,a.selfif,idxitem,idxss,dutycharge ,dutyShould as dutyShould,
(select [dbo].[f_selfComIDNow](a.idxNum,@comId))as selfReal
from [dbo].[selfcheck] a inner join [dbo].[dutydis] b on a.dutyID=b.dutyID join indexs i on a.idxid=i.idxid where a.pid in (select pID from person where comID=@comID))c join person p on c.pID=p.pId
where c.perID=(select top 1 perid from period order by perID desc) order by idxid asc)
GO
CREATE function fn_SelfComIDContent(@comId int)
returns table
as
return(
select c.*,(c.selfReal/c.dutyShould)as selfRate, p.pName from
(select a.selfID,a.idxNum,a.pID,a.idxID,a.perid,a.selfReason,a.selflevel,a.selfif,idxitem,idxss,dutycharge ,dutyShould as dutyShould,
(select [dbo].[f_selfComIDNow](a.idxNum,@comId))as selfReal
from [dbo].[selfcheck] a inner join [dbo].[dutydis] b on a.dutyID=b.dutyID join indexs i on a.idxid=i.idxid where a.pid in (select pID from person where comID=@comID))c join person p on c.pID=p.pId
where c.perID=(select top 1 perid from period order by perID desc))
-- order by idxid asc)
-- 这里不能用order by了
GO--调用
declare @i int
set @i = 1
select * from dbo.fn_SelfComIDContent(@i)
??
declare @i int
set @i = 1
select * from dbo.fn_SelfComIDContent(@i) order by idxid asc