ref cursor in t-sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ref cursor in t-sql

Author

Topic

zahid.sabreen
Starting Member

14 Posts

Posted - 2011-02-01 : 01:14:41

Can you actually write a ref cursor for a stored procedure in T-SQL?what i know is it can only be written in oracle.

Please provide inputs.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-01 : 01:29:55

well I dont know if some one can help you unless he/she has a good knowledge of Oracle :D

Any how if you could provide details of your requirements with the help of an example (input and desired output). so might be able to give some idea with reference to the SQL server!

zahid.sabreen
Starting Member

14 Posts

Posted - 2011-02-01 : 03:13:26

below is the stored procedure and would like to know if we can write a
ref cursor for this as that is the need in my project ,so that this code can be used by /exported to any application.

USE [EAS_APJK12]
GO
/****** Object: StoredProcedure [dbo].[sp_approve_vacation_from_bb] Script Date: 02/01/2011 13:39:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_approve_vacation_from_bb]
@iv_employee_id varchar(10),
@iv_approver_id varchar(10),
@id_startdate datetime,
@id_enddate datetime,
@iv_approve_YN varchar(1),
@oi_ReturnValue int output
AS BEGIN
DECLARE @i_count int

SELECT @i_count = COUNT(*)
FROM T_EMPLOYEE_VACATION tev
WHERE (tev._SUBMIT_FLAG = 1)
AND (tev._APPROVE_CLOSE_FLAG <> '1')
AND (tev._REJECT_FLAG = 0)
AND (tev._EMPLOYEE_ID = @iv_employee_id)
AND tev._VACATION_STARTDATE = @id_startdate
AND tev._VACATION_ENDDATE = @id_enddate

if (@i_count > 0) and (@iv_approve_YN = 'Y')
begin
Update T_EMPLOYEE_VACATION
set _APPROVE_CLOSE_FLAG = 1,
_EMPLOYEE_APPROVER_ID = @iv_approver_id,
_STAMP_MODIFY_DATE = getdate(),
_STAMP_MODIFY_NAME = user
Where (_SUBMIT_FLAG = 1)
AND (_APPROVE_CLOSE_FLAG <> '1')
AND (_REJECT_FLAG = 0)
AND (_EMPLOYEE_ID = @iv_employee_id)
AND _VACATION_STARTDATE = @id_startdate
AND _VACATION_ENDDATE = @id_enddate
Select @oi_ReturnValue = 1
RETURN
end
else if (@i_count > 0) and (@iv_approve_YN = 'N')
begin
Update T_EMPLOYEE_VACATION
set _REJECT_FLAG = 1,
_EMPLOYEE_APPROVER_ID = @iv_approver_id,
_STAMP_MODIFY_DATE = getdate(),
_STAMP_MODIFY_NAME = user
Where (_SUBMIT_FLAG = 1)
AND (_APPROVE_CLOSE_FLAG <> '1')
AND (_REJECT_FLAG = 0)
AND (_EMPLOYEE_ID = @iv_employee_id)
AND _VACATION_STARTDATE = @id_startdate
AND _VACATION_ENDDATE = @id_enddate
Select @oi_ReturnValue = 2
RETURN
end
else if (@i_count = 0)
begin
Select @oi_ReturnValue = 0
RETURN
end
END

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-01 : 03:59:07

I'm confused...there is no looping in your procedure...? You do have cursors in t-sql as well:
http://msdn.microsoft.com/en-us/library/ms180169.aspx

I really don't know very much about oracle but I *think* that a table variable is what resembles an oracle cursor the most. A table variable is basically a variable like any other that can be passed between procedures, etc. It's created like this:

DECLARE @myTableVar table(
  ID int,
  ...)
INSERT INTO @myTableVar
SELECT ID, ... FROM someTable WHERE...

- Lumbago
My blog-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources