请SQL高手帮我看下这个存储过程

有四个表 BASWL、 STOCK、 ColorMXB、 SizeMXB

其中 BASWL 有 WLID、 WLDH 、WLMCH 、WLLBID

STOCK 有 WLID 、SHL、 COLORID 、SIZEID

ColorMXB 有 MXID、 NAME

SizeMXB 有 MXID 、NAME

* GetFreeItem()是另一个存储过程可以查出KYSHL

其中 BASWL.WLID=STOCK.WLID STOCK.COLORID=COLORMXB.MXID

STOCK.SIZEID=SIZEMXB.MXID

我想查出 WLLBID＝ **** WLDH LIKE ‘%***%’对应的

WLDH WLMCH COLORMXB.NAME SIZEMXB.NAME SUM(SHL) KYSHL

我写的存储过程如下，当我查询 select * from getlbmx(1111,’%cc%’)时查到25个结果却用了21秒，求高手指点或写个简单的，谢谢

IF OBJECT_ID ('dbo.getlbmx') IS NOT NULL

DROp FUNCTION dbo.getlbmx

GO

CREATE FUNCTION getlbmx(@lbid INTEGER ,@wldh VARCHAr (10))

RETURNS @P TABLE(wldh VARCHAr (80),

wlmch VARCHAr (120),COLOR VARCHAr (80),

SIZE VARCHAr (80),

SHL numeric(24,8),

kyshl numeric(24,8) ) AS

BEGIN

DECLARE wlid CURSOR

FOR SELECT wlid FROM BASWL WHERe WLLBID = @lbid AND WLDH LIKE @wldh

OPEN wlid

DECLARE @id INT

FETCH next FROM wlid INTO @id while (@@fetch_status<>-1)

BEGIN

if(@id>0)

BEGIN

INSERT INTO @p (wldh,wlmch,Color,Size,shl,kyshl)

SELECt (SELECT WLDH FROM BASWL WHERe WLID = @ID) AS WLID,

(SELECt WLMCh FROM BASWL WHERe WLID =@ID) AS WLMCh,

(SELECt NAME FROM ColorMXB where MXID = a.colorid) AS Color,

(select NAME from SizeMXB where MXID=a.sizeid) as Size,

sum(SHL) as SHL ,

(select ShL from GetFreeItem() where WLID=@ID and colorID=a.colorid and sizeid=a.sizeid ) as KYSHL

from STOCK a where WLID=@ID

GROUP BY a.colorid ,a.sizeid

end

FETCH next FROM wlid INTO @id

END

CLOSE wlid

DEALLOCATE wlid

RETURN

end

GO

select WLDH,WLMCH,C.NAME,D.NAME,SUM(b.SHL),E.ShL AS KYSHL
from BASWL A left join STOCK B ON A.WLID=B.WLID
LEFT JOIN ColorMXB C ON B.COLORID=C.MXID
LEFT JOIN SizeMXB D ON B.SIZEID=D.MXID
LEFT JOIN GetFreeItem() E ON E.WLID=B.WLID AND E.COLORID=B.COLORID AND E.SIZEID=B.SIZEID
WHERe A.WLLBID IN (**** WLDH LIKE ‘%***%’)
GROUP BY WLDH,WLMCH,C.NAME,D.NAME,E.SHL

不知道你的getibmx是怎么写的，前面的这样实现如何： select A.wldh,A.wlmch,C.Color,D.SIZE,sum(B.SHL) as SHL from

from BASWL A ,STOCK B ,ColorMXB C,SizeMXB D where A.WLID = B.WLID AND B.colorid = C.MXid AND B.sizeid = D.MXid

and a.wllbid = '

我的建议是，你后面的SQL语句最好使用连接，写在一个语句中，而不是把这个多的语句多分开来

同时，最好不要使用游标，游标是非常消耗SQL效率的

还有GetFreeItem这个存储过程，如果不是复杂的，最好就也是直接在SQL语句中得到结果

查询的结果最后直接有存储过程来完成，而不是用语句select * from getlbmx(