How to use Cursor inside the Store procedure

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

How to use Cursor inside the Store procedure

Author

Topic

alanhuro
Starting Member

34 Posts

Posted - 2008-07-16 : 15:05:02

Hello!

I try to write SQL 2000 store procedure to transfer the data from the DB to the asp page. I have an ASP code to read the store procedure but don't know how to write a store procedure. Can you give me a boost. To keep it simple let say I have a table like this

DateTime_______Cat________Test Value
2008-07-01______A_____________30
2008-07-02______B_____________31
2008-07-02______C_____________33

The input to SP will be begin and endDate. The output is everything from the table giving a date range.

thanks
alan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 15:08:45

I love how people want to use a cursor when a cursor is not recommended and certainly not needed here.

CREATE PROC SomeProc (@begin datetime, @end datetime)
AS
SELECT * FROM SomeTable WHERE SomeColumn ...
GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2008-07-16 : 15:16:20

Hmm, I think I am that "people" that you refer to. And I certainly was incorrect. In his case, though, I don't think his use of the word "cursor" meant the same thing. For example, in the FoxPro world (which I came from) any select statement results in a cursor, not just the type that you scan through.

But I bow to the Goddess!

Greg

alanhuro
Starting Member

34 Posts

Posted - 2008-07-16 : 16:33:01

tkizer,

Thank for your solution. It does work however the world is not perfect. I try to use the store procedure to collect the data from many different sources. You name it Industrial SqL, Oracle etc. Industrial SQL and Oracle are connected via a link server. To simplify a problem I use an example above. Basically I just want to know how to send data to the output variable. here is what I got so far

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE TestStoreProcedure2

@Test_Value int OUTPUT,
@Cat varchar(10) OUTPUT,
@StartDateTime datetime,
@EndDateTime datetime

AS
SELECT @Test_value = Test_value,
@Cat = cat
FROM Temp
WHERE Temp.DateTime >= @startDateTime
and Temp.DateTime <= @EndDateTime
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

When I run the procedure it does not return anything. Maybe you can help me fill in the missing.

Thanks
Alan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 16:38:45

Your stored procedure looks correct, so show us how you executed it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

alanhuro
Starting Member

34 Posts

Posted - 2008-07-16 : 16:50:26

Here is the code I used to test the store procedure.

Thanks

declare
@Inv int,
@Serial VARCHAR(10)

EXECUTE TestStoreProcedure2
@Inv OUTPUT,
@Serial OUTPUT,
10

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 16:54:12

Try like this:

DECLARE @Inv int, @Serial varchar(10), @begin datetime, @end datetime

SELECT @Inv = 1, @Serial = 'SomeSerial', @begin '01-01-2008', @end = GETDATE()

EXEC TestStoreProcedure2 @Inv=@Inv OUTPUT, @Serial=@Serial OUTPUT, @begindate=@begin, @enddate=@end

SELECT @Inv, @Serial

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

alanhuro
Starting Member

34 Posts

Posted - 2008-07-16 : 17:02:25

tkizer

It works but only returned one row. I think it made sense because the variable declared are not array. I think that the reason I need a cursor. Or maybe I did something wrong.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 17:12:16

For an array, you can use a csv list. Search the forums for csv solutions. You'll find splitter functions to assist with this, that way you can avoid a dreaded cursor.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 05:08:57

quote:
Originally posted by alanhuro

tkizer

It works but only returned one row. I think it made sense because the variable declared are not array. I think that the reason I need a cursor. Or maybe I did something wrong.

Thanks

Refer http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

alanhuro
Starting Member

34 Posts

Posted - 2008-07-17 : 10:01:13

Thank you guys. I will try that.

I have this example from Oracle procedure but don't know how to convert to work for SQL. I don't know if it even posible. Or do you have any cursor example?

CREATE OR REPLACE PACKAGE Temporary AS

TYPE l_number IS TABLE OF NUMBER(16,4)
INDEX BY BINARY_INTEGER;
--
TYPE l_var IS TABLE OF VARCHAR2(100)
INDEX BY BINARY_INTEGER;

PROCEDURE GET_Test_data
(
p_date1 in rm_date%type,
p_date2 in rm_date%type,
o_cat out l_number,
o_value out l_var,
)

IS

CURSOR TestCur (ValueFilter varchar2) IS -- IPSS007
SELECT cat,
value,
FROM rm_date
WHERE datetime between p_date1 AND p_date2

-----------------------------------------------------------
l_count NUMBER DEFAULT 1;
-------------------------------
BEGIN

FOR l_cursor IN TestCur (ValueFilter)
LOOP

if (l_cursor.charged > 0) OR (l_cursor.rejected > 0) then

o_cat(l_count):= l_cursor.cat;
o_value(l_count):= l_cursor.value;

l_count := l_count + 1;

end if;

END LOOP;

END;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-17 : 11:24:16

Cursors aren't bad in Oracle. They are bad in SQL Server in almost all cases, especially in the scenario you've described here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

alanhuro
Starting Member

34 Posts

Posted - 2008-07-17 : 13:04:30

I have found one of the example using cursor just in case you need it in the future.

CREATE FUNCTION dbo.fnCMN_GetProductiveTimes(@UnitId int, @StartTime datetime, @EndTime datetime)
returns @RunTimes Table(Run_Start datetime, Run_End datetime)
AS
Begin
-------------------------------------
-- Local Variables
-------------------------------------
Declare @ReasonId int
Declare @NPTimes Table(NP_Start datetime, NP_End datetime)
Declare @NP_Start datetime, @NP_End datetime, @OldNP_Start datetime, @OldNP_End datetime
---------------------------------------------
-- Get Non Productive Times
---------------------------------------------
insert into @NPTimes(NP_Start, NP_End)
select start_time, End_Time
From NonProductive_Detail nptd
Left Outer Join Event_Reason_Tree_Data ertd On (ertd.Event_Reason_Tree_Data_Id = nptd.Event_Reason_Tree_Data_Id)
Left Outer Join Event_reason_category_data ercd On ercd.Event_Reason_Tree_Data_Id = ertd.Event_Reason_Tree_Data_Id
Left Outer Join Prod_Units pu On pu.PU_Id = nptd.PU_Id
Where nptd.PU_Id = @UnitId
AND End_Time > @StartTime
And Start_Time < @EndTime
And (@ReasonId Is Null Or ertd.Event_Reason_Id = @ReasonId)
And pu.Non_Productive_Category = ercd.ERC_Id
Order By Start_Time asc
-- Seed Loop
Select @OldNP_End = @StartTime
---------------------------------------------
-- Get All Productive Times Between NP Times
---------------------------------------------
Declare MyCursor CURSOR
For ( Select NP_Start, NP_End From @NPTimes )
For Read Only
Open MyCursor
Fetch Next From MyCursor Into @NP_Start, @NP_End
While (@@Fetch_Status = 0)
Begin
If @OldNP_End < @NP_Start
insert into @Runtimes(Run_Start, Run_End)
Select @OldNP_End, @NP_Start

Select @OldNP_Start = @NP_Start, @OldNP_End = @NP_End
Fetch Next From MyCursor Into @NP_Start, @NP_End
End
Close MyCursor
Deallocate MyCursor
If @OldNP_End < @EndTime
insert into @Runtimes(Run_Start, Run_End)
Select @OldNP_End, @Endtime
--select * from @Runtimes
--/*********************************************
RETURN
END
--********************************************/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources