Unique Cursor Name In Recursive Stored Procedure
How can I solve this probleM?
I thing that I need to create cursor with unique cursor name
procedure XX param
DECLARE cc CURSOR FOR SELECT....param --ERROR bcause it's already exists
OPEN cc
...
WHILE @@FETCH_STATUS = 0
BEGIN
XX param
FETCH NEXT ...
END
CLOSE cc
....
View Complete Forum Thread with Replies
Related Forum Messages:
How To Convert Recursive Function Into Recursive Stored Procedure
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
View Replies !
Recursive Stored Procedure?
How do I call my Stored Procedure recursively:CREATE PROCEDURE dbo.GetParentIONode(@IONodeID int,@FullNodeAddress char(100) OUTPUT)ASBEGINDECLARE @ParentIONodeID intIF EXISTS (SELECT ParentIONodeID FROM IONodes WHERE IONodeID = @IONodeID)BEGINSET @FullNodeAddress = CAST((SELECT ParentIONodeID FROM IONodes WHEREIONodeID = @IONodeID) AS VARCHAR) + ' / ' + @FullNodeAddress--CALL SP Again with @ParentIONodeID and @FullNodeAddress untilParentIONodeID = NULLSELECT @FullNodeAddressENDENDGO
View Replies !
Stored Recursive Procedure
Hi, I would like some help on a stored recursive procedure I have been working on for my assigment. I had the solution from my course master some time back, but accidentally deleted the file before finishing the course, and would like the solution to that problem. The questions are as follows: 1) Create a recursive stored procedure that counts from 1 to 10 2) Create a recursive stored procedure that counts from 10 to 1 I believe there's more than one approach in solving these procedures, and any feeback is welcome. cheers
View Replies !
'Recursive Stored Procedure In SQL SERVER
Hi, I have 2 SQL SERVER tables MSTHDRML (Header table) & MSTDTLML(details Table) MSTHDRML MLID int 4 MLITemID int 4 ConcatStringvarchar 20 EffectiveDateFromsmalldatetime EffectiveDateTodatetime MSTDTLML MLID int40 ItemID int40 ConcatStringvarchar201 Qty money81 The MLID in the header table will be generated automatically.All the Parents will be stored in the HEADER and their childs in the DETAIL.When a child is added to a parent,the Parent's MLID will be stored in the MLID field in the DETAIL table with the newly added child.That child will come to the PARENT table when a child is added to that.The MLITEM id in the parent table can be repeated when that item undergoes a rivision.But the MLID for this will be a new one.An item in the Parent Table can have any number of childs and these childs can have any number of children(there is no limit for the level.) Some Sample Data MSTHDRML -------------- MLIDMLITemID ConcatStringEffectiveDateFromEffectiveDateTo 11000 56V 01/06/200331/12/9999 21003 Red 01/08/200331/12/9999 31001 01/08/200331/12/9999 41007 01/08/200331/12/9999 51008 01/08/200331/12/9999 61002 01/08/200331/12/9999 71005 01/08/200331/12/9999 82000 01/08/200331/12/9999 MSTDTLML -------------- MLIDItemIDConcatStringQty 11001Round 10 11002Square 20 21004Blue 19 11005Green 22 31007Flat 223 41008 100 51009 200 61010 11 71011 22 71010 45 71012 454 82001 5 Now if i select an item id '1000' (for example from the Header Table) with a concatstring (it could be without a concat string also).all its childs and their children should be printed in a report like the following 1000 | --- 1001 | | | --1007 | |_ 1008 ----1002 |__1009 | |_1010 | ----1005 ************************************************** *************************** I NEED TO CREATE THE TREE USING BOTH THE HEADER(MSTHDRML) AND THE DETAIL TABLE(MSTDTLML) ************************************************** *************************** How can this be done.Is it necessary to use a recursive function in a stored procedure to generate this ...... i have never used Recursive function in SQL SERVER Stored Procedures.Can anyone help me on this(with Code).if not stored procedure, then what else can be done for this.
View Replies !
Recursive Calls To Stored Procedure
I need to develope a stored procedure (eventually called by a trigger) that creates a record in an event table for all the descendants of a drawing. There are 3 tables involved as example tables shown below: DwgTable DwgID (integer)--drawing record identifier PrntDwgID (integer)--parent drawing record identifier (a previously defined DwgID from this table) DwgEventTable DwgEventID (integer)--record identifier DwgID (integer)--value from DwgTable EventID (integer)--value from EventTable EventTable EventID There are other fields in two of the tables and only the fields shown in the DwgEventTable, but only the fields shown are required for adding a record in the DwgEventTable for a new event in the EventTable. The problem is identifying all the DwgID's of the descendant of the DwgID where the EventID occurred. There may be 0 to N descendants in 0 to N generations. I need to add a record for the original DwgID and all the descendant DwgID's in the DwgEventTable for the event identified by EventID. I could do this from the client side, but a better place would be from the server side. I need some clue(s) on how to start coding a recursive stored procedure in SQL Server 2005. From what I have read, you cannot create a managed code procedure that appends or updates records--if managed code can add/modify records then I can do the above with managed code procedure. Any Suggestions?
View Replies !
Recursive Stored Procedure To Populate Tree
I apologize if I posted in the wrong section, but I cannot find a solution to this. I was hoping that some one can help me figure this out. I need this solution in a stored procedure VS using dataset because there are thousands of categories and it is extremely slow with a dataset. I have a category table ID intPARENTID intNAME SAMPLE DATA: ID NAME PARENT_ID 1 ANIMALS 0 2 DOGS 1 3 CATS 1 4 Abyssinian 3 5 Persian 3 6 Rurkish Van 3 7 Dalmation 2 8 German Shepherd 2 9 Irish Setter 2 10 Bulldog 2 I need the stored proc to return results in a single record set like this: ANIMALS - DOGS - - Dalmation - - German Shepherd - - Irish Setter - - Bulldog - CATS - - Abyssinian - - Persian - - Turkish Van Seems fairly easy but I have been battling with this for days now. Thank you in advance, EL
View Replies !
Write A Stored Procedure Based On Recursive Data.
Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved. John Smith - Jacob Jones - Lisa Thompson - Samuel Barber - Paul Smith - John Jackson Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber. Here are the tables: Users holds the user_id, first_name, last_name, and reports_to_user_id. User_Roles holds the user_id, role_type_id Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest. Thanks very much in advance, Saied
View Replies !
How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source? Something like this: CREATE PROCEDURE TestHardDisk AS BEGIN DECLARE CURSOR HardDisk_Cursor FOR Exec xp_FixedDrives -- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source OPEN CURSOR HardDisk_Cursor FETCH NEXT FROM HardDisk_Cursor INTO @Drive, @Space WHILE @@FETCH_STATUS = 0 BEGIN ... END END
View Replies !
Recursive Cursor
Hi, I have 2 tables tblparent parent_term_id term_id -------------- ----------- 1 2 2 3 tblname id name ----- ------------------------------------------ 1 My top parent node 2 My second node 3 my child node If I do a search for say 'my child node' I need to display where 'my child node' is in relation to the hierarchy. i.e i need to show it's parent and if that has a parent I need to show its parent etc... and continue until there are no more parents left So using the table details if i search for 'my child node' I need to display this : My top parent node -> My second node - > my child node The id for 'My top parent node' doesn't exist in tblparent because it is the top parent Can anybody help with doing this Thanks in advance
View Replies !
Distinct Vs. Unique Index In Stored Procedure
Hi All, I'm struggling with this one. We have a Street database that can contain multiple entries for one record in our main table. We don't wish to pass duplicate rows back to our end user so I wish to get distinct recno's (the unique identifier for the main table) back from our Street Table. We have a stored procedure that currently uses Select distinct Recno but I think this is slower than if I used a unique index with ignore_dup_key. Problem is that when I alter the stored procedure it looks like the functionality of the LIKE function is somehow altered. For example Like 'King%' fails to return anything but Like 'King St%' does. I suspect it is the fact that the error "Server: Msg 3604, Level 16, State 1, Procedure" is being generated. How do I code around this server side.
View Replies !
Cursor With Stored Procedure
I have a stored procedure that basically recieves the where clause of a select statement and executes the new sql statement... ie: CREATE PROCEDURE [dbo].[bsa_GetImportIDs] (@FilterText varchar(1000)) AS DECLARE @MySQL varchar(1000) SET @MySQL = "SELECT Import_ID FROM tblImport WHERE " + @FilterText EXEC (@MySQL) GO Now, in another stored procedure, I need to use the stored procedure above in a cursor so that I can execute an insert statement for each occurance of the Import_ID that appears in that dataset... ie: CREATE PROCEDURE [dbo].[bsa_PutLargeCase] AS DECLARE @CaseID uniqueidentifier SET @CaseID = NewID() Declare @ImportID uniqueidentifier Declare curClient Cursor FAST_FORWARD for SELECT Import_ID FROM dbo.bsa_GetImportIDs (@FilterText) <---- this does not work!!! Open curClient FETCH NEXT FROM curClient INTO @ImportID WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.bsa_PutCaseDetail @CaseID, @ImportID FETCH NEXT FROM curClient INTO @ImportID END CLOSE curClient DEALLOCATE curClient GO How can I utilize my first stored procedure in the cursor of the second? ... or Are there any other approaches that may be a better solution to what I am trying to accomplish? Thanks in advance for any input.
View Replies !
Stored Procedure Into A Cursor
Hi guys!!I am trying to fill a cursor with the results of a StoredProcedured, but SQL give me an syntax error message , does any one cangive me some helpI am using SQL Server, this is the first lines of my codeDECLARE FRates_Cursor CURSOR FORexec GET_FJRs_Eng 'all'OPEN FRates_Cursorif I run just the exec GET_FJRs_Eng 'all' line it give me the dataresults I am trying to put into the cursor, what that means is thestored is working fineThanks in advance
View Replies !
Help In Stored Procedure With Cursor
I am trying to create the procedure from two tables to create the flat file from the two tables and as they are processed to the file then want to change the status from 'U' to 'P'. I am trying to declare the cursor from the union of two tables so that I can store the data into one table. And then put the data into the flat file and then marked the processed data as 'P' from 'U' please help in doing that. thanks, Harish CREATE PROCEDURE SP_P8 AS declare c123 cursor for select 'CA'+'|'+COMPANY+'|'+COMPANYNAME+'|'+CUSTOMERNUMBE R+'|'+COMPANYPHONE +'|'+COMPANYFAX+'|'+FEDTAXID+'|'+DUNSNUMBER+'|'+S1 099+'|'+DUNSSUFFIX+'|'+convert(char,ADDRESSSEQ) +'|'+COUNTRY+'|'+ADDRESSTYPE+'|'+ADDRESS1+'|'+ADDR ESS2+'|'+ADDRESS3+'|'+ADDRESS4 +'|'+CITY+'|'+STATE+'|'+ZIP+'|'+FAXNUMBER hello1,status from v1 UNION select 'CT'+'|'+COMPANY+'|'+CONTACTNAME+'|'+CONTACTTYPE+' |'+CONVERT(CHAR(10),CONTACTADDRESS) +'|'+PHONE+'|'+FAX+'|'+TITLE hello,status from v3 declare @status char(1) declare @wholerecord text open c123 fetch next from c123 into @wholerecord,@status while @@fetch_status=0 begin update v1 set status='P' where current of c123 update v3 set status='P' where current of c123 fetch next from c123 into @wholerecord,@status end close c123 deallocate c123 select * into temp23 from v2 where status='P' and declare @v datetime declare @filename varchar(32) select @v=getdate() select @filename = 'XCUST'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2) + SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT' declare @cmdstring varchar(255) Select @cmdstring = 'bcp conversion..temp23 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Ppassword' exec master..xp_cmdshell @cmdstring drop table temp23
View Replies !
Help In Stored Procedure With Cursor
Please help in creating the stored procedure for the following code. Thanks in Advance. ============================= Create procedure extractdata @companynumber varchar(3), @firstdateofmonth int, @lastdateofmonth int as truncate table xt truncate table xtt insert into xt(account_code,balance_date,net_change,current_ba lance,oldlob) select account_code, balance_date,net_change,current_balance,@companynu mber from ccc@companynumber..glbal where balance_date<=@firstdateofmonth declare @v1 varchar(32) declare @v2 int declare @v3 float declare c1 cursor for select account_code, max(balance_date) from xt where oldlob=@companynumber group by account_code open c1 fetch next from c1 into @v1,@v2 WHILE @@FETCH_STATUS = 0 begin update xt set status='max' where account_code=@v1 and balance_date=@v2 and oldlob=@companynumber fetch next from c1 into @v1,@v2 end close c1 deallocate c1 insert into xtt(account_code,balance_date,net_change,current_b alance,oldlob) select account_code,balance_date,net_change,current_balan ce,oldlob from xt where status='max' and oldlob=@companynumber order by account_code insert into test1(account_code,current_balance,net_change,bala nce_date,oldlob) select account_code,round(current_balance,2),round(net_ch ange,2),balance_date,@companynumber from xtt where oldlob=@companynumber and account_code in (select account_code from xxxmay where oldlob=@companynumber) update test1 set net_change=0 where balance_date<@lastdateofmonth and oldlob=@companynumber select sum(current_balance),sum(net_change) from test1 where oldlob=@companynumber
View Replies !
Out A Cursor From A Stored Procedure
hello! any of you have an idea how i can declare an output parameter for my cursor which is inside a stored procedure. i would lik to see the output using the exec command but i don't know how to get the out from my cursor. please help! honey
View Replies !
Catching Violation Of UNIQUE KEY Constraint In Stored Procedure
Dear All, I have a stored procedure which bulk inserts records into a table based on a passed in variable that contains comma separated values of record Ids. However I have a constraint on the table ensuring that value-pairs in 2 columns must be unique (as a person can not be twice on the same project) Since I insert the passed in person Ids in a loop, I’d like to catch if this constraint has been violated and skip that specific cycle if it has but do commit the rest. Not sure if this can be done, and if yes could someone let me know the SQL syntax and structure please? Am I explaining this clearly? Thanks in advance all comments are much appreciated!
View Replies !
Using Cursor As OUT Parameter In Stored Procedure
Hi guys, I have a serious problem. I need to use my cursor as an out parameter, but the problem is, HOW CAN I CLOSE THE CURSOR??????If I dont close the cursor, my server is getting really slow because of the open cursors, cause I have more than 100 stored procedures, which have a cursor as an out-parameter.Here's one of my stored procedures : create or replace PACKAGE pkgResIS TYPE resType IS REF CURSOR RETURN res%ROWTYPE;END pkgRes; create or replace procedure res_sel_val(p_id in number,cs out pkgRes.resType)asBEGIN open cs for select * from res where res_id = p_id; --close cs;EXCEPTION when others then raise_application_error(-20970, 'record kan niet geselecteerd worden');END res_sel_val; How can I close my cursor? If I write the "close cursor" (which is in red at the code above), it returns an empty cursor, which is not my intention.Please help me with thisThanks in advance Morph 'n Nike
View Replies !
Creating Cursor From Stored Procedure
Hi guys!i want to create one cursor in the t-sql. the problem is i want to usestored procedure instead of select command in cursor.can anyone tell me how can i use stored procedure's o/p to createcursor?i'm using sql 2000 and .net 2.0thanks,Lucky
View Replies !
Error From Stored Procedure With A Cursor
I receive the following intermittent error when executing a stored procedure: Msg 16942, Sev 16: Could not generate asynchronous keyset. The cursor has been deallocated. [SQLSTATE 42000] Msg 3624, Sev 16: Location: lckclass.cpp:111 Expression: m_status == ACTIVE || m_status == ORPHANED SPID: 17 Process ID: 203 [SQLSTATE 01000] The process uses a cursor to update a SQL7 table from another. This is not a consistent failure. Sometimes the procedure runs fine to completion. Has anyone else had the same problem??? We had a suggestion that it might be a tempdb problem??? Any ideas?
View Replies !
Dynamic Cursor In Stored Procedure
when i try to compile the following sp, i get an error Line 11:Incorrect syntax near;. Can someone please tell me what i am doing wrong. thanks a lot. CREATE PROCEDURE test_dump (@p_query nvarchar(4000)) AS declare @cmdtxt as varchar(4000), @SQLString NVARCHAR(4000), @SQLString1 NVARCHAR(4000), @pid varchar(22), @lname varchar(60) begin EXEC SQL BEGIN DECLARE SECTION; char prep[] = @p_query; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE prep_stat FROM :prep; EXEC SQL DECLARE contact_crsr CURSOR FOR prep_stat; OPEN contact_crsr FETCH NEXT FROM contact_crsr INTO @pid, @lname -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLString1 = 'HELLO ' + @pid + ' ' + @lname select @cmdtxt = "echo " + @SQLString1 + " >> c:empmyfile.txt" exec master..xp_cmdshell @cmdtxt FETCH NEXT FROM contact_crsr INTO @pid, @lname END CLOSE contact_crsr DEALLOCATE contact_crsr end
View Replies !
How To Declare Cursor In Stored Procedure?
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure. CREATE PROCEDURE STP_EMPSAL @empno int, @Employee_Cursor CURSOR VARYING OUTPUT FOR SELECT empno FROM AdventureworksDW.dbo.emp AS OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor into @empno; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRAN UPDATE emp set sal= sal+ 2000 where empno = @empno and comm is null mgr='Scott'; FETCH NEXT FROM Employee_Cursor into @empno; COMMIT; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor;
View Replies !
Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor. The doctors table has the following columns with specialism allowing NULL values doctor ( staff_no CHAR(3), doctor_name CHAR(12), position CHAR(15), specialism CHAR(15), PRIMARY KEY(staff_no) ) Any help would be greatly appreciated.
View Replies !
Copy Subtree, Recursive Sproc With Cursor Doesn't Work
Hi all, I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point: (id, parentId, label) 0, null, World 1, 0, US 2, 1, NY 3, 0, UK 4, 3, London now i want to copy object 3 (UK) and it's children, so i would get 0, null, World 1, 0, US 2, 1, NY 3, 0, UK 4, 3, London 5, 0, UK_copy 6, 5, London_copy I have this sproc: Code Snippet alter proc CopyObject (@ObjectId int, @NewParentId int) as declare @NewId int, @NewName varchar select @NewId = max(Id) + 1 from Object select @NewName = [Name] + 'copy' from [Object] where Id = @ObjectId -- copy object INSERT INTO [Object] ([Id] ,[Name] ,[ParentId] select @NewId, @NewName, @NewParentId from [Object] where Id = @ObjectId -- copy children and set their parent to the newly created object declare c cursor fast_forward for select Id from [Object] where ParentId = @ObjectId declare @ChildId int open c fetch next from c into @ChildId while @@fetch_status = 0 begin exec CopyObject @ObjectID = @ChildId, @NewParentId = @NewId fetch next from c into @ChildId end close c deallocate c But htis throws an error that the cursor already exists: Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66 A cursor with the name 'c' already exists. Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72 The cursor is already open. I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree. Any ideas? Thanks in advance, Gert-Jan
View Replies !
Sql Stored Procedure - With In Cursor Get @@identity Value For Insert And Use That Again
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit. (#tblSalesOrders is a temporary table which containsset of records from orginal table )DECLARE @soNo1 INT DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD FOR select fldSoNo from #tblSalesOrders declare @newSONO1 int OPEN @CursorOrders FETCH NEXT FROM @CursorOrders INTO @soNo1 WHILE @@FETCH_STATUS = 0 BEGIN ----for each salesorder insert to salesorderline insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1; set @newSONO1=SCOPE_IDENTITY; -------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert -------------------------- SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM FETCH NEXT FROM @CursorOrders INTO @soNo1 END CLOSE @CursorOrders DEALLOCATE @CursorOrders
View Replies !
Receiving And Sending A Cursor With(in) A Stored Procedure
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record. And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed. Thanks for help on this, Peter
View Replies !
Stored Procedure Cursor Problem URGENT
Hi, I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks CREATE PROCEDURE [MYSP] AS Declare @pdesc nvarchar(30) Declare @ssc int Declare @myid int Declare @name nvarchar(30) Declare new_cur CURSOR DYNAMIC FOR SELECT ProductDescription, SubSubCatID FROM C2000HPB FOR UPDATE Open new_cur FETCH FROM new_cur INTO @pdesc, @ssc While @@FETCH_STATUS = 0 BEGIN Declare new_cur2 CURSOR DYNAMIC FOR SELECT SubSubCatID, SubSubCategory FROM SSC FOR READ ONLY Open new_cur2 FETCH FROM new_cur2 INTO @myid, @name While @@FETCH_STATUS = 0 BEGIN IF PATINDEX ('@name%',@pdesc) = 0 Set @ssc = @myid UPDATE C2000HPB SET SubSubCatID = @ssc FETCH NEXT FROM new_cur2 INTO @myid, @name END Close new_cur2 DEALLOCATE new_Cur2 FETCH NEXT FROM new_cur INTO @pdesc,@ssc END Close new_cur DEALLOCATE new_Cur
View Replies !
Make A Dynamic Cursor In A Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure. For sample: In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds. How can I do ??? My code: Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50) SELECT @idRelat = idRelat, @cmd_Det = cmd_DET FROM Relatórios WHERE Nome = @p_Relat Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint, @Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024) Select @Tot_Col = 0 Select @Tot_Lin = 0 Select @Campos = 0 Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma" Select @Aux_Group = "GROUP BY " Select @Aux_Order = "ORDER BY " Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50) Declare cur_Aux insensitive cursor for SELECT Local, Linha, Campo From Relatórios_Margens WHERE (idRelat = @idRelat) ORDER BY Local, Linha Open cur_Aux Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo While @@FETCH_status = 0 begin If @a_Local = "C" Select @Tot_Col = @Tot_Col + 1 Else Select @Tot_Lin = @Tot_Lin + 1 Select @Campos = @Campos + 1 If @Aux_Group <> "GROUP BY " begin Select @Aux_Group = @Aux_Group + ", " If @Aux_Order <> "ORDER BY " begin Select @Aux_Order = @Aux_Order + ", " Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos Select @Aux_Group = @Aux_Group + @a_Campo Select @Aux_Order = @Aux_Order + @a_Campo Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo End Select @Aux_Select = @Aux_Select -- <<<< MONTA COMANDO SQL Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order Declare @Cursor_Aux cursor Set @Cursor_Aux = cursor for @Aux_Select Open @Cursor_Aux Not working !!!!
View Replies !
How To Call A Stored Procedure In T-SQL And Pass It To A Cursor
Hi, I have a kind of problem. In SQL Server I have a stored procedure ressembling this: Code Block ALTER PROCEDURE procedure1 ( @param int ) SELECT * FROM table WHERE param = @param Now I want to call this procedure and pass it to a cursor. We all know you can do this: Code Block DELCARE cursor1 CURSOR for SELECT * FROM table WHERE param = @param .. , but I want something like this: Code Block DECLARE cursor1 CURSOR for EXEC procedure1 @param Is it possible? I could solve it in another, but then I have to connect 2x to the database, which is less performant. I have also tried something like this: Code Block ALTER PROCEDURE procedure1 ( @param int ) SELECT @test = id FROM table WHERE param = @param RETURN @test ALTER PROCEDURE procedure2 ( @param int ) DECLARE @var varchar(100) EXEC @var = procedure1 @param But then it returns always 0. So is there a way to pass a procedure's select to a cursor? Thanks in advance Stevevil0
View Replies !
Return An Unique Identifier To An ASP.NET Page To Send It As A Parameter Into Another Stored Procedure
Hi ! I have a problem with the unique identifier and don't know how to solve it. I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values. I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable. Dim cmd As New SqlCommand cmd.CommandText = "Insertar_Contacto" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = connect Thanks!!
View Replies !
Problem When Invoking Stored Procedure With Cursor From Inside .net
Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,
View Replies !
Cursor Works In Query Analyzer But Not In Stored Procedure
Hi i have a script works in sql query analyzer; declare @id decimal declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID open mycur; Fetch ABSOLUTE 30 from mycur into @id close mycur; deallocate mycur; select @id this script turns me a value. i create a stored procedure from above script and its syntax is ok; CREATE PROCEDURE SELECT_MyRow AS declare @cur cursor declare @RowID decimal set @cur = CURSOR SCROLL for select myRowID from myTable order by myRowID open @cur Fetch ABSOLUTE 30 from @cur into @RowID close @cur deallocate @cur select @RowID GO my c# code using stored procedure is below; Code Snippet try { OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection); cmd.CommandType = CommandType.StoredProcedure; myconnection.Open(); OleDbDataReader reader = cmd.ExecuteReader(); MessageBox.Show(reader.GetName(0));//here fails while (reader.Read()) { MessageBox.Show(reader.GetDecimal(0).ToString()); } reader.Close(); myconnection.Close(); } catch(Exception ex) { MessageBox.Show(ex.Message); } The code above fails because reader reads no values, error message is "No data exists for the row/column" but i know exists. Can anyone help me, what is the difference between stored procedure and script ?
View Replies !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
View Replies !
PRINT Debug Messages And CURSOR In Stored Procedure Confuses DTS; &"Invalid Pointer&"
I have the following stored procedure that is called from the source ofa transformation in a DTS package. The first parameter turns on PRINTdebug messages. The second, when equals 1, turns on the openning,fetching, and closing of a cursor. Things are fine if only one of thetwo parameters was set to 1.When run with both parameters turned on, "dba_test_dts 1, 1", DTSsource (tab) preview fails because it thinks no row was returned. "Norowset was returned from the SQL statement". Understanbly then thetransformation step would also fail with the "Invalid Pointer" error.As you'd see, I have SET NOCOUNT ON in the code. Has anyoneexperienced this? Is this a known bug? This occurs in SQL Server 2000running on Windows Server 2003.-----------------------------------------------------------------------------------------------------------------CREATE PROC dba_test_dts ( @debug BIT = NULL, @cur BIT = NULL )AS-- Always have these 2 options set or unset so DTS would not error out-- with the Invalid Pointers message.SET NOCOUNT ONSET ANSI_WARNINGS OFFDECLARE @FMT_FILE_NAME VARCHAR(256)DECLARE @OUTPUT_FILE_NAME VARCHAR(256)DECLARE @emp_id INTDECLARE @lastname VARCHAR(70)IF ( @debug = 1 )BEGINPRINT '=== BEGIN ==='PRINT 'Stored Procedure dts_calling_stored_proc'PRINT 'Begin timestamp: ' + CONVERT(VARCHAR(32), CURRENT_TIMESTAMP,109 )PRINT 'Server : ' + @@SERVERNAMEPRINT 'Host Name/ID : ' + HOST_NAME() + '/' + CONVERT(VARCHAR(32),HOST_ID())PRINT 'Database : ' + DB_NAME()PRINT 'User/NT User : ''' + USER_NAME() + '''/''' + SYSTEM_USER +''''PRINT 'DEBUG FLAG : ' + CONVERT( CHAR(1), @DEBUG )PRINT '=== BEGIN ==='PRINT SPACE(1)ENDIF ( EXISTS ( SELECT 1 FROM sysobjects WHEREid=object_id(N'Employees_temp') ) )DROP TABLE Employees_tempCREATE TABLE Employees_temp(emp_id INTEGER, lastname VARCHAR(70))INSERT INTO Employees_temp([emp_id],[lastname])SELECT EmployeeID, lastnameFROM EmployeesIF ( @cur = 1 )BEGINDECLARE curEmp CURSOR FORSELECT emp_id, lastnameFROM Employees_tempOPEN curEmpFETCH NEXT FROM curEmpINTO@emp_id, @lastnameWHILE ( @@FETCH_STATUS = 0 )BEGINFETCH NEXT FROM curEmpINTO@emp_id, @lastnameENDCLOSE curEmpDEALLOCATE curEmpENDSELECT emp_id, lastnameFROM Employees_tempGO
View Replies !
Help Cursor Based Stored Procedure Is Getting Slower And Slower!
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View Replies !
Recursive Stored Proc Help
I'm trying to write a recursive stored proc for the TreeView control that will return XML for the TreeView. Does anyone have any examples of code to do this or any resources that might help me write the stored proc? Thanks
View Replies !
Recursive Stored Procedures
I've got an application where I need to traverse a "tree" of parent and child relationships. I've got code in my application that does this nicely, and I want to move it to the database in the form of stored procs for a little better performance. The initial way I created it, is that the stored proc creates a cursor for the parent nodes, then calls itself for each child node, and rips through it that way. The problem is is that I get a cursor already exists error. Am I doing something wrong, or is that a limitation of SQL server to only have one named cursor open at a time? Is there a better way to do this? Thanks in advance...
View Replies !
Recursive Call In Stored Proc
Hi allI am implementing a stored procedure which needs to recursively callitself until specific condition is reached, Could anyone give someadvice about that?Thanks a lotRobert Song
View Replies !
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Hi all, I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE): (1) /////--spTopSixAnalytes.sql--/// USE ssmsExpressDB GO CREATE Procedure [dbo].[spTopSixAnalytes] AS SET ROWCOUNT 6 SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName FROM LabTests ORDER BY LabTests.Result DESC GO (2) /////--spTopSixAnalytesEXEC.sql--////////////// USE ssmsExpressDB GO EXEC spTopSixAnalytes GO I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--/////////// Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;") Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection) sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure 'Pass the name of the DataSet through the overloaded contructor 'of the DataSet class. Dim dataSet As DataSet ("ssmsExpressDB") sqlConnection.Open() sqlDataAdapter.Fill(DataSet) sqlConnection.Close() End Sub End Class /////////////////////////////////////////////////////////////////////////////////////////// I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1) Please help and advise. Thanks in advance, Scott Chang More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View Replies !
Cursor And Procedure
Hello: I want find that the ntext column data string have more than 2000 characters. I need to truncate those string to segment with 200 character, then put those segments along with their table_name and column_name to another table. Maybe need to use cursor? If so, how to use it? Your help is highly appreciated. s
View Replies !
Cursor In Procedure
Hi I am trying to write one cursor which is been made from join of two tables. and then i want to update one table by using where current of cursor. Can i do that..? or should i create the view by joining the two tables and then try to update it. or should i create the temp table and put the records in that and then try to update that. Please guide me in this. Thanks, Harish ==== CREATE PROCEDURE SP_P4 AS declare c123 cursor for select COMPANY.COMPANY, COMPANY.COMPANYNAME, COMPANY.CUSTOMERNUMBER, COMPANY.FEDTAXID, COMPANY.DUNSNUMBER, COMPANY.S1099, COMPANY.DUNSSUFFIX, COMPANYADDRESS.ADDRESSSEQ, COMPANYADDRESS.COUNTRY, COMPANYADDRESS.ADDRESSTYPE, COMPANYADDRESS.ADDRESS1, COMPANYADDRESS.ADDRESS2, COMPANYADDRESS.ADDRESS3, COMPANYADDRESS.ADDRESS4, COMPANYADDRESS.CITY, COMPANYADDRESS.STATE, COMPANYADDRESS.ZIP from COMPANY,COMPANYADDRESS where company.company=COMPANYADDRESS.company order by company.company declare @company char declare @COMPANYNAME char declare @CUSTOMERNUMBER char declare @FEDTAXID char declare @DUNSNUMBER char declare @S1099 char declare @DUNSSUFFIX char declare @ADDRESSSEQ int declare @COUNTRY char declare @ADDRESSTYPE char declare @ADDRESS1 char declare @ADDRESS2 char declare @ADDRESS3 char declare @ADDRESS4 char declare @CITY char declare @STATE char declare @ZIP char open c123 fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099, @DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY, @STATE,@ZIP while @@fetch_status=0 begin update companyaddress set ADDRESSSEQ=@ADDRESSSEQ where current of c123 update companyaddress set COUNTRY=@COUNTRY where current of c123 update companyaddress set ADDRESSTYPE=@ADDRESSTYPE where current of c123 update companyaddress set ADDRESS1=@ADDRESS1 where current of c123 update companyaddress set ADDRESS2=@ADDRESS2 where current of c123 update companyaddress set ADDRESS3=@ADDRESS3 where current of c123 update companyaddress set ADDRESS4=@ADDRESS4 where current of c123 update companyaddress set CITY=@CITY where current of c123 update companyaddress set STATE=@STATE where current of c123 update companyaddress set ZIP=@ZIP where current of c123 fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099, @DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY, @STATE,@ZIP end close c123 deallocate c123 declare @v datetime declare @filename varchar(32) select @v=getdate() select @filename = 'XAPIALTMKT'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2) + SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT' declare @cmdstring varchar(255) Select @cmdstring = 'bcp conversion..expt1 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Psa' exec master..xp_cmdshell @cmdstring drop table expt1
View Replies !
Cursor In Procedure
Hi I AM UPDATING VIEW V1 IN THE PROGRAM. AND I WANT TO PULL THE UPDATED DATA IN THER FLAT FILE WHICH I HAVE MARKED 'P' IN THE CURSOR. PLEAS ETELL ME HOW TO GO FOR THAT. ================================ CREATE PROCEDURE SP_P5 AS declare c123 cursor for select * from v1 declare @company char(12) declare @COMPANYNAME char(60) declare @CUSTOMERNUMBER char(12) declare @FEDTAXID char(12) declare @DUNSNUMBER char(9) declare @S1099 char(3) declare @DUNSSUFFIX char(4) declare @ADDRESSSEQ int declare @COUNTRY char(30) declare @ADDRESSTYPE char(12) declare @ADDRESS1 char(40) declare @ADDRESS2 char(40) declare @ADDRESS3 char(40) declare @ADDRESS4 char(40) declare @CITY char(30) declare @STATE char(2) declare @ZIP char(11) declare @status char(1) declare @dateofaction datetime open c123 fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099, @DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY, @STATE,@ZIP,@status,@dateofaction while @@fetch_status=0 begin update v1 set ADDRESSSEQ=@ADDRESSSEQ,COUNTRY=@COUNTRY,ADDRESSTYP E=@ADDRESSTYPE, ADDRESS1=@ADDRESS1,ADDRESS2=@ADDRESS2,ADDRESS3=@AD DRESS3,ADDRESS4=@ADDRESS4,CITY=@CITY, STATE=@STATE,ZIP=@ZIP,status='P' where current of c123 fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099, @DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY, @STATE,@ZIP,@status,@dateofaction end close c123 deallocate c123 declare @v datetime declare @filename varchar(32) select @v=getdate() select @filename = 'XCUST'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2) + SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT' declare @cmdstring varchar(255) Select @cmdstring = 'bcp conversion..V1 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Psa' exec master..xp_cmdshell @cmdstring
View Replies !
How Can I Store A Stored Procedure Name For A Report In A Table And Link It To A Dataset As A Stored Procedure?
Hi! I have about 100 SSRS 2005 reports, each of which links to a stored procedure. Each stored procedure may have two, three or even four parameters, so they vary a bit. I now also have a table called ReportInfo that stores the displayable report names, the rdl file names and some additional information that displays on the header of each report. I'd like to be able to store the name of the stored procedure in that table as well and just tell the dataset to execute that stored procedure, but it isn't working the way I expected. There are two datasets with each report. The first dataset points to the ReportInfo table, where all the standard information about the report is located, including now the name of the stored procecdure to which the second dataset is supposed to link. I'm not able to point the name of the stored procedure to a field in another dataset. I can't say, for example =First(Fields!StoredProcedure.Value, "ds_ReportInfo") That gives an error I then tried setting the dataset type to Text, creating a ReportParameter called StoredProcedure (which was filled in from the first query) and then tried: Exec (@StoredProcedure) In a way, that kind of worked. I got an error message back telling me the stored procedure needed a startdate and endingdate, which are the two parameters for this parrticular stored procedure. I just don't want to have to code that into the text query. Anyway, it wasn't my intention to have to use a text-based data query. It's as much of a hassle to use the drop-down to pick stored procedure names as it is to create a long text string with two or three parameters. I just want to dynamically control the name of the stored procedure and have it act exactly as it does when I select a stored procedure from a drop down. That is, I want to be able to tell Reporting Services where to find the name of the Stored Procedure for the dataset and then see all the fields it would return and be prompted for the two, three or four parameters exactly the same way I am when I select a stored procedure from a dropdown. The reason I ask this is that we've changed the naming convention for the stored procedures for reports, and now I'm having to go back into every report and reselect the new stored procedure name. I'd really much rather have the names in a database (in case they decide to change them again) and then just have the report pull the stored procedure name from the table. But I'm not finding an easy way to do that. I wouldn't mind putting a little piece of code in each report to do this if necessary, but what I don't want to do is have every report be different. That's the problem with using the EXECUTE statement in a text-based query. Each query has to be different based on the number and content of the parameters, and I don't want that. I just want to tell Reporting Services where to find the name of the stored procedure for the dataset and then have it treated like any other stored procedure. Any suggestions? Is anyone else trying to do this? Thanks Karen
View Replies !
Why Need To Open CURSOR In The Procedure?
Hi,everyone. Why need to open CURSOR in the part in which a procedure is created? The following codes are listed in the Microsoft SQL Server 2005 BOOKS ONLINE. (1) USE AdventureWorks; GO IF OBJECT_ID ( 'dbo.currency_cursor', 'P' ) IS NOT NULL DROP PROCEDURE dbo.currency_cursor; GO CREATE PROCEDURE dbo.currency_cursor @currency_cursor CURSOR VARYING OUTPUT AS SET @currency_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency; OPEN @currency_cursor; GO (2) USE AdventureWorks; GO DECLARE @MyCursor CURSOR; EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT; WHILE (@@FETCH_STATUS = 0) BEGIN; FETCH NEXT FROM @MyCursor; END; CLOSE @MyCursor; DEALLOCATE @MyCursor; GO Please help me. Thank you in advance!
View Replies !
Cursor And Procedure Questions
Hello:I want find that the ntext column data string have more than 2000 characters. I need to truncate those string to the segments with 200 character, then put those segments along with their table_name and column_name to another table. Maybe need to use cursor? If so, how to use it? Your help is highly appreciated. S
View Replies !
Procedure/static Cursor
can someone tell me how to use static cursor to read the rows applying it in procedure. i have input and output tables i will have to use input table to read data and then in procedure update/insert into output table ) insert values from input and dditioanl calulate charges. ok, there is my problem: An Internet service provider has three different subscription packages for its customers: Package A: For $15 per month with 50 hours of access provided. Additional hours are $2.00 per hour over 50 hours. Assume usage is recorded in one-hour increments, i.e., a 25-minute session is recorded as one hour. Package B: For $20 per month with 100 hours of access provided. Additional hours are $1.50 per hour over 100 hours. Package C: For $25 per month with 150 hours access is provided. Additional hours are $1.00 per hour over 150 hours Assume a 30-day billing cycle. 1) Create a table to hold customer input billing data. 2) Populate input table with follwing records: CustomerID Pkg Hours ---------- --- ------ 1000 A 49 1010 A 50 1020 a 90 1030 a 130 1090 B 40 1100 B 99 1110 b 100 1120 b 145 1140 C 45 1150 c 85 1160 c 149 1170 c 150 1180 c 200 3) Create a table to hold customer data used to generate the statement to be sent to the customer. It should include CustomerID, Package, HoursUsed, and Charges. Write an SQL script that reads customer billing data, calculates a customer’s monthly charges, and populates the customer statement table. Use Cursor to process records and Stored Procedures for ProcessBill and calcCharges. CREATE TABLE custinput( cust_id int NULL, pkg char(1) NULL, hrs smallint NULL ) CREATE TABLE custoutput( cust_id int NULL, pkg char(1) NULL, hrsused smallint NULL, charges money null ) insert into custinput values (1000,'A',49); insert into custinput values (1010,'A',50); insert into custinput values (1020,'a',90); insert into custinput values (1030,'a',130); insert into custinput values (1090,'B',40); insert into custinput values (1100,'B',99); insert into custinput values (1110,'b',100); insert into custinput values (1120,'b',145); insert into custinput values (1140,'C',45); insert into custinput values (1150,'c',85); insert into custinput values (1160,'c',149); insert into custinput values (1170,'c',150); insert into custinput values (1180,'c',200); then there is conditions: if upper (@pkg)= 'A' begin if @hrs<= 50 set @charges =15 else set @charges =15 + (@hrs-50)*2 end; else if upper(@pkg)= 'B' begin if @hrs <= 100 set @charges = 20 else set @charges = 20 + (@hrs - 100)*1.5 end; else if @hrs <=150 set @charges = 25 else set @charges =25+(@hrs-150) insert into custoutput values(@cust_id,@pkg,@hrs,@charges)
View Replies !
|
TRACKER
