SQL存储过程游标调用存储过程示例

最新推荐文章于 2023-11-29 21:47:57 发布

guyongqing52

最新推荐文章于 2023-11-29 21:47:57 发布

阅读量2.8k

点赞数

文章标签：数据库存游标 SQL 存储过程

本文链接：https://blog.csdn.net/guyongqing52/article/details/8688246

版权

if(object_id('proc_temp','p') is not null)
DROP PROCEDURE proc_temp
go 
CREATE PROCEDURE proc_temp
with recompile
AS 
 SELECT * FROM student;
GO
EXECUTE proc_temp;

IF(object_id('GetMasterGoods','p') is not null)
DROP PROCEDURE GetMasterGoods
GO 
CREATE PROCEDURE GetMasterGoods
@MyCursor CURSOR VARYING Output
WITH encryption
AS set @MyCursor=CURSOR FOR 
  SELECT Select GoodsCode,GoodsName From Master_Goods 
OPEN @MyCursor
GO 

IF(object_id('GetAllGoodsIDAndName','p') is not null)
CREATE PROCEDURE GetAllGoodsIDAndName
AS 
DECLARE @GoodsCode VARCHAR(18)
DECLARE @GoodsName VARCHAR(20)
DECLARE @MasterGoodsCursor CURSOR
EXECUTE GetMasterGoods @MasterGoodsCursor OUT
FETCH Next FROM @MasterGoodsCursor
INTO @GoodsCode,@GoodsName
WHILE(@@Fetch_Status=0)
  BEGIN Print Print @GoodsCode + ':' + @GoodsName 
    FETCH Next FROM @MasterGoodsCursor
    INTO @GoodsCode,@GoodsName
End
CLOSE @MasterGoodsCursor
DEALLOCATE @@MasterGoodsCursor
GO