Join Cursor With Table Outside Of Cursor
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 Complete Forum Thread with Replies
Related Forum Messages:
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
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 Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
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 Replies !
Please Help Obi-Wan: Efficient Join/Cursor/Something/Anything?
Hi allI have a bit of a dilema that I am hoping some of you smart dudesmight be able to help me with.1. I have a table with about 50 million records in it and quite a fewcolumns. [Table A]2. I have another table with just over 300 records in it and a singlecolumn (besides the id). [Table B]3. I want to:Select all of those records from Table A where [table A].descriptiondoes NOT contain any of (select color from [table B])4. An exampleTable Aid ... [other columns] ... description1the green hornet2a red ball3a green dog4the yellow submarine5the pink pantherTable Bidcolor55blue56gold57green58purple59pink60whiteSo I want to select all those rows in Table A where none of the wordsfrom Table B.color appear in the description field in Table A.I.E: The query would return the following from Table A:2a red ball4the yellow submarineThe real life problem has more variables and is a little morecomplicated than this but this should suffice to give me the rightidea.Due to the number of rows involved I need this to be relevantlyefficient. Can someone suggest the most efficient way to proceed.PS. Please excuse my ignorance.CheersSean
View Replies !
One Statement Update - Join, No Cursor ?
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View Replies !
Table Name And Cursor
I am using MS-SQL Server 2K.... I have a stored procedure that contains a cursor. Right now, the table name for the cursor is "hard-coded". I would like to be able to "soft-code" the table name, because the table name may vary (although the fields would always be the same). Is this even possible? The only workaround I can think of is to use an EXEC on a real-time built SQL statement into a temp table and then cursor through the temp table. Is there a better way to do this? Thanks...
View Replies !
Cursor Inside A Cursor
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 Replies !
Cursor Or Temp Table
I just want to know which one is more efficient cursor or temp table for looping through a record set? I am basically looking for better performance and server utilization.
View Replies !
Cursor Or Temp Table
Hi Chaps, I want to write a stored procedure in which I will update the all calendar week values (stored in a table). And to achieve this I will have to loop through all the records present in calendar table. I am just wondering that should I use Cursor or TEMP table (caz of performance issues)? As it will be part of data warehouse so all the processing will be carried out at SERVER ( means no client) Can any one tell me that which would be the best solution ( Cursor or Temp table) in my case and WHY? Note: I am using SQL Server 2005 standard edition waiting for quick reply. regards, Anas
View Replies !
Cursor On A Temp Table
Hi all, I have a task of rewriting all the old procs which were build on cursors, the management does want me to remove the cursor completely since that would mean rewriting everthing.. they want to use a temp table and run a cursor on the temp table. Since there are many cursors in each proc. Any suggestions on to go about it. I am against the use of cursor any everywhere in it , even if it calls for rewriting the whole db again. I did some tests on running the old proc and the semi new proc , which runs a cursor from the temp table for the cursor query. And it even bad than the cursor only. what does this mean? Is it really a very bad idea to run cursor on a temp table? Necessity is the mother of all inventions!
View Replies !
How To Iterate Through Table Variable Without Using Cursor
Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...
View Replies !
Can't Reference Logical Table Within Cursor
Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this... DECLARE check_contact_fields CURSOR FOR SELECT field_id, column_name FROM contacts_fields OPEN check_contact_fields FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name WHILE (@@FETCH_STATUS = 0) BEGIN set @SQL = 'select ' + @column_name + ' into ##DeletedData from deleted' exec sp_executesql @SQL set @SQL = 'select ' + @column_name + ' into ##InsertedData from inserted' exec sp_executesql @SQL if (select * from ##DeletedData) <> (select * from ##InsertedData) select * from ##InsertedData FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name END CLOSE check_contact_fields DEALLOCATE check_contact_fields drop table ##DeletedData drop table ##InsertedData Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'deleted'. TIA
View Replies !
Inserting Cursor Information Into A Table
i'm currently writing an application whereby the user must be able to view the records within a table in chunks of 20, 50 or whatever. thus i need to be able to select the first 20 for example then once a 'next' button is pushed, the next 20 must be displayed. i was thinking of using a cursor to get all the records, moving absolutely to where the first row that should be displayed (fetch absolute 20 from cursor), then using the fetch next from cursor into a temporary table. is this possible and how would i got about doing it? (ie. code examples) thanx
View Replies !
Cursor From Diff Table -challenge
I have 17 tables with names as Breaktable1, Breaktable2,.... till Breaktable17 i make a query like this set nocount on Declare @sEventtable varchar(20) Declare @sstartzoneid varchar(20) Declare @ssttopzoneid varchar(20) Select @sstartzoneid ='1' Select @ssttopzoneid = '17' select @sstartzoneid = convert(smallint,@sstartzoneid) if @sstartzoneid<> 0 DECLARE Tablecursor FOR <-------> ( error here ) select status, sum(cost) FROM "breaktable " + @sstartzoneid where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003') group by status CLOSE tableCursor DEALLOCATE tableCursor can somebody help ????
View Replies !
Possible To Fetch Next From Cursor Into Table Variable?
Hello, I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like: Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table it does not work. any help would be great. thnx
View Replies !
Cursor To Store Value In Temp Table
Following sp gives wrong result whats wrong with following cursor? ALTER PROCEDURE [dbo].[spPMPT_GetProjectBenefitDetailsForAssess] @ProjectBenefitID INT AS SET NOCOUNT ON DECLARE @ErrorMsgID INT DECLARE@ErrorMsg VARCHAR(200) DECLARE @TEMP_BENEFIT TABLE (ActualQuantity INT, ExpectedQuantity INT, ActualQulity VARCHAR(2000), ExpectedQulity VARCHAR(2000) ) DECLARE @AssessBenefitID INT DECLARE @ActualQuantity INT DECLARE @ExpectedQuantity INT DECLARE @ActualQuality VARCHAR(2000) DECLARE @ExpectedQuality VARCHAR(2000) DECLARE @AssessFlag CHAR DECLARE CUR_BENEFIT CURSOR FOR SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID OPEN CUR_BENEFIT FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID INSERT INTO @TEMP_BENEFIT (ActualQuantity , ExpectedQuantity , ActualQulity , ExpectedQulity )VALUES(@ActualQuantity,@ExpectedQuantity,@ActualQuality,@ExpectedQuality) FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag END CLOSE CUR_BENEFIT DEALLOCATE CUR_BENEFIT SELECT * FROM @TEMP_BENEFIT
View Replies !
The Cursor Does Not Include The Table ... Error?
We use a lot of virtual machines. I have a base VM with SQL 2005 installed. I rename this VM (i have multiple copies running) and run an old application (VB code, iterates through recordsets, etc.). I get: Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '2K3VM-DG' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. This makes sense, and I can fix it with sp_dropserver / sp_addserver [local]. Good. The next error is puzzling though: [Microsoft][ODBC SQL Server Driver][SQL Server]The cursor does not include the table being modified or the table is not updatable through the cursor. Any suggestions on how to fix this?
View Replies !
Client Side Cursor Vs Sever Side Cursor?
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 Replies !
'One Table' Record To Separate 'two Or Three Tables' Using Cursor?
I would like to 'one table' record to separate 'two or three tables' . I just know use the DTS , try to import and export again and agian. So trouble. Could you give me some suggestions for me? For example , 'Cursor' write in new table . But I try to SQL Server Books Online which is not suitable for me solving problems. One table separate two or three tables. Can you wirte the detail example for me? Thx a lot.
View Replies !
Cursor Count Loop - Update Table
I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out. The table looks something like this. inv# SKU 1001 ABC123 1001 DEF456 1001 GHI789 1002 123DEF 1002 456GHI 1002 876HGT I need the cursor to go through and number each line, something like this. inv# SKU Line# 1001 ABC123 1 1001 DEF456 2 1001 GHI789 3 1002 123DEF 1 1002 456GHI 2 1002 876HGT 3 Any help is greatly appriciated. Thanks
View Replies !
Returning Multiple Results From A Cursor Into A Table
The problem I'm having is that I need a cursor to return multiple results into 1 .NET System.Data.DataTable, and I don't know if it's possible. Actually, the real problem I'm having is that whoever designed this database did a poor job and if it weren't for one small thing I could do this all with aggregates... But more specifically, this is what I need to accomplish. I have written a cursor that I know works - when I run it in MS SQL Query Analyzer, I get the results I want, but it's returned in multiple tables. I am maintaining a .NET application with some pretty lousy performance and I'm trying to boost it up. I need to take the cursor's results, stuff them into a DataTable, and then return that table to my web application to render. In my C# code, I am using a SqlDataAdapter to execute my query. This is what I'd like to do... /*assume query is my working cursor, InvoiceDBConnection is not null, and dt is an empty, non-null DataTable*/ SqlDataAdapter sda = new SqlDataAdapter(query, InvoiceDBConnection); sda.Fill(dt); ... but this doesn't work, and it makes sense, I guess...since the cursor returns multiple "tables" (not sure if that's actually how the data is returned). So is there any way to accomplish what I want to do? Or do I have to do it the crappy way it's done now by running my aggregate query and then using a really time-consuming for loop on my results to make the necessary adjustment? Any help is greatly appreciated
View Replies !
Does Cursor Convert Table To Read/write?
Hello, Any help here much appreciated. I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write. i suspect this is when i run a cursor through the table. Is anyone able to confirm for me whether running a cursor changes a table's properties? Many thanks. Tim Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
View Replies !
Increment In Select Query Without Cursor And Temp Table
I have: Select T0.Id_Roomtype, count(T0.ID_Room)as Total, convert(smalldatetime,'20000601') as ADate --- !!!! from rmRoom T0 where T0.id_Property = 1 group by T0.Id_Roomtype How to do: declare @x int set @x = 36901 Select T0.Id_Roomtype, count(T0.ID_Room)as Total, convert(smalldatetime,(set @x = @x+1)) as ADate --- !!! from rmRoom T0 where T0.id_Property = 1 group by T0.Id_Roomtype I am trying to increment value in Column ADate so to get new date for each row Is it possible without using cursor and temp table ? ht
View Replies !
Error: Table Schema Changed After The Cursor Is Declared
Hi, I have a package which loads data from one sql server table to another. I am loading 15million records in that. Earlier I tested that package with smaller data (less than a million) and it worked fine. So, I put it in production to load that 15 million records. But strangely after loading over 1.5million records, the job aborted with error at destination. The log says 'the table schema has changed after the cursor is declared'. But there is no change made in both destination as well as source. In my package I am using a 'OLEDB Source' to read data from a SQL Server table, using 'Script component' making some changes and loading data to a sql server table using 'OLE DB Destination'. Both source and destination are in same server, but under different schema. Do you have any idea about the problem? Thanks.
View Replies !
Putting Data Into Table B From Table A Without Using A Cursor
on SQL Server 2005, no SP. Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table. like so: declare @HTR money declare @Uniqueid varchar(15) declare cur_HTR cursor for select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module group by uniqueid open cur_htr fetch next from cur_HTR into @Uniqueid, @HTR while @@fetch_status = 0 begin update com_hpd_helpdesk_history set total_hours_to_resolve = @HTR where case_id_ = @Uniqueid and dateasat = @dateasat fetch next from cur_HTR into @Uniqueid, @HTR end close cur_htr deallocate cur_htr ... This is taking about 45 minutes each time to do 21k records. Is there a faster, better way to do this?
View Replies !
Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables
Hi , I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table, e.g. ID Email -- ---------- 1 AS1 AS11 2 AS2 AS3 AS4 AS5 3 AS6 AS7 The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g. ID EmailAddress --- ------------------ AS1 abcstu@emc.com AS2 abcstu2@emc.com AS3 abcstu3@emc.com AS4 abcstu4@em.com AS5 abcstu5@emc.com AS6 abcstu6@emc.com AS7 abcstu7@emc.com AS11 abcstu8@emc.com I need to create a stored procedure or function that: 1. Selects an Email from the first table, based on a valid ID, 2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then, 3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors? Many Thanks, probetatester@yahoo.com
View Replies !
Get &&"Invalid Cursor State&&" When Modifying A Table In SQL 2000
When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state. SP3 has been applied to SQL Server 2000. Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.
View Replies !
How Cursor Used In Asp.net
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 Replies !
Is This Possible Without A Cursor?
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 Replies !
Use Of Cursor
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 haveleft out some of the sql, but have isolated the Cursor stuff. Open MarketCursor -- How is MarketCursor loaded with data ? FETCH NEXT FROM MarketCursorINTO ItemID, @Item, @Reguest WHILE @@FETCH_STATUS = 0BEGIN DEALLOCATE MarketCursor
View Replies !
Cursor For
Hi, Declare wh_ctry_id CURSOR FOR Is "cursor for" is a function or datatype or what is this? Regards Abdul
View Replies !
Need Help With A Cursor
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 Replies !
Need Cursor Help
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 Replies !
Cursor And UDf
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 Replies !
Is Cursor Best Way To Go?
I need to get two values from a complex SQL statement which returns a singlerecord and use those two values to update a single record in a table. Inorder to assign those two values to variables and then use those variablesin the UPDATE statement, I created a cursor and used Fetch Next.... Into.This way, I only have to call the complex SQL once instead of twice.This seems like the best way to go. However, I've always used cursors forscrolling through resultsets. In this case, though, there is just a singlerecord being returned, and the cursor doesn't scroll.Is that the most efficient way to go, or is there a better way to be able touse both values from the SQL statement without having to call it twice?Thanks.
View Replies !
To Use A Cursor Or To Not Use A Cursor
I need to loop through a set of records to build a string. I can dothis without using a cursor by inserting the records into a temporarytable with an identity column. Count the number of records in thetemporary table and loop though the table selecting the values andbuilding the string where the identity column = the loop number.Is this more or less efficient than just using a cursor? If so why isit more or less efficient?Please explain in detailThank You,Jim Lewis
View Replies !
Cursor Help PLEASE!
Yes, I know that cursors are gauche, but I can't see a solution usingqueries and I'm pretty adept at them. This will be running once a dayin the wee small hours when minimal server activity will be takingplace, it will be handling 700-1000ish records. The box is a P4/SQL2000 with lots of ram and multiple CPUs.The code for creating and populating the table in question follows thecursor code.The application is for medical transport billing. We take people tothe doctor and home again and the health plan pays us. A one-waytrip, home -> doctor, is a single ‘line item'. A round trip, home ->doctor -> home is rolled up, the charge summed, and billed as a singleline item. A three-leg, home -> doctor -> pharmacy -> home, is billedas three line items.This code is just for identifying the data, once this works correctlyI'll add the code in for doing the rollup and I'm confident I canhandle that.If I ruled the world, every leg of every trip would be a single lineitem and I wouldn't have to deal with this rollup BS, but I don't makethe policy, I just have to code it. <g>A trip is a round trip if the date, account number, and passenger nameis the same and the origin street of record X equals the destinationstreet of record X+1. The problem is that X+1 could be the start of around trip and X+2 could be the completing leg of a round trip. Soyou could have a scenario of hospital -> home -> doctor -> home inwhich X is one-way and X+1 & X+2 form a round trip.BLERG! Any help is greatly appreciated, it has me stumped./*select trip_id, acct_number, passenger, street, dest_street,flat_rate, screated, smeteroff, remark1, remark2from zvoucherstodbf order by cast(screated as smalldatetime),acct_number, passenger, smeteroff*/declare @roundtrip integerdeclare @cmsg char(200)declare @Trip_ID char(11)declare @acct_number char(11)declare @passenger char(12)declare @created char(22)declare @meter_off char(22)declare @street char(32)declare @dest_street char(32)declare @remark1 char(32)declare @remark2 char(32)declare @flat_rate smallintdeclare @prev_cmsg char(200)declare @prev_Trip_ID char(11)declare @prev_acct_number char(11)declare @prev_passenger char(12)declare @prev_created char(22)declare @prev_meter_off char(22)declare @prev_street char(32)declare @prev_dest_street char(32)declare @prev_remark1 char(32)declare @prev_remark2 char(32)declare @prev_flat_rate smallintdeclare cVoucher cursor scroll forselect trip_id, acct_number, passenger, street, dest_street, screated,smeteroff, remark1, remark2, flat_ratefrom zVouchersToDBF--where screated = '12/03/03'order by screated, acct_number, passenger, smeteroffopen cVoucher--Load the @Prev_ (previous record) variables with the first recordfetch from cVoucher into @prev_trip_id, @prev_acct_number,@prev_passenger,@prev_street, @prev_dest_street, @prev_created, @prev_meter_off,@prev_remark1, @prev_remark2, @prev_flat_rate--Initialize current variablesselect @trip_id = @prev_trip_idselect @acct_number = @prev_acct_numberselect @passenger = @prev_passengerselect @street = @prev_streetselect @dest_street = @prev_dest_streetselect @created = @prev_createdselect @meter_off = @prev_meter_offselect @remark1 = @prev_remark1select @remark2 = @prev_remark2while @@fetch_status = 0--not EOFbeginselect @roundtrip = 0if (@prev_created = @created) and (@prev_acct_number = @acct_number)and (@prev_passenger = @passenger)and (@prev_street =@dest_street)-- MATCH! Apparent round tripbeginselect @roundtrip = 1select @prev_trip_id = @trip_idselect @prev_acct_number = @acct_numberselect @prev_passenger = @passengerselect @prev_street = @streetselect @prev_dest_street = @dest_streetselect @prev_created = @createdselect @prev_meter_off = @meter_offselect @prev_remark1 = @remark1select @prev_remark2 = @remark2endif (@roundtrip = 0)beginfetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateif (@prev_created = @created) and (@prev_acct_number = @acct_number)and (@prev_passenger = @passenger)and (@prev_street =@dest_street)-- MATCH! Apparent round tripselect @roundtrip = 1else--definitely one-way tripselect @roundtrip = 0fetch prior from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendif (@roundtrip = 1)beginselect @cMsg = 'Start: ' + rtrim(@prev_created) + ' ' +rtrim(@prev_trip_id) + ': ' + @prev_acct_number + ' ' +rtrim(@prev_meter_off) + ' ' + @prev_passenger + ' ' +rtrim(@prev_street) + ' to ' + @prev_dest_street + ' ' + @prev_remark1+ ' ' + @prev_remark2print @cMsgselect @cMsg = 'End: ' + rtrim(@created) + ' ' + rtrim(@trip_id) +': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + ' '+ rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +@remark2print @cMsgprint ''endelsebeginselect @cMsg = 'One-Way: ' + rtrim(@created) + ' ' + rtrim(@trip_id)+ ': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + '' + rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +@remark2print @cMsgprint ''endselect @prev_trip_id = @trip_idselect @prev_acct_number = @acct_numberselect @prev_passenger = @passengerselect @prev_street = @streetselect @prev_dest_street = @dest_streetselect @prev_created = @createdselect @prev_meter_off = @meter_offselect @prev_remark1 = @remark1select @prev_remark2 = @remark2fetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendclose cVoucher--/*--This code works for displaying the data set as a whole-- so you can visually identify what constitutes a round trip.open cVoucherfetch from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_ratewhile @@fetch_status = 0beginselect @cMsg = rtrim(@created) + ' ' + rtrim(@trip_id) + ': ' +@acct_number + ' ' + @passenger + ' ' + @street + ' ' + @dest_street +' ' + @meter_off + ' ' + @remark1 + ' ' + @remark2print @cMsgfetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendclose cVoucher--*/deallocate cVoucherCREATE TABLE [zVouchersToDBF] ([house] [char] (5) NULL ,[street] [char] (32) NULL ,[district] [char] (8) NULL ,[passenger] [char] (12) NULL ,[remark1] [char] (32) NULL ,[remark2] [char] (32) NULL ,[dest_house] [char] (5) NULL ,[dest_street] [char] (32) NULL ,[dest_district] [char] (13) NULL ,[acct_number] [char] (11) NULL ,[sub_acct_number] [char] (15) NULL ,[flat_rate] [money] NULL ,[car] [char] (3) NULL ,[driver_id] [char] (9) NULL ,[meter_on] [char] (5) NULL ,[meter_off] [char] (5) NULL ,[fare] [char] (9) NULL ,[cancelled] [char] (21) NULL ,[no_trip] [char] (7) NULL ,[no_trip_reason] [int] NULL ,[auth_number] [char] (32) NULL ,[auth_name] [char] (32) NULL ,[trip_id] [char] (7) NULL ,[created] [char] (8) NULL ,[patient_birthday] [char] (16) NULL ,[waittime] [char] (3) NULL ,[sNoTrip] [char] (22) NULL ,[sMeterOn] [char] (22) NULL ,[sMeterOff] [char] (22) NULL ,[sCancelled] [char] (22) NULL ,[sCreated] [char] (22) NULL ,[sPatientBirthday] [char] (22) NULL ,[SystemRate] [money] NULL) ON [PRIMARY]GOInsert zvoucherstodbfValues (null,'100 E 1st',null,'A','X','Y',null,'200 W2nd',null,'1000',null,5.00,null,null,null,'13:20', '5',null,null,null,null,null,'1',null,null,null,nu ll,null,'13:20',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'200 W 2nd',null,'A','X','Y',null,'100 E1st',null,'1000',null,5.00,null,null,null,'14:20', '5',null,null,null,null,null,'2',null,null,null,nu ll,null,'14:20',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'101 E 101',null,'B','X','Y',null,'202 W202',null,'2000',null,10.00,null,null,null,'13:50' ,'10',null,null,null,null,null,'3',null,null,null, null,null,'13:50',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'123 N 456',null,'C','X','Y',null,'234 S321',null,'2000',null,5.00,null,null,null,'13:51', '5',null,null,null,null,null,'4',null,null,null,nu ll,null,'13:51',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'999 N 666',null,'D','X','Y',null,'666 S999',null,'1000',null,7.50,null,null,null,'14:00', '7.5',null,null,null,null,null,'5',null,null,null, null,null,'14:00',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'666 S 999',null,'D','X','Y',null,'999 N666',null,'1000',null,8.00,null,null,null,'14:30', '8',null,null,null,null,null,'6',null,null,null,nu ll,null,'14:30',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'123 n 456',null,'E','X','Y',null,'456 s789',null,'3000',null,5.00,null,null,null,'14:30', '5',null,null,null,null,null,'7',null,null,null,nu ll,null,'14:30',null,'12/8/2003',null,null)
View Replies !
What Is A Cursor?
I had a friend write a stored procedure to perform a function for oneof my clients. What he wrote doesn't fully do what I need, and I hopeto finish it myself. I have programming sense, but not so much withSQL.I'm trying to figure out the code, and he has used something called a"cursor." I'm not sure whether this is an SQL construct or a structurethat he has just labeled "cursor." My guess is that it is an SQLconstruct. Can anyone give me a quick run down of how this works?sincerely,Tyler H.-----------------------------------------------<a href="http://www.seearoomhawaii.com/bed-breakfasts/">bed &breakfasts in Hawaii</a>
View Replies !
It Is Possible With Cursor?
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 Replies !
Can I Use SQL Instead Of Cursor?
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 Replies !
Can't Use Cursor With SP
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 Replies !
Get Value From CURSOR
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 Replies !
|
TRACKER
