Dynamic Execution Of Cursor Fetch
I'm trying to write code which will fetch records from a dynamically created cursor. Therefore the cursor name is not known at design time.
Unfortunately applying an enclosing the fetch command in quotes and then calling the execute method with this string will not work.
e.g.
declare @A varchar(10),
@B varchar(10),
@sFetch varchar(255)
--Open the cursor
Execute ('Open ' + crAnyCursor)
--create fetch string
Select @sFetch = 'Fetch Next From' + crAnyCursor + 'into @A, @B'
execute (@sFetch)
The following error is returned...
Msg 137, Level 15, State 1
Must declare variable '@A'.
Is there another means of working with dynamically executed cursors?
Thanks
JohnG
View Complete Forum Thread with Replies
Related Forum Messages:
Fetch Cursor Help
Let's say i have 5 unique RRID's, column APID and ITID RRID - APID - ITID 1 13 700 2 13 700 3 13 700 4 14 700 5 15 700 If I run the stored procedure below, I get the results above however, I want my result to be RRID - APID - ITID 1 13 700 2 13 700 3 13 700 4 14 701 5 15 702 I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on... Any help is highly appreciated.... SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ALTER PROCEDURE InsNewEmployeeImpTaskP2 @REID int, @LOID int, @RetValintoutput AS Declare @RRID int Declare @APID int Declare @intREID varchar(20) Declare @intIMID varchar(20) Declare crReqRec cursor for select RRID from RequestRecords where REID = @REID and RRSTatus = 'AC' and APID is not null open crReqRec fetch next from crReqRec into @RRID set @APID = (select APID from RequestRecords where REID = @REID and RRID = @RRID) set @intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @APID and ImplementationGroup_Location.LOID = @LOID ) insert into ImplementationTasks ( IMID, ITStatus, ITStatusDate ) VALUES ( @intIMID, '2', GetDate() ) SET @RetVal = @@Identity while @@fetch_status = 0 Begin Update RequestRecords set ITID = @RETVal, RRStatus = 'IA' where REID = @REID and RRID = @RRID FETCH NEXT FROM crReqRec into @RRID end close crReqRec deallocate crReqRec GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
View Replies !
Can't Fetch Record From Cursor
Hi, I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks ---- DECLARE @CreatedByUser nchar(100), @WorkflowIDs varchar(50); DECLARE @MyVariable CURSOR; SET @MyVariable = CURSOR FOR SELECT isnull(Filters.WorkflowIDs, ''), isnull(FilterElements.CreatedByUser, '') FROM Filters RIGHT OUTER JOINFilterElements ON Filters.ItemID = FilterElements.FiltersItemID WHERE FiltersItemID = @FilterID; OPEN @MyVariable;FETCH NEXT FROM @MyVariable INTO @WorkflowIDs, @CreatedByUser;
View Replies !
MDAC 2.8 And Cursor FETCH NEXT
it seems that starting with MDAC 2.8 the FETCH NEXT statement for cursors requires an INTO clause. otherwise an unspecified error is returned. so it's not possible to scroll through the records anymore without storing the values of the fields into local variables?
View Replies !
Help With Cursor And Fetch Statement
Hello, I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function. I have a temp table populated with the below headers and their associated data. The headers are as follows: ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost. The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function. One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand. The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with. The current script I have written is below. If you can offer any help I would greatly appreciate it. Code SnippetDeclare @ItemID VarChar(30), @QtyOnHand Decimal (16,8), @WhseID VarChar (6), @SumRcvd Int, @TranID VarChar(30), @TranDate DateTime, @QtyRcvd Decimal (16,8), @UnitCost Decimal (16,8), @ItemKey Int, @WhseKey Int, @ShortDesc VarChar (40), @StdCost Decimal (16,8) DECLARE Temp_cursor CURSOR FOR SELECT TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost FROM #Temp1 tem OPEN Temp_cursor FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost WHILE @@FETCH_STATUS = 0 BEGIN -- 0 Insert Into #Temp3 (TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost) Values (@TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost) FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost
View Replies !
Multiple While Fetch Cursor Code
I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or??? What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string. For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @instrumentlinje remains empty. If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @instrumentlinje = @instrumentlinje + ' ' + @instrument" statement doesn't execute. DELETE FROM ALL_tbl_instrumentkoder DECLARE @medlem int DECLARE @instrument varchar(10) DECLARE @instrumentlinje varchar(150) DECLARE medlemmer_cursor CURSOR FOR SELECT medlemsnummer FROM ket.ALL_tbl_medlemsinfo (NOLOCK) WHERE medlemsnummer = 9611 DECLARE instrumenter_cursor CURSOR FOR SELECT [MCPS Kode] FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK) WHERE Medlemsnummer = @medlem OPEN medlemmer_cursor FETCH NEXT FROM medlemmer_cursor INTO @medlem WHILE @@FETCH_STATUS = 0 BEGIN OPEN instrumenter_cursor FETCH NEXT FROM instrumenter_cursor INTO @instrument WHILE @@FETCH_STATUS = 0 BEGIN SELECT @instrumentlinje = @instrumentlinje + ' ' + @instrument FETCH NEXT FROM instrumenter_cursor INTO @instrument END CLOSE instrumenter_cursor INSERT INTO ALL_tbl_instrumentkoder VALUES(@medlem, @instrumentlinje) FETCH NEXT FROM medlemmer_cursor INTO @medlem END CLOSE medlemmer_cursor DEALLOCATE medlemmer_cursor DEALLOCATE instrumenter_cursor
View Replies !
Possible To Fetch Next From Cursor Into Table Variable?
Hello, I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like: Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table it does not work. any help would be great. thnx
View Replies !
It Is Very Slow At Updating By Use Cursor (fetch Method)
Hi all, I got a problem. I am working on DTS package. The last step is updating a table field. I wrote a stored procedure as below: CREATE PROCEDURE [Update_product_manufacturer] AS Declare @product_id int Declare @supplier_name VarChar (255) Declare ValueCursor Cursor For select product.product_id, [P21_SUPPLIER_id_name_ke].[supplier_name] from [VARIANT],[P21_INV_MAST_uid_itenID_weight_ke],[product], [P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e],[P21_SUPPLIER_id_name_ke] where [product].product_id = [VARIANT].[product_id] and [P21_INV_MAST_uid_itenID_weight_ke].[item_id]=[VARIANT].[SKU] AND [P21_INV_MAST_uid_itenID_weight_ke].[inv_mast_uid]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[inv_mast_uid] AND [P21_SUPPLIER_id_name_ke].[supplier_id]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[supplier_id] order by [product].[product_id] for read only Open ValueCursor while (0 = 0) begin fetch next from ValueCursor Into @product_id, @supplier_name update product set manufacturer = @supplier_name where product_id = @product_id end close ValueCursor Deallocate ValueCursor Notes: Table: Product has 28,000 rows, other tables with 28,000 - 56,000 rows it's been 2 hours, the job is still working. Who has this kind of experience? How can I make updating quickly? Thanks, Kevin Zhang
View Replies !
How To Return Multiple Results In 1 Fetch Cursor- Urgent Help!
I want to send 1 email with all clientname records which the cursor gets for me. My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help. I ano table to understand here about the while if right. thanks. +++++++++++++++++++++++++++++++++++++++++ CREATE PROCEDURE test1 AS declare @clientName varchar(1000) declare myCursor CURSOR STATIC for select client_name from clients ------------------------- -- now prepare and send out the e-mails declare @ToEmail varchar(255) declare @FromEmail varchar(255) declare @Subject varchar(255) declare @Body varchar(2000) declare @UserID numeric(38) declare @UserName varchar(255) declare @SMTPServer varchar(100) set @SMTPServer = 'test.testserver.com' -- loop for each record open myCursor fetch next from myCursor into @clientName --loop now: while (@@fetch_status=0) begin -- while(@@fetch_status=0) -- check if valid "To" e-mail address was found if ((@clientName is null) or (ltrim(@clientName) = '')) begin --should not come here anytime ideally set @FromEmail = 'me@test.com' set @ToEmail = 'me@test.com' set @Subject = 'was emailed to wrong person' set @Body = 'the client name got is : '+ @clientName + 'client is null or empty' end --if else begin set @FromEmail = 'me@test.com' set @ToEmail = 'me@test.com' set @Subject = '-testing' set @Body = 'this will send ClientName:'+ @clientName end --end else -- send the e-mail --exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer --fetch next from myCursor into @clientName fetch next from myCursor into @clientName end --while(@@fetch_status=0) exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer close myCursor deallocate myCursor GO
View Replies !
Hi, Please Help Me W/ Fetch Cursor Querying, This Sends Email Automatically
create proc emailnew as declare @From varchar(8000) declare @Subject varchar(8000) declare @Body varchar(4000) declare @smtp varchar(8000) declare @counter int, @tbl varchar(8000) Declare @MailID int Declare @hr int Declare @To varchar(8000) Declare @tblquery varchar(8000) declare @id int, @deptemail varchar(8000), @tmpmth varchar(8000), @usedb varchar(8000) set @from = 'name@mail.com' set @subject = 'testheader' set @body = 'testing successful' set @smtp = 'smtp.com' --========================================================================= --============================ get database name ======================= IF (LEN(MONTH(GETDATE())) = 1) BEGIN Set @TmpMth = '0' + CAST(MONTH(GETDATE()) AS varchar(2)) --01 END ELSE BEGIN Set @TmpMth = CAST(MONTH(GETDATE()) AS varchar(2)) --12 END SET @UseDB = 'DATA' + CAST(YEAR(GETDATE()) AS varchar(4)) + @TmpMth --aia_DATA200712 --=================================================================== --============================ get table number ======================= set @counter = 1 while @counter >= 59 begin IF (LEN(@counter) = 1) BEGIN Set @Tbl = '0' + @counter --01 END ELSE BEGIN Set @Tbl = @counter --12 END --=========================check if table being created exists IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE catalog_name = 'temptable') GOTO table_1 --=================================== get all email accounts ===================== set @tblquery = ' select ID, Email INTO temptable FROM ( select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.inward' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.local' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null )data DECLARE deptemail_cursor CURSOR FOR select id, email from temptable where id is not null ' --============================================================================= --====just above you can see the cursor.. below is sending the emails --====================================================================== exec(@tblquery) OPEN deptemail_cursor FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail WHILE @@FETCH_STATUS = 0 BEGIN --If LEN(@deptemail) > 0 --BEGIN set @to = @deptemail EXEC @hr = sp_OACreate 'CDo.message', @MailID OUT --CDo.message |CDONTS.NewMail <-- different mail server EXEC @hr = sp_OASetProperty @MailID , 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' EXEC @hr = sp_OASetProperty @MailID , 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtp EXEC @hr = sp_OASetProperty @MailID , 'From', @From EXEC @hr = sp_OASetProperty @MailID , 'HTMLBody', @Body EXEC @hr = sp_OASetProperty @MailID , 'Subject', @Subject EXEC @hr = sp_OASetProperty @MailID , 'To', @to EXEC @hr = sp_OAMethod @MailID , 'Send', NULL EXEC @hr = sp_OADestroy @MailID --END FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail END CLOSE deptemail_cursor DEALLOCATE deptemail_cursor Table_1: drop table temptable return set @counter = @counter + 1 end this is suppose to send email automatically to every email account that it will get from all the tables (around 250 tables). the problem is its not sending, but if i try to take my code outside of the "SET @COUNTER = @COUNTER + 1 END" and close the if statement above, i can produce the correct result.. i'm thinking maybe its the positioning? but, there could be some overhauling needed to do with this.. sorry for posting the sp. sorry for the trouble.. please help me
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 !
How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?
Dear All I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor. Here is my query sample for yours to understand. SET NOCOUNT ON DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000) Set @qstring = 'select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm' PRINT '-------- ITEM Products Report --------' DECLARE ITEM_cursor CURSOR FOR execute (@qstring) OPEN ITEM_cursor FETCH NEXT FROM ITEM_cursor INTO @ITEMCODE WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' ' SELECT @message = '----- Products From ITEM: ' + @ITEMNAME PRINT @message -- Get the next ITEM. FETCH NEXT FROM ITEM_cursor INTO @ITEMcode END CLOSE ITEM_cursor DEALLOCATE ITEM_cursor Why i use @qstring? It is because the query will be changed by different critiera. Regards Edmund
View Replies !
SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule
Hi, I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly. So, is this possible using data driven subscriptions? Scenario is: 1. User enters parameter used for query, as well as email addresses. 2. Report is generated and displayed on screen. 3. Report is emailed to addresses specified by user. Any tips on how to get this working? Thanks Mark Smith
View Replies !
&"Cursor-Fetch&" Problem:Oracle2SQL Server Migration
Dear all, I have a procedure in Oracle that contains the following cursor: CURSOR SCHED_TRIPS IS SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID FROM Dbo.SCHEDTRIPS_VIEW WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE) AND DISPOSITION <> 'V'; BEGIN FOR S IN SCH_TRIPS LOOP UPDATE dbo.SCHEDULES T SET T.DIRTYBIT = 1 WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA); UPDATE dbo.SCHEDULES T SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE; END LOOP; COMMIT ; END; My problem is with the line shown in Red. What will be the T-SQL equivalent for this line. Anxiously waiting for help!
View Replies !
Dynamic Cursor/ Dynamic SQL Statement
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement. Using the examples given in Books Online returns compilation errors. See below. Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement? James -- SQL --------------- EXEC SQL BEGIN DECLARE SECTION; char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?"; char szLastName[] = "White"; char szFirstName[30]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE author_cursor CURSOR FOR select_statement; EXEC SQL PREPARE select_statement FROM :szCommand; EXEC SQL OPEN author_cursor USING :szLastName; EXEC SQL FETCH author_cursor INTO :szFirstName; --Error-------------------- Server: Msg 170, Level 15, State 1, Line 23 Line 23: Incorrect syntax near ';'. Server: Msg 1038, Level 15, State 1, Line 24 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 1038, Level 15, State 1, Line 25 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near ';'. Server: Msg 170, Level 15, State 1, Line 30 Line 30: Incorrect syntax near 'select_statement'. Server: Msg 170, Level 15, State 1, Line 33 Line 33: Incorrect syntax near 'select_statement'. Server: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'author_cursor'. Server: Msg 170, Level 15, State 1, Line 36 Line 36: Incorrect syntax near ':'.
View Replies !
How To Get Source By Execution Oracle Sp Using Ref Cursor
Hi, I need to get recordset returned by oracle sp in execute sql task to process futher in For Each Loop container and on same lines i want to use oracle sp for extraction data in Data Flow Task. Could anybody suggest if it how we could do it in SSIS? All suggestions will be highly appreciated. Thanks, Lalit
View Replies !
Dynamic SQL In Cursor
I need to pass a list of values into a cursor as such... DECLARE @group_SQL varchar(255) SET @group_SQL = 'SELECT group_id FROM groups where group_id in (' + @group_id + ')' DECLARE groupContact_import_cursor CURSOR FOR EXEC(@group_SQL) OPEN groupContact_import_cursor FETCH NEXT FROM groupContact_import_cursor INTO @group_id WHILE (@@FETCH_STATUS = 0) BEGIN insert into groupContacts (group_id, contact_id) values (@group_id, @new_cid) FETCH NEXT FROM groupContact_import_cursor INTO @group_id END CLOSE groupContact_import_cursor DEALLOCATE groupContact_import_cursor But MS SQL doesn't seem to like the FOR EXEC(@group_SQL). Can someone shed some light? TIA
View Replies !
Dynamic Cursor
I am trying to use a dynamic cursor in a stored procedure: The code looks like this : /************************************************** *** set @sFormula = 'Monthlyformula' set @sStartDate = '02/01/2004' set @sEndDate = '02/01/2004' exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '+ @sStartDate +' and '+ @sEndDate +')' ) /************************************************** *** And this is what it is interpreting select populateid From appgridrows where histdisplaygrid = 3 And Monthlyformula Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between 02/01/2004 and 02/01/2004) My problem is Is there anyway that I can put the quotes before those dates('02/01/2004') so that my cursor has some records returned Thanks in advance SK
View Replies !
Dynamic Cursor
Hello ! I´m having a big problem with a dynamic cursor. There is my problem: I have two cursors. One I use to select a master table. The other I use to select a detail table. I want to fetch all rows of the master and for each row select the details. How can I pass to the detail cursor the master key ? Thanks and I´m sorry for my poor English ! :)
View Replies !
How Get The Dynamic Sql In To Cursor
Dear folks, In My Query i am using where in condition .It return multiple record .I want store it in to cursor and perform the operation. Declare @sql varchar(5000); set @sql='select * from Role where Role_id in('+ @role_ids +')'; Exec @sql; I want take this record set in to cursor .How to do it. please help me.
View Replies !
Dynamic Execution
Hello, How do I get quiry result in variable? exp. - DECLARE @varVariable varchar(50), @varFld varcahr(50), @varSel varchar(1000) SELECT @varVariable = (SELECT Field1 FROM Table1 WHERE Field2 = 1234) This will give value of Field1 in @varVariable But how I will get this value in Dynamic Execution? It will work? - SET varFld = 'Field1' SELECT @varVariable = (SELECT @varSel = 'SELECT '+@varFld+' FROM Table1 WHERE Field2 = 1234') EXEC (@varSel) PRINT @varVariable ************************************ This will work? what is the alternative? help please. ANB
View Replies !
Dynamic SQL Execution
Hey guys, I've got a problem here. I gave execute permission to an user on a particular stored proc which has simple select queries. (The tables have no select permissions for that user.) Now, the user was able to run it fine and got the results. When hdynamically creates sql queries inside the proc (Based on the input parameters) sqlserver is checking for the permissions on the tables and got permission denied message. Is there any solution to avoid this problem?..please let me know and i'd appreciate that.. thank you very much.
View Replies !
Dynamic Cursor Generation..
Hi Everybody,I have a probs with dynamic generation.I am writing the probs======================================create proc testasdeclare @query varchar(500)set @query = 'select * from table'----------------------------------------------declare mycur Cursor for Select * from table |open mycur |----------------------------------------------but instate of above block how can I dynamically generate this query?---------------------------------------declare mycur Cursor for exec (@query) |---------------------------------------Or tell me the way.RegardsArijit Chatterjee
View Replies !
Declare Cursor With Dynamic SQL?
Hello.. Can you declare a cursor with dynamic SQL? I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement. these attempts did not work: DECLARE crsCursor CURSOR FOR @vchrSQL DECLARE crsCursor CURSOR FOR (@vchrSQL) Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?
View Replies !
Dynamic Select For CURSOR
Hi all I am trying to do dynamic Select for Cursor. The dynamic would be like this: IF CONDITION1 IS TRUE: SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID FROM dbo.tnd_TenderSiteRateConsumptionView WHERE CustomerTenderID = @CustomerTenderID IF CONDITION2 IS TRUE: SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID FROM dbo.tnd_TenderSiteRateConsumptionView WHERE CustomerTenderID = @CustomerTenderID AND CustomerSiteID = @CustomerSiteID etc etc Here's the cursor DECLARE RateList CURSOR FOR SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID FROM dbo.tnd_TenderSiteRateConsumptionView WHERE (BASED ON CONDITION) ORDER BY CustomerTenderID, CustomerSiteID, SupplierID, ContractPeriod OPEN RateList FETCH NEXT FROM RateList INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID WHILE @@FETCH_STATUS = 0 BEGIN SET @rowNum = @rowNum + 1 -- DO SOME FUNKY STUFF FETCH NEXT FROM RateList INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
View Replies !
Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi
View Replies !
Dynamic Cursor - Sorting In Declaration
Hello everybody!I have a small table "ABC" like this:id_position | value---------------------------1 | 112 | 223 | 33I try to use a dynamic cursor as below.When the statement "order by id_position" in declare part of the cursor_abcis omitted - cursor work as it should.But when the statement "order by id_position" is used, cursor behave asstatic one.What's the matter, does anybody know?Code:declare @id_position as int, @value as intDECLARE cursor_abc CURSORFORselect id_position, value from abcorder by id_positionset nocount onopen cursor_abcFETCH NEXT FROM cursor_abcINTO @id_position, @valueWHILE @@FETCH_STATUS = 0BEGINprint @id_positionprint @valueprint '----------------------------'update abc set value=666 --next reading should give value=666FETCH NEXT FROM cursor_abcINTO @id_position, @valueENDCLOSE cursor_abcDEALLOCATE cursor_abcGORegardsLucas
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 !
Executing SP Having A Dynamic Cursor Fails In Calling SP
Hi, In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message: Msg 16916, Level 16, State 1, Procedure SP2, Line 114 A cursor with the name 'C1' does not exist. Yes, the cursor is of type GLOBAL. I am sure I miss something here ... Any help is highly appreciated ! Thanks: Peter
View Replies !
Declare Cursor Based On Dynamic Query
Hi, I am declaring the cursor based on a query which is generated dynamically. but it is not working Declare @tempSQL varchar(1000) --- This query will be generated based on my other conditon and will be stored in a variable set @tempsql = 'select * from orders' declare cursor test for @tempsql open test This code is not working. please suggest Nitin
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 !
Dynamic SQL And Execution Plan And Very Slow. A Few Questions
Hey. I've a problem and I think I know the answer also but still want to confirm. We are using SQL 2000 and SSRS 2000. The problem is, we have custom reports which a customer can build and run. I wonder how one can write sp's for that. The way it's written right now is a dynamic select clause then a dynamic, from, a dynamic where, dynamic groupby all appended torgether and run by execute command. I know it'd dynamic SQL and execution plans and stuff will hurt me but someof these reports take forever. Is there anything that can be done to fasten these reports? And if the select will be dynamic and the where will be dynamic, does it make sense to even use a sp? Is it ever going to use the same execution plan? When I run DBCC memorystatus, procedure cache takes up most of this memory. Does the use of dynamic SQL explain that? Thank you for your time and effort in replying.
View Replies !
Dynamic Query, Local Cursor Variable And Global Cursors
Hi all. I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out. So, first things first: let me explain what I need to do. I am designing a web application that will allow users to consult info available in a SQL2000 database. The user will enter the search criterea, and hopefully the web page will show matching results. The problem is the results shown aren't available per se in the DB, I need to process the data a bit. I decided to do so on the SQL Server side, though the use of cursors. So, when a user defines his search criteria, I run a stored procedure that begins by building a dynamic sql query and creating a cursor for it. I used a global cursor in order to do so. It looked something like this: SET @sqlQuery = ... (build the dinamic sql query) SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery OPEN myCursor FETCH NEXT FROM myCursor INTO ... CLOSE myCursor DEALLOCATE myCursor This works fine, if there's only one instance of the stored procedure running at a time. Should another user connect to the site and run a search while someone's at it, it'll fail due to the atempt to create a cursor with the same name. My first thought was to make the cursor name unique, which led me to: ... SET @cursorName = 'myCursor' + @uniqueUserID SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery ... The problem with this is that I can't do a FETCH NEXT FROM @cursorName since @cursorName is a char variable holding the cursor name, and not a cursor variable. So to enforce this unique name method the only option I have is to keep creating dynamic sql queries and exucting them. And this makes the sp a bitch to develop and maintain, and I'm guessing it doesn't make it very performant. So I moved on to my second idea: local cursor variables. The problem with this is that if I create a local cursor variable by executing a dynamic query, I can't extract it from the EXEC (or sp_executesql) context, as it offers no output variable. I guess my concrete questions are: Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How? Anybody sees another way arround this?Thanks in advance, Carlos
View Replies !
Using A &"dynamic Top&" Statement With A Cursor
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper
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 !
Static Variables In A SQLCLR Stored Proc Seem To Get Reused From Execution To Execution Of The Sp
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View Replies !
Execution Plans &<&> Proportionate Execution Times
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite 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 !
Fetch Within A Fetch
Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures? CREATE PROCEDURE TrigSendPreNewIMAlertP2 @REID int AS Declare @RRID int Declare @ITID int Declare @intIMEmail varchar(300) Declare crReqRec cursor for select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null open crReqRec fetch next from crReqRec into @RRID Declare crImpGrp cursor for select ITID from RequestRecords where RRID = @RRID open crImpGrp fetch next from crImgGrp into @ITID while @@fetch_status = 0 EXEC TrigSendNewIMAlertP2 @ITID FETCH NEXT FROM crImpGrp into @ITID close crImpGrp deallocate crImpGrp while @@fetch_status = 0 FETCH NEXT FROM crReqRec into @RRID close crReqRec deallocate crReqRec GO
View Replies !
My Dog Won't Fetch
Fellow SQL developers: I'm trying to do some segmentation of records into percentiles, and am afraid I need to use cursors <ugh>. Since I'm never sure of the number of rows that need to be ranked, my thought was to do the following: Select @rowcount = count(*) from tablex Select @rowblock = @rowcount/10 Declare xx_cursor scroll cursor for select TotSales from tablex order by TotSales desc Open xx_cursor Fetch absolute @rowblock from xx_cursor into @TotSales Update tablex set SalesRank = 1 where TotSales >= @TotSales and SalesRank is null etc. The problem is with my Fetch Absolute N contsruct. SQLServer expects an integer, rather than a variable here. I've got this working with loops and lots of Fetch Next's, but that seems like extra work to me. Is there anything I'm missing here, or should I just let sleeping dogs lie? Jim Kunz Kunz, Leigh & Associates J.Kunz@handleman.com
View Replies !
Execution In Scheduled Job Vs Direct Execution
Here's my case, I have written a stored procedure which will perform the following: 1. Grab data from a table using cursor, 2. Process data, 3. Write the result into another table If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails. I used the same record, same parameters, and the same statements to call the stored procedure. Any idea?
View Replies !
Data Fetch
Hello.. i develope a web projects of horoscope or astrology(http://demo.reallianzbussimart.com/allzodiac.aspx), there is an 12 Zodiaz sign and all the data call on this page through the Database , in this page i there is an one sql query ---- (Select Top 1 col from colour order by newid(),Select Top 1 num from number order by newid() ,Select Top 1 days from day order by newid() ) then all data call one by one change .. when the refresh the page the value of all zodiac is change that is wrong ,,, so what is the quary that one time in day the value off all zodiac is same next day the value has been change............................................ so please help me /........... tell what is the process to not data change into the page refresh Ashwnai
View Replies !
|
TRACKER
