ref cursor - 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

General SQL Server Forums

New to SQL Server Programming

ref cursor

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-22 : 06:56:49

is there refcursor concept in sql server as it is in oracle
can we pass out parameter using cursor in sql server

i have a problem with this when migrating database from oracle to sql server

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-09-22 : 07:57:00

SQL Server supports a cursor data type:

http://msdn.microsoft.com/en-us/library/ms190498.aspx

Please note that in SQL Server, cursors are not as efficient and typically more complex as set-based solutions. Having done some Oracle-to-SQL Server migrations I would strongly recommend reworking your Oracle code to be a better fit for SQL Server. The SQL Server Migration Assistant (if you're using it) can sometimes generate some really bad code.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-22 : 07:58:45

You don't need cursors in SQL server to pass output back to caller.
Stored procedures can return output of SELECT statement as a result set which you can capture in either table variable or temp tables in the parent stored proc.

Harsh Athalye
http://www.letsgeek.net/

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-22 : 08:45:27

procedure usp_get_overtimereport( p_date in date,
p_overtimereport out sys_refcursor,
p_iserror out number)
/* test code ....

var ret1 varchar2
var retErr number
var rc refcursor

exec :ret1 := '04-Oct-2012'

exec custom.pkg_atnot_main.usp_get_overtimereport(p_date=>:ret1,p_overtimereport => :rc, p_iserror=> :retErr )
print rc;
print retErr;

*/
as
begin
p_iserror := 0;

open p_overtimereport for
select ove.eid as eid,
eue.name_complete,
eue.job_dept_name,
eue.job_grade,
to_char(ove.swipe_in_time,'HH24:MI') as intime,
to_char(ove.swipe_out_time,'HH24:MI') as outtime,
to_char(ove.swipe_in_date,'dd-Mon-yyyy') as swipe_in_date,
to_char(ove.swipe_in_date,'Day') as swipe_in_day,
to_char(ove.swipe_out_date,'dd-Mon-yyyy') as swipe_out_date,
to_char(ove.swipe_out_date,'Day') as swipe_out_day,
ove.ot_hrs,
(select name_complete
from custom.hail_ba_ess_users_extended_v
where personid = ove.approver_id ) as supervisorname,
shi.shift_desc as shift,
shi.shift_allowence
from custom.tbl_atnot_overtime ove
join custom.tbl_atnot_shiftmaster shi on(ove.shift_id = shi.shift_id)
left join custom.hail_ba_ess_users_extended_v eue on (ove.personid = eue.personid)
where eue.job_grade in ('B2')
and eue.is_active='Y'
and TO_CHAR(OVE.SWIPE_IN_DATE,'dd-mon-yyyy' )= TO_CHAR(P_DATE,'dd-mon-yyyy' )
-- and shi.shift_time_from = to_char(ove.swipe_in_time,'HH24:MI')
order by ove.eid;

exception
when others then
p_iserror := 1;
pkg_exception_handler.usp_raise_error(-20001,pkg_atnot_configs.c_application_id,'pkg_atnot_main.usp_get_overtimeatholiday');

end usp_get_overtimereport;

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-22 : 08:46:30

this code i have to migrate in sql server 2008....

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-24 : 08:03:57

harsh please give me one simple example to understand this concept

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-24 : 08:21:08

Your code can be converted to sql something like below:


procedure dbo.usp_get_overtimereport( p_date datetime, 
p_iserror int output)
begin
begin try
-- Select query here
select .... from ... 
end try
begin catch
select p_iserror=ERROR_NUMBER()
end catch
end

So now calling proc will be something like:


create proc dbo.usp_caller
begin
create table #temp
(
-- match table structure to the columns from usp_get_overtimereport select query
)

Declare err int
insert #temp
exec dbo.usp_get_overtimereport(getdate(), err output)

end

Harsh Athalye
http://www.letsgeek.net/

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-24 : 23:15:13

thanx harsh.... i will try as u per ur reply

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-25 : 02:06:39

harsh please tell me in detail about these two line code

insert #temp
exec dbo.usp_get_overtimereport(getdate(), err output)

now im facing problem with this

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-25 : 02:13:26

These lines tell sql server that you want the output of stored procedure to be inserted into a temp table.

My code won't work as is, you need to make changes to it as per your stored procedure.

Harsh Athalye
http://www.letsgeek.net/

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-25 : 23:34:10

thank u harsh .... i got the point

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-26 : 08:31:02

harsh will u please tell me how to migrate oracle procedure having xml code within sql server

code is like below...

select xmlelement("menu",
xmlagg(
xmlelement("section",
xmlattributes(ams.text as "text",
ams.url as "url",
ams.image_url as "image_url"
),
xmlforest((select xmlagg(
xmlelement("item",
xmlattributes(itm.text as "text",
itm.url as "url",
itm.image_url as "image_url"
),
)
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources