Use Of Cursor

Oct 21, 2007

 

 I need some help with the concept of a Cursor, as I see it being used in
a stored procedure I need to maintain.

Here is some code from the stored proc. Can someone tell me what is going on here. I have
left out some of the sql, but have isolated the Cursor stuff.

Open MarketCursor  -- How is MarketCursor loaded with data ?

FETCH NEXT

FROM MarketCursor
INTO ItemID, @Item, @Reguest

WHILE @@FETCH_STATUS = 0
BEGIN

 

DEALLOCATE MarketCursor

 

View 1 Replies


ADVERTISEMENT

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say's that modifications is not allowed in the static cursor. I have a  questions regarding that

Static Cursor
declare ll cursor global static
            for select  name, salary from ag
  open ll
             fetch from ll
 
              while @@FETCH_STATUS=0
               fetch from ll
                update ag set salary=200 where 1=1
 
   close ll
deallocate ll

In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says  modifications is not allowed in the static cursor.But I am able to update the data using static cursor.

View 3 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.

Sep 20, 2007

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View 7 Replies View Related

Join Cursor With Table Outside Of Cursor

Sep 25, 2007

part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View 1 Replies View Related

Cursor Inside A Cursor

Oct 5, 2004

I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View 1 Replies View Related

Client Side Cursor Vs Sever Side Cursor?

Jul 20, 2005

I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View 1 Replies View Related

Need Help With A Cursor

Jul 25, 2006

I hope this is the appropriate forum for this question, if not then I apologize.
I've got a SQL Server 2000 stored procedure that returns data to be used in a crystal report in Visual Studio 2005.  Most of the stored procedure works well, but there is a point where I need to calculate an average number of days been a group of date pairs. 
I'm not familiar with cursors, but I think that I will need to use one to achieve the result I am looking for so I came up with the code below which is a snippet from my stored procedure.  In this part of the code, the sp looks at the temporary table #lmreport (which holds all of the data that is returned at the end to crystal) and for every row in the table where the terrid is 'T' (the territory is domestic), it selects all of those territories from the territory table and loops through them to determine the date averages (by calling a nested stored procedure, also included below) for each territory and then updates #lmreport with that data.
When I try to run the stored procedure, I get "The column prefix '#lmreport' does not match with a table name or alias name used in the query." on the line indicated. 
Does anyone have any idea what might be wrong or if this will even work the way I need it to?
Thank you in advance.
 

View 1 Replies View Related

Cursor For

Sep 27, 2007

Hi,
Declare wh_ctry_id CURSOR FOR 
 
Is "cursor for" is a function or datatype or what is this?
Regards
Abdul

View 3 Replies View Related

Is This Possible Without A Cursor?

Dec 5, 2007

 I have something like
 update table
set field = ...
where field = ...
 and for each entry that was effected by this query I want to insert an entry into another table.
I have always done this with cursors is there a more effecient way?  For some reason cursors run a lot slower on my sql2005 server than the sql2000 server...

View 2 Replies View Related

How Cursor Used In Asp.net

Mar 17, 2008

hii have creted cursor but i want to use in my asp.net programming when some insert or delete command is work that time i want to excute my cursor how can i do that using asp.net with c#  waiting for replaythanks 

View 4 Replies View Related

Cursor And UDf

Sep 2, 2005

Hello:
I am trying to define a cursor as follows:
 DECLARE   EmployeeList CURSOR FOR   dbo.GetRecord(@EmployeeID,@CurrentDate)Can't I use a UDF in the CURSOR FOR ?Help please.thank you.

View 18 Replies View Related

Need Cursor Help

Apr 4, 2006

Hello, I'm trying to construct a cursor that will sequentually increment a number and then update a column with the incremented number. My propblem is that all the rows in the table are being updated with the base number +1. So all rows are updated with 278301. BUT, what I really want is for only the items with adrscode of 'bill to' to be given an incremented number.
For example, if there are only five rows of 100 with an adrscode = 'bill to' then only five rows will be updated and the value of the custnmbr should be, 278301, 278302, 278303 .....
I could really use some help with this cursor:
Declare @CustomerName as char (60),     @seqno as int,     @BaseSeqno as intset @Baseseqno = 278300
declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbropen cfetch next from c into @CustomerNamewhile @@fetch_status=0begin set @seqno = @BaseSeqno + 1
update NXOFcustomers set custnmbr = @seqnoWhere custnmbr = @CustomerName                    fetch next from c into @CustomerNameend close cdeallocate c
 

View 3 Replies View Related

Cursor

Apr 20, 2001

I have a cursor defined as follows

Declare c_cursor Cursor
Scroll For Select card_id From cardcreator where card_every_cat = @cat_id
Open c_cursor
/* Scroll to the randomly selected row and populate into output parameters */
Fetch absolute @iRandomRecord From c_cursor Into @vi_cardid1
print @vi_cardid1
/*Need to check to fetch status if you have reached end of record set begin from first */
select @@fetch_status
if (@@fetch_status = 0)
Begin
Fetch next from c_cursor into @vi_cardid2
print @vi_cardid2
End
if (@@fetch_status = 0)
Begin
Fetch next from c_cursor into @vi_cardid3
End
print @vi_cardid3

/* Close and deallocate cursor */
Close c_cursor
Deallocate c_cursor

I need to have atleast three records. But if random value starts the cursor at the end, the cursor would not wrap to the beginning.

Is there a way to wrap the cursor to begining if its status is not zero.

View 1 Replies View Related

Cursor

May 22, 2001

Are the cursors declared and opened in stored procedures are always the server cursors?

View 1 Replies View Related

Cursor

Apr 13, 2000

How can I do take the select of a stored procedure from another stored procedure.
Something like that:

PROC A
Declare c Cursor For Exec B




PROC B
Select * From Clients

View 1 Replies View Related

Can I Not To Use Cursor?

Mar 18, 2003

I am sure I am not the first one ask this.
I have got two tables, what I would like to do now is to update the second table using the values in the first table where
T1.id = T2.id, normally I have to use cursor to loop through table two to achieve this. But is it possible to do this without using cursor?

Thanks,

Alan

View 2 Replies View Related

Cursor Help!

Apr 22, 1999

This is what I am trying to do:

Table 1 has numerous resume's for each person. Each resume has a unique id.
ie: Table 1
res_id fname lname userid pwd address city state etc...
100 John Doe jd ok xxxx xxxx xx xxxx
104 Sally May sm sm ccccc cc c cc ccc
643 John Doe jd ok ssss null null
1003 John Doe jd ok 123 elm Nome AK ...
5000 Tom Cat tc tc null null null

I need to insert into Table 2 only the demographic information for each person appearing in Table 1. The catch is that Table 2 doesn't have the same unique id that appears in Table 1. userid and pwd are unique to Table 2 but are numerous in Table 1.

Table 2
new_ident userid pwd address city state etc..
10 jd ok 123 elm Nome AK ....
11 Sally May sm sm ccccc cc c cc ccc
12 Tom Cat tc tc null null null


Basically I need to choose the most current "max(res_id)" occurance for John Doe above to get only one row out of his three rows. Then I need to get all the other unique rows from table 1.

I hope that is clear. I was considering a cursor. Any ideas??

Troy

View 1 Replies View Related

Cursor

Jun 17, 2002

I am trying to build a cursor based on a query that uses a variable and cant seem to make it work..

Here is the query:

declare ob_cursor cursor for
select name
from @dbname.dbo.sysobjects
where xtype = 'U'

How could I pass this cursor declaration the database name?

Any help would be appreciated.

View 1 Replies View Related

Cursor, Is It The Only Way?

May 18, 1999

Is using cursor the only way to do update in this case.
I'm updating TableA.ID with TableB.New_id where TableA.ID =
TableB.ID. TableA has 2.5 million records and TableB has 500,000
records. Doing it this way bring the system down to it's knees, and
is taking forever. Any suggestion are welcome.

declare mrn_cur cursor for
select dealer_ident, kealer_id
from dealer
for read only

declare @result int
declare @temp_ident int
declare @temp_id int
declare @temp_var int

open mrn_cur
fetch mrn_cur into @temp_ident, @temp_id

while (@@fetch_status = 0)
begin
begin transaction
update label
set dealer_id = @temp_ident
where dealer_id = @temp_id
commit tran
fetch mrn_cur into @temp_ident, @temp_id
end
close mrn_cur
deallocate mrn_cur
go

View 1 Replies View Related

Can I Do This Without A Cursor?

Jul 8, 2002

Here's what I' trying to do...

I am writing an application for a training facility. I have three tables in particular that I'm concerned with.

Here they are with the relevent keys
1. Registrations - contains a customerid and a classid
2. Sessions - contains a classid (multiple sessions can exist for a class)
3. Attendence - contains a session and a customerid.

Whenever I insert a session record for a class, I want to automatically create a corresponding record in the attendence table for every student in the class . My only thought is to create an insert trigger on the session table than creates creates a cursor containing the customerid for every student registered in the class. Then I can walk through the cursor and insert an Attendence record for each student.

I really don't want to use a cursor if I can help it, but I can't think of a way to write an single INSERT statement to put into my trigger. Is there a way to do this without using a cursor?

Any thoughts would be appreciated.
David

View 2 Replies View Related

A BUG In Cursor Or What Is Going On???

Nov 30, 1998

Hi, I have created a cursor to select 5 rows from a table , then I put the cursor in a store procedure, when I test the cursor the firt time I get correct results, but when I test it again in the same query window NOTHING happen?.... is this a BUG or am I doing something wrong, by the way, when I execute the procedure in another query window, I get it right. In conclusion, whenever I run the procedure more than once in the same query window the second try fails...but when I go to another query window it works.. I appreciate if anyone can explain why?

reagards
Ali

create procedure p_test_cursor as
DECLARE RST CURSOR
FOR SELECT counter,INVESTORID,BALANCE
FROM BALANCE
WHERE INVESTORID =300
OPEN RST
DECLARE @COUNTER INT,@INVESTORID INT, @BALANCE MONEY -- putting rst col in @
WHILE @@FETCH_STATUS <>-1 -- mean when there is no error

BEGIN
SELECT @COUNTER ,@INVESTORID,@BALANCE
UPDATE BALANCE
SET OWNERSHIP =@COUNTER
WHERE COUNTER =@COUNTER
FETCH RST INTO @COUNTER ,@INVESTORID,@BALANCE
END
CLOSE RST
DEALLOCATE RST

View 2 Replies View Related

Help With Cursor

Dec 17, 2004

hi,

i am pretty new with t-sql, cursors, and stored procs, so please bare with me. i am trying to update a table where
num1,num2,num3, and num4 represent certain percentages based on the first select counts. i am looking at the breaktimes and if those breaktimes are in certain interval, i am trying to set the breaktype field to 'SOMETHING'.

USE crap

DECLARE @BreakDate datetime(8), @ProductID int(4), @dp1 numeric(9), @dp2 numeric(9), @dp3 numeric(9),
@dp4 numeric(9), num1 numeric(9), num2 numeric(9), num3 numeric(9), num4 numeric(9), @event varchar(1024)

SET @BreakDate = '11/30/2004'
SET @ProductID= 4
SET @dp1 = 21600
SET @dp2 = 43200
SET @dp3 = 64800
SET @dp4 = 86400

SELECT COUNT(CASE WHEN BreakTime >= 0 AND BreakTime < @dp1 THEN 1 END) AS SC1,
COUNT(CASE WHEN BreakTime >= @dp1 AND BreakTime < @dp2 THEN 1 END) AS SC2,
COUNT(CASE WHEN BreakTime >= @dp2 AND BreakTime < @dp3 THEN 1 END) AS SC3,
COUNT(CASE WHEN BreakTime >= @dp3 AND BreakTime < @dp4 THEN 1 END) AS SC4
FROM DenInt
WHERE BreakType = 'SOFT'
AND BreakDate = @BreakDate
AND ProductID = @ProductID

SET num1 = SC1 * 0.1
SET num2 = SC2 * 0.1
SET num3 = SC3 * 0.1
SET num4 = SC4 * 0.1

DECLARE cCan CURSOR
FOR
SELECT *
FROM DenInt
WHERE BreakDate = @BreakDate
AND ProductID = @ProductID
ORDER BY BreakTime


OPEN cCan

FETCH cCan INTO @event

WHILE @@FETCH_STATUS = 0

BEGIN
IF num1 > 0 AND BreakTime >= 0 AND BreakTime < dp1

UPDATE DenInt
SET BreakType = 'SOMETHING'
WHERE DIGridID = DIGridID

END

CLOSE cCan

DEALLOCATE cCan


however, when i run the code above it returns

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'num1'.
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '='.
Server: Msg 137, Level 15, State 1, Line 38
Must declare the variable '@event'.


what am i doing wrong? thanks for the help in advance

View 3 Replies View Related

It Is Possible With Cursor?

Jul 5, 2004

I need to fill a cursor with 3 columns.
A want to use a Select sprocs (for re-use de code), but this sproc return 15 columns and the 3 a need was not the 3 frist. :confused:

Do I need to map the 15 columns with 15 variables locally? Or they have a way easier?


Thanks

View 3 Replies View Related

Can I Use SQL Instead Of Cursor?

Oct 6, 2004

Hi,

I'm currently converting a VB function to SQL-Server. The function uses a cursor to find the "terms of delivery" (TOD) with the highest priority.

I have a table with articlenumber, tod (and lots of other columns that doesn't matter now)

ABC123 , AFG
ABC123 , AFG
ABC123 , BGH
ABC123 , BGH
ABC123 , CDD

"CDD" has the highest priority and therefore ALL with the same articlenumber should use that tod.

The existing function uses a cursor and loops through a recordset and updates every row with the same articlenumber as the current row with the tod with the highest priority (of the ones read) with the same articlenumber.

One update per row takes "forever" to run...

I figured it would be possible to select the tod with the highest priority for one articlenumber into a temp table and then do ONE update to set the tod on all rows...

View 7 Replies View Related

Can't Use Cursor With SP

Nov 15, 2004

Hi

I have a SP and in it I call another SP which returns one row in one column, I need to concatenate the value (varchar) to the query in the first SP.
I tried to use cursor with FAST_FORWARD to fetch the result and concatenate it, but I get an error, here is what I tried:

DECLARE Cur CURSOR FAST_FORWARD
FOR SP_Something @SomeValue

So is it possible to use cursor on SP ? And if it's possible so how ???

Thanks,

Inon.

View 8 Replies View Related

Get Value From CURSOR

Mar 4, 2005

I'm writing a stored procedure that involves looping through a recordset and using the values from the recordset as parameters for a second stored procedure. Here's what I've got so far...

My question is, how do I get the value out of the Cursor? There's only one field.

Declare @Day as int
Declare @Plant as varchar(30)

SET NOCOUNT ON

CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime)

Declare Facility_Cursor CURSOR
For Select Distinct(Facility) From ProductionHistory
OPEN Facility_CURSOR
Declare @Facility_Cursor as sysname

FETCH NEXT From Facility_CURSOR into @Facility_Cursor

WHILE @@FETCHSTATUS = 1

--YESTERDAY
Set @Day = -2

Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @Day, @Facility, 'NAP'

--TODAY
SET @Day = -1
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @Day, @Facility, 'NAP'

FETCH NEXT FROM Facility_CURSOR into @Facility_Cursor

CLOSE Facility_Cursor
DEALLOCATE Facility_CURSOR

SET NOCOUNT OFF

Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESC

View 2 Replies View Related

How To Do This Without Using A Cursor?

Jan 20, 2006

I am in the last stages of designing a forecasting "engine" for my company,
and I'm stuck on something that seems simple in comparison to everything
else I've done so far.

I have product ABC, and it's total sales forecast is 15 units.
I split the forecast into 2 different locations, based on an established percentage. In this case, I'll say 67% in location 'OH', and 33% in location 'AL' That's 10 units 'OH' and 5 units 'AL'. Then I get my actual orders by location, and compare them to the forecast.

If the orders exceed the forecast, I'll use orders, otherwise, I use forecast. Whenever I do that, I need to reduce the forecast for the other location, in order to keep the total forecast of 15 whole. (It is not possible for total orders to exceed total forecast, I've already dealt with that.)


CREATE PROCEDURE tempSelect
AS

CREATE TABLE #tmpTest (
parent char(2),
proj_ship real,
open_ord real,
)

insert into #tmpTest (parent, proj_ship, open_ord)
select 'OH', 10, 4

insert into #tmpTest (parent, proj_ship, open_ord)
select 'AL', 5, 7

SELECT PARENT, 'UNITS' = ???
FROM #TMPTEST

DROP TABLE #TMPTEST
GO


I need help with '???' in the query.

The result set I am looking for is:
OH 8
AL 7

View 3 Replies View Related

Trying To NOT Use A Cursor...

Mar 3, 2006

I need to write a sproc to supply records for a report. The boss has asked
"Of all the tons on order right now, how much is already in inventory, and how much needs to be produced." And "Apply the same logic to just the orders that came in yesterday." It would have been easy, if he hadn't asked for the second part, because now I have to look at each product on each order, rather than comparing total orders for a product to total available inventory.

Here's some sample data for what I need to do:


CREATE TABLE ORDER_ITEM (
ORDER_NUM VARCHAR(10),
SHIP_DATE SMALLDATETIME,
PRODUCT VARCHAR(10),
ORD_TONS REAL)

INSERT INTO ORDER_ITEM (ORDER_NUM, SHIP_DATE, PRODUCT, ORD_TONS)
SELECT '001', '3/1/2006', 'ABC', 4 UNION ALL
SELECT '002', '3/4/2006', 'ABC', 2 UNION ALL
SELECT '002', '3/4/2006', 'DEF', 6 UNION ALL
SELECT '003', '3/7/2006', 'DEF', 8

CREATE TABLE PROD_INVENTORY (
PRODUCT VARCHAR(10),
INV_TONS REAL)

INSERT INTO PROD_INVENTORY (PRODUCT, INV_TONS)
SELECT 'ABC', 5 UNION ALL
SELECT 'DEF', 13



The final recordset needs to be something like:

PRODUCT ORDER_NUM ORD_TONS SFI SFP END_INV
ABC 001 4 4 0 1
ABC 002 2 1 1 0
DEF 002 6 6 0 7
DEF 003 8 7 1 0


SFI = Sales from Inventory
SFP = Sales from production

I need a little help in how to do a running inventory balance (END_INV)
for each item. Once I have that, then I can calculate SFI and SFP.
I could figure out how to do it with a cursor, but it
would probably be pretty slow. I'll have about 10,000 records to sort thru,
and of course there will be more columns than what I show here.

Any ideas would be appreciated.

View 4 Replies View Related

Cursor

May 6, 2006

hey all,
i am relatively new to cursors and have created a SP that uses a cursor to populate a table. here is the code

CREATE PROCEDURE sppa_invvoid
(
@invnoint
)

AS

DECLARE @glTranKey int
DECLARE @AcctRefKey int
DECLARE @BatchKey int
DECLARE @CreateDate datetime
DECLARE @CreateType smallint
DECLARE @CurrExchRate float
DECLARE @CurrID varchar (3)
DECLARE @ExtCmnt varchar (255)
DECLARE @FiscPer smallint
DECLARE @FiscYear varchar (5)
DECLARE @GLAcctKey int
DECLARE @JrnlKey int
DECLARE @JrnlNo int
DECLARE @PostAmt decimal(15, 3)
DECLARE @PostAmtHC decimal(15, 3)
DECLARE @PostCmnt varchar (50)
DECLARE @PostDate datetime
DECLARE @PostQty decimal(16, 8)
DECLARE @SourceModuleNo smallint
DECLARE @TranDate datetime
DECLARE @TranKey int
DECLARE @TranNo varchar (10)
DECLARE @TranType int
DECLARE @Companyid varchar(3)
DECLARE @Batchtype int
DECLARE @Userid varchar (30)
DECLARE @Moduleno int
DECLARE @NextBatchNo int
DECLARE @_oRetVal int
DECLARE @iTableNamevarchar(50)
DECLARE @iCommitFlag int
DECLARE @NextJrnlNo int


Set @CompanyID= 'EMA'
Set @JrnlNo = 139
Set @iCommitFlag = 1
Set @JrnlKey = 193
Set @iTableName='tgltransaction'
Set @batchtype = 501
Set @moduleNo = 5
Set @Userid = 'Admin'



EXECUTE spGetNextBatchNo @CompanyID, @BatchType, @UserId, @ModuleNo, @BatchKey OUTPUT, @NextBatchNo OUTPUT, @_oRetVal OUTPUT

Execute spglGiveNextJrnlNo @CompanyID, @JrnlKey, @iCommitFlag, @JrnlNo, @NextJrnlNo OUTPUT




DECLARE cursor_tran CURSOR FOR

select glTranKey, AcctRefKey,CreateDate,CreateType,CurrExchRate,Curr ID,ExtCmnt,FiscPer,FiscYear,GLAcctKey,JrnlKey,Jrnl No,PostAmt,PostAmtHC,PostCmnt,PostDate,PostQty,Sou rceModuleNo,TranDate,TranKey,TranNo,TranType
from tgltransaction where tranno = @invno

OPEN cursor_tran

FETCH NEXT FROM cursor_tran INTO

@glTranKey,
@AcctRefKey,
@CreateDate,
@CreateType,
@CurrExchRate,
@CurrID,
@ExtCmnt,
@FiscPer,
@FiscYear,
@GLAcctKey,
@JrnlKey,
@JrnlNo,
@PostAmt,
@PostAmtHC,
@PostCmnt,
@PostDate,
@PostQty,
@SourceModuleNo,
@TranDate,
@TranKey,
@TranNo,
@TranType





WHILE (@@FETCH_STATUS <> -1)

BEGIN

Execute spgetnextSurrogateKey @iTablename , @glTranKey OUTPUT
/*Execute aaaa*/





INSERT INTO tgltransaction
(glTranKey,
AcctRefKey,
BatchKey,
CreateDate,
CreateType,
CurrExchRate,
CurrID,
ExtCmnt,
FiscPer,
FiscYear,
GLAcctKey,
JrnlKey,
JrnlNo,
PostAmt,
PostAmtHC,
PostCmnt,
PostDate,
PostQty,
SourceModuleNo,
TranDate,
TranKey,
TranNo,
TranType)

Values

(@glTrankey,
@AcctRefKey,
@BatchKey,
@CreateDate,
@CreateType,
@CurrExchRate,
@CurrID,
@ExtCmnt,
@FiscPer,
@FiscYear,
@GLAcctKey,
@JrnlKey,
@JrnlNo,
@PostAmt,
@PostAmtHC,
@PostCmnt,
@PostDate,
@PostQty,
@SourceModuleNo,
@TranDate,
@TranKey,
@TranNo,
@TranType)




FETCH NEXT FROM cursor_tran INTO

