שימוש ב CURSOR ו- FETCH
Use of CURSOR in SQL
USE [database_name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Igor Margulyan
--
-- exec stored_procedure_name
-- =============================================
ALTER PROCEDURE [dbo].[sp_stored_procedure_name]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @lot nvarchar(7)
-- Define new cursor
DECLARE tbl_cursor CURSOR FOR
select distinct customerlot from tblTraceTable
-- clear temp table
delete from tblRejectTable_Temp
-- open cursor
OPEN tbl_cursor
WHILE @@FETCH_STATUS = 0 -- ok
BEGIN
-- push into temp table
INSERT INTO tblRejectTable_Temp exec sp_RejectRateMain @lot
FETCH NEXT FROM tbl_cursor INTO @lot
END
-- close curcor
CLOSE tbl_cursor
DEALLOCATE tbl_cursor
SELECT * FROM tblRejectTable_Temp
END
===================================================================================
דוגמה נוספת
USE [datatbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:
-- select dbo.Test
-- =============================================
ALTER FUNCTION [dbo].[Test]
(
@var1 varchar(17),
@var2 varchar(17)
)
RETURNS varchar(1000)
AS
BEGIN
declare @var3 as varchar(17)
declare @var4 as varchar(1000)
declare @i as int
declare crs cursor for
select rtrim(ltrim(t_loca))
from database2.dbo.main_table
where ltrim(t_item) = ltrim(@item) and ltrim(t_cwar) = ltrim(@cwar)
and t_strs <> 0
group by rtrim(ltrim(t_loca))
open crs
fetch next from crs into @location
set @i=1
set @locations=''
while @@fetch_status=0
begin
if @i=1
set @locations = @locations + @location
else
set @locations = @locations + ',' + replace(@location, ',', '')
fetch next from crs into @location
set @i = @i + 1
end
close crs
deallocate crs
return @locations
END