请教一个存储过程优化
试着学做SQL存储过程，不知这个存储过程问题在哪里，还能如何优化，特发出来向有经验的朋友请教，敬请指点，谢谢！
CREATE PROCEDURE [dbo].[p_yyk_qz]
(
@bzqbm VARCHAR(10)='桑拿', --部门
@yyrq VARCHAR(15), --营业日期
@dhhm VARCHAR(15), --电话号码
@jsgh VARCHAR(10), --技师工号
@jsmm VARCHAR(10), --技师密码
@ccgcfhz INT OUTPUT, --存储过程返回值
@ccgcfhzms NVARCHAR(100) OUTPUT --存储过程返回值描述
)
AS
DECLARE @fh varchar(10)
DECLARE @jsxm varchar(10)
DECLARE @jsxb varchar(2)
DECLARE @zdid varchar(20)
DECLARE @gkid varchar(20)
DECLARE @fwxm varchar(50)
DECLARE @jb bit
DECLARE @dz bit
DECLARE @fwy varchar(10)
DECLARE @ph varchar(10)
DECLARE @mc varchar(10)
DECLARE @xfsj datetime
DECLARE @yjtzjssj datetime
DECLARE @fl varchar(10)
DECLARE @zl varchar(10)
DECLARE @xmmc varchar(30)
DECLARE @dw varchar(2)
DECLARE @dj decimal
DECLARE @sl decimal
DECLARE @yxdz bit
DECLARE @jzdz bit
DECLARE @zdjr bit
DECLARE @zdjs bit
DECLARE @sc decimal
DECLARE @tzjssl decimal
DECLARE @djcpbm bit
DECLARE @ksjs bit
DECLARE @tcxm bit
DECLARE @jsfw bit
DECLARE @cpddy bit
DECLARE @fjfw bit
DECLARE @kgxm bit
DECLARE @ck varchar(10)
DECLARE @cpbm varchar(10)
DECLARE @jffs varchar(10)
DECLARE @fwyxm varchar(10)
DECLARE @js varchar(10)
DECLARE @dydd varchar(10)
DECLARE @fwdd varchar(10)
BEGIN
--通过电话号码取房间号
DECLARE curFj CURSOR FOR
SELECT bh FROM g_sy_bh WHERE xm=@bzqbm AND dh=@dhhm
OPEN curFj
FETCH NEXT FROM curFj
INTO @fh
WHILE (@@FETCH_STATUS=0)
--通过技师工号获取技师安排信息
DECLARE curJs CURSOR FOR
SELECT name,xb,gkid,fwxm,jb,dz,fwy,fwdd,zt FROM g_sy_js WHERE xm=@bzqbm AND gh=@jsgh AND rq=@yyrq
OPEN curJs
FETCH NEXT FROM curJs
INTO @jsxm,@jsxb,@gkid,@fwxm,@jb,@dz,@fwy
WHILE (@@FETCH_STATUS=0)
--通过安排技师的顾客ID判断宾客是否结帐，并取得宾客信息，以便写消费明细时调用
DECLARE curBk CURSOR FOR
SELECT bh,mc,zdid,ld FROM g_sy_xfjs WHERE xm=@bzqbm AND gkid=@gkid
OPEN curBk
FETCH NEXT FROM curBk
INTO @ph,@mc,@zdid
WHILE (@@FETCH_STATUS=0)
--通过安排技师的服务项目获取服务项目信息
DECLARE curFwxm CURSOR FOR
SELECT GETDATE() AS xfsj,DATEADD(minute,sc,GETDATE()) AS yjtzjssj,fwxmfl,zl,xmmc,dw,dj,yxdz,jzdz,zdjs,sc,tzjssl,djcpbm,tcxm,jsfw,cpddy,fjfw,kgxm,ck FROM g_sy_fwxm WHERE xm=@bzqbm AND xmmc=@fwxm
OPEN curFwxm
FETCH NEXT FROM curFwxm
INTO @xfsj,@yjtzjssj,@fl,@zl,@xmmc,@dw,@dj,@yxdz,@jzdz,@zdjs,@sc,@tzjssl,@djcpbm,@tcxm,@jsfw,@cpddy,@fjfw,@kgxm,@ck
WHILE (@@FETCH_STATUS=0)
DECLARE @xfmxid varchar(20)
SET @xfmxid=NEWID()
--写入消费明细
INSERT INTO g_sy_xfmx(xfmxid,zdid,gkid,xm,bh,mc,fl,zl,xmmc,dw,dj0,dj,sl,xj,dzl,dze,ys,czy,yxdz,jzdz,zdjr,zdjs,sc,tzjssl,xfrq,xfsj,yjtzjssj,ksjs,ksjssj,ksjsczy,djcpbm,cpbm,jffs,tcxm,fwy,fwyxm,jsfw,js,jsxm,jsxb,jb,dz,cpddy,dydd,fjfw,fwdd,kgxm,ck) VALUES (@xfmxid,@zdid,@gkid,@bzqbm,@ph,@mc,@fl,@zl,@xmmc,@dw,@dj,@dj,1,@dj,0,0,@dj,@jsgh,@yxdz,@jzdz,@zdjr,@zdjs,@sc,@tzjssl,@yyrq,@xfsj,@yjtzjssj,@ksjs,@xfsj,@jsgh,@djcpbm,@cpbm,@jffs,@tcxm,@fwy,@fwyxm,@jsfw,@js,@jsxm,@jsxb,@jb,@dz,@cpddy,@dydd,@fjfw,@fwdd,@kgxm,@ck)
--更新技师上钟信息
UPDATE g_sy_js set gkbh=@ph,gkmc=@mc,gkid=@gkid,zt='上',szsj=@xfsj,yjxzsj=@yjtzjssj WHERE xm=@bzqbm AND gh=@jsgh
--更新服务房间状态为有客
UPDATE g_sy_bh SET zt='有客' WHERE xm=@bzqbm AND bh=@fh AND kgfwxmdj=1 AND zt<>'有客'
--更新安排通知日志为已起钟
UPDATE g_sy_js_aptzrz SET qz=1 WHERE xm=@bzqbm AND gh=@jsgh AND qz=0 AND qxtz=0
CLOSE curFj
DEALLOCATE curFj
CLOSE curJs
DEALLOCATE curJs
CLOSE curBk
DEALLOCATE curBk
CLOSE curFwxm
DEALLOCATE curFwxm
SET @ccgcfhz=1
SET @ccgcfhzms=''
END
GO
楼主试着拆了游标吧。等于推翻了现有的语句格式，重新设计下
[解决办法]
慢慢调试.蹭分.
[解决办法]
数据仓库的特点就是特大数据量，用游标死的更快，OLTP系统用游标还勉强说得过去。
[解决办法]
数据库要试着用集合的思路解决问题