@glTranKey,
@AcctRefKey,
@CreateDate,
@CreateType,
@CurrExchRate,
@CurrID,
@ExtCmnt,
@FiscPer,
@FiscYear,
@GLAcctKey,
@JrnlKey,
@JrnlNo,
@PostAmt,
@PostAmtHC,
@PostCmnt,
@PostDate,
@PostQty,
@SourceModuleNo,
@TranDate,
@TranKey,
@TranNo,
@TranType



END
CLOSE cursor_tran
DEALLOCATE cursor_tran
GO


the issue that i am having is after i do the insert, the cursor picks up on the inserted row and it ultimately becomes an infinate loop. what can i do to prevent it from picking up the newly inserted rows. thanks alot

tibor

View 3 Replies View Related

SQL Cursor?

Dec 7, 2006

Hi all,

I have two tables with

IMPORT_TABLE (Imported from MS Excel) <-- Source table
Name
Address
City
State
Zip

CONTACT_TABLE: <-- Target Table
Full_Name
Address_Line1
City
State
Zip


I have to match the name from the Import table with the full_name in Contact table, if it matches, i have to update address / city / state / zip information in the Contact table if and only if corresponding columns in the target table (Contact table) is empty. If the name does not match, i have to insert a new record in contact table.

Should i use SQL cursor? If so could any one provide me with a sample solution?

Thanks in Advance,
Newbie

---------------------------------------------
SQL 2000 on Windows 2000 Server

View 3 Replies View Related

Using Cursor

Apr 30, 2008

Using SQL2005. I need to create a cursor to update a field in a table with current date by Invoice Number. I get an endless loop and record is not updated. Where did I go wrong. David

DECLARE

@GRGR_ID CHAR(10),
@SBSB_ID CHAR(10),
@INVOICE_DUE_DATE DATETIME,
@INVOICE_TOTAL_AMOUNT_DUE MONEY,
@INVOICE_NUMBER CHAR(12),
@PASS1_GENERATION_DATE DATETIME,
@PASS1_TOTAL_AMOUNT_DUE MONEY,
@PASS1_END_OF_GRACE DATETIME



DECLARE DEQ CURSOR FOR
SELECT
GRGR_ID,
SBSB_ID,
INVOICE_DUE_DATE,
INVOICE_TOTAL_AMOUNT_DUE,
INVOICE_NUMBER,
PASS1_GENERATION_DATE,
PASS1_TOTAL_AMOUNT_DUE,
PASS1_END_OF_GRACE

FROM dbo.RPT_DELINQUENCY_TEST WHERE INVOICE_NUMBER = '080710019183'

OPEN DEQ

FETCH DEQ INTO
@GRGR_ID,
@SBSB_ID,
@INVOICE_DUE_DATE,
@INVOICE_TOTAL_AMOUNT_DUE,
@INVOICE_NUMBER,
@PASS1_GENERATION_DATE,
@PASS1_TOTAL_AMOUNT_DUE,
@PASS1_END_OF_GRACE

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE dbo.RPT_DELINQUENCY_TEST SET @PASS1_GENERATION_DATE = GETDATE()
WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID AND INVOICE_NUMBER=@INVOICE_NUMBER

END

CLOSE DEQ
DEALLOCATE DEQ

Thank for your help.

View 7 Replies View Related

Msg 512 On Cursor

Jan 6, 2004

Everytime I execute this procedure it gives a msg 512 subquery returns more then one value. Can someone just explain why or what I'm doing wrong? Thanks in advance

Here is my code:

create procedure insert_sku_info
AS
Declare
@method varchar(40),
@sku int,
@location varchar(40)

Declare insert_cur Cursor For
select a.method, s.sku, s.location
from archive_sku a INNER JOIN sku s on a.sku = s.sku
and a.location = s.location

Open insert_cur

Fetch Next from insert_cur
Into @method, @sku, @location

While (@@Fetch_Status = 0)
Begin

print @method + @sku + @location

Update sku
set method = @method
where sku = @sku
and location = @location

Fetch Next from insert_cur
Into @method, @sku, @location

End
close insert_cur
deallocate insert_cur

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved