ORDER BY Have Problem In SP With Cursor
Hi,
when I create a stored procedure with cursor, I got this problem: if I have ORDER BY in the code, I got below error message. It change to OK once I remove the ORDER BY.I tried to run the SELECT with ORDER BY in seperated statement, works just fine.I cannot understand what the error message mean, either google found nothing. What's the problem, or how I debug out the reason. Thank you very much.
here is the code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Lookup_A]
(
@projId varchar(10)
)
AS
BEGIN
-- Lot Number
DECLARE rsLotnos CURSOR FOR
(SELECT LOT_NUMBER
FROM ASG_LOT
WHERE PROJECT_ID = @projId
ORDER BY LOT_NUMBER
)
END
here is error message:
Msg 156, Level 15, State 1, Procedure SP_Lookup, Line 17
Incorrect syntax near the keyword 'ORDER'.
Wes
View Complete Forum Thread with Replies
Related Forum Messages:
Cursor And Order By
I noticed that I cannot use ORDER BY in cursor, but in my case I need to order data in cursor. I'm thinking about selecting data for cursor from temp table where data is ordered but may be there are other decisions to get data in cursor ordered?
View Replies !
Cursor Issue - Order Not Correct
I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records. I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc. In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode). Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed. /*CREATE Sales_Table table */ CREATE TABLE [dbo].[Sales_Table] ( [count_id] [int] NULL , [org_id] [int] NULL , [postalcode] [varchar] (20) NULL, [sales] [numeric](18,6) NULL , [sales_rank] [integer] NULL, [org_criteria_input_date] [datetime] NULL ) ON [PRIMARY] insert into Sales_Table select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date fromorg_model_data omd join org o on o.org_id = omd.org_id where o.client_id = @ClientID and model_Criteria_id = 27 and org_criteria_value <> 0 order by omd.org_id asc, omd.org_criteria_value desc -- DECLARE CURSOR for Sales_Table declare SalesRankCursor CURSOR SCROLL dynamic FOR select org_id, sales, sales_rank from Sales_Table for update of sales_rank -- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values OPEN SalesRankCursor while exists (Select * from Sales_Table where sales_rank = 0) Begin FETCH NEXT FROM SalesRankCursor set @StoreNext = @StoreCurrent set @SalesRank = (@SalesRank + 1) update Sales_Table set@StoreCurrent = org_id where current of SalesRankCursor if @StoreCurrent <> @StoreNext begin set @SalesRank = 1 end update Sales_Table setsales_rank = @SalesRank where current of SalesRankCursor End CLOSE SalesRankCursor DEALLOCATE SalesRankCursor Any ideas?
View Replies !
Sorting Cursor Output WITHOUT Using Order By
Hi, I have a situation where I need to sort the output of a cursor. But since the sort criteria are rather complex, I am NOT able to use the Order By clause directly with the cursor definition statement. Hence, I need to have a solution where I will use a dummy (calculated) field within the CURSOR and I want the output of this cursor sorted by the dummy field that I calculated within the cursor itself. Please let me know the different possibilities in this scenario. Thank you in advance Raj
View Replies !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
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 !
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following... SQL Server Express edition Visual Studios 2005 Jet 4.0 newest upgrade .Net Framework 2.0 (or should I use 3.0) VS2005 Security upgrade Anything else I need for just creating a database for my VS2005 Visual Basic project? I was trying to use MS Access as my backend db but would like to try SQL Express Thank you, Mark
View Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Hey guys, i need to find out how can i add order items under a Purchase Order number. My table relationship is PurchaseOrder ->PurchaseOrderItem. below is a Stored Procedure that i have wrote in creating a PO: CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT) AS BEGIN INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID) END SET @POno = @@IDENTITY RETURN However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem? CREATE TRIGGER trgInsertPOItem ON PurchaseOrderItem FOR INSERT AS BEGIN 'What do i entered???' END RETURN help is needed asap! thanks!
View Replies !
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 Replies !
Find Order By Date Range Or Order Id
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
View Replies !
Recordset's Order And Database's Physical Order?
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
View Replies !
Default Sort Order When Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek: Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name. Product: Microsoft SQL Server Express Edition Version: 9.00.1399.06 Server Collation: SQL_Latin1_General_CP1_CI_AS for example, create table test_sort ( description varchar(75) ); insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons'); then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75)); select * from test_sort order by cast( description as nvarchar(75)); Resultset1 ---------- Non-A Non-O Noni Nons Resultset2 ---------- Non-A Noni Non-O Nons Any ideas?
View Replies !
Order By Clause In View Doesn't Order.
I have created view by jaoining two table and have order by clause. The sql generated is as follows SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth If I run sql the results are displayed in proper order but the view only order by first item in order by clause. Has somebody experience same thing? How to fix this issue? Thanks,
View Replies !
Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table. My query would look something like this: Select leadershipName From Leadership Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows: Executive Board Members, Delegates, Grievance Chairs, and Negotiators My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC? Thanks, Zoop
View Replies !
Order ID For Latest Order For Every Customer
Hi! For the Orders table (let's assume for the Northwind database), I'm trying to get the order id of the latest order for every customer. That means that the result should be one record per customer and that would display CustomerID and OrderID. Any ideas? Thanks, Assaf
View Replies !
In-Order/Level Order Etc. Traversal Using CTE
Hi, I have some hierarchical data in a table. Say for example: Parent Child ------------------------ NULL 1 1 2 1 3 2 4 2 5 3 6 3 7 5 8 5 9 7 10 7 11 11 12 11 13 Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13... What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well. with Tree (id) as ( select id from WithTest where parent is null union all select a.id from Tree b join WithTest a on b.id = a.parent ) select * from Tree Any ideas? 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 !
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 !
How To Load A Unicode File Into The Database In The Same Order As The File Order
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View Replies !
Force The &"ORDER BY&" To Be In Ascending Order??
I noticed the StockDate is not sorted in proper order, like ascending order... Code: select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd... The sample data is MM/DD/YYYY... So, how do I get it ordered propery by Year, Month then Day??
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 !
How To Do This Without Using A Cursor?
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 Replies !
Trying To NOT Use A Cursor...
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 Replies !
Cursor
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 Replies !
SQL Cursor?
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 Replies !
Using Cursor
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 Replies !
Msg 512 On Cursor
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 Replies !
Cursor Help Please
Hi all! I just need some help with cursors, a topic I don't profess to be an expert in. I've got two tables with a 1-many relationship between them. Let's say they're "tblCustomers" and "tblOrders". tblCustomers data: CustomerID Name 1 Fred 2 Charlie 3 Lucy tblOrders data: OrderID CustomerId Qty 1 1 10 2 1 5 3 1 20 4 2 8 5 3 20 6 3 6 I need to return a result set that puts all the "many" records into a single row, like: Name Qty Fred 10, 15, 20 Charlie 8 Lucy 20, 6 THANKS IN ADVANCE!!!!!!!!!!
View Replies !
Do I Really Need A Cursor?
I've built an application to import transactions into the database. Bad transactions go in a separate table and dupe transactions get updated. Currently, it takes about 2 hours to import ~40K records using the code below. Obviously I'd like this to run as fast as possible and since cursors are a real drag I was wondering if there was a more efficient way to accomplish this. DECLARE @contact_id int, @product_code char(9), @status_date datetime, @business_code char(4), @expire_date datetime, @prod_status char(4), @transaction_id int, @emailAddress varchar(50), @journal_id int BEGIN TRAN DECLARE transaction_import_cursor CURSOR FOR SELECT transaction_id, product_code, emailAddress, status_date, business_code, expire_date, prod_status from transactions_batch_tmp OPEN transaction_import_cursor FETCH NEXT FROM transaction_import_cursor INTO @transaction_id, @product_code, @emailAddress, @status_date, @business_code, @expire_date, @prod_status WHILE (@@FETCH_STATUS = 0) BEGIN SELECT top 1 contacts.contact_id AS contact_id, transactions_batch_tmp.status_date AS status_date, transactions_batch_tmp.product_code AS product_code, transactions_batch_tmp.business_code AS business_code, transactions_batch_tmp.expire_date AS expire_date, transactions_batch_tmp.prod_status AS product_status FROM transactions_batch_tmp INNER JOIN journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id ON transactions_batch_tmp.emailAddress = contacts.emailAddress AND transactions_batch_tmp.product_code = journal.product_code INNER JOIN products ON transactions_batch_tmp.product_code = products.product_code WHERE rtrim(ltrim(contacts.emailAddress)) = @emailAddress AND journal.product_code = @product_code ORDER BY transactions_batch_tmp.status_date desc IF @@ROWCOUNT = 0 BEGIN print 'NEW transaction! ' + @product_code + @emailAddress insert into journal (contact_id, product_code, status_date, business_code, expire_date, entryTypeID, product_status, date_entered) SELECT distinct rtrim(ltrim(contacts.contact_id)) as cid, rtrim(ltrim(products.product_code)), transactions_batch_tmp.status_date, rtrim(ltrim(transactions_batch_tmp.business_code)) , transactions_batch_tmp.expire_date, 21, rtrim(ltrim(transactions_batch_tmp.prod_status)), getDate() FROM contacts INNER JOIN (transactions_batch_tmp INNER JOIN products ON transactions_batch_tmp.product_code=products.produ ct_code) ON contacts.emailAddress=transactions_batch_tmp.email Address WHERE transactions_batch_tmp.transaction_id=@transaction _id END ELSE BEGIN --print 'UPDATE transaction! ' + @product_code + @emailAddress UPDATE journal SET status_date = (SELECT max(tmp.status_date) FROM transactions_batch_tmp tmp, contacts c, products p, journal j WHERE tmp.emailaddress = @emailAddress AND tmp.emailaddress = rtrim(c.emailaddress) AND c.contact_id = j.contact_id AND j.product_code = @product_code AND j.product_code = tmp.product_code) FROM transactions_batch_tmp tmp, contacts c, products p, journal j WHERE tmp.emailaddress = @emailAddress AND tmp.emailaddress = rtrim(c.emailaddress) AND c.contact_id = j.contact_id AND j.product_code = @product_code AND j.product_code = tmp.product_code END FETCH NEXT FROM transaction_import_cursor INTO @transaction_id, @product_code, @emailAddress, @status_date, @business_code, @expire_date, @prod_status END CLOSE transaction_import_cursor DEALLOCATE transaction_import_cursor COMMIT TRAN /** purge data from temp error table before writing bad records for this batch **/ truncate table tran_import_error; /** write bad records (missing product code or email address) to temp_error table **/ insert into tran_import_error (transaction_id, product_code, emailAddress, date_entered) SELECT DISTINCT transactions_batch_tmp.transaction_id, transactions_batch_tmp.product_code, transactions_batch_tmp.emailAddress, getDate() FROM transactions_batch_tmp where transactions_batch_tmp.emailaddress not in (select emailaddress from contacts) OR transactions_batch_tmp.product_code not in (select product_code from products) TIA
View Replies !
A BUG In Cursor Or What Is Going On???
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 Replies !
Cursor, Is It The Only Way?
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 Replies !
Cursor Help!
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 Replies !
|
TRACKER
