Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
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 !
Update Record In A Cursor
Please tell me how to code the Update of the current cursor record as one would do using VD/ADO : VB: Table("Fieldname") = Value ---------------------------------------------------------- Declare @NextNo integer Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1 --Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone Open Clone_Cursor Fetch Next from Clone_Cursor WHILE @@FETCH_STATUS = 0 BEGIN Select @NextNo = @NextNo + 1 Clone_Cursor("ABAN8") = @NextNo Update Clone_Cursor FETCH NEXT FROM Clone_Cursor END CLOSE Clone_Cursor DEALLOCATE Clone_Cursor GO
View Replies !
Cursor/variable Help
I'm not sure about this one so if someone could help I'd appreciate this. As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a) and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.
View Replies !
How To Use Variable In A Cursor
if i have the cursor cursor: cursor for select name from sysobjects where type='P' AND category='0' and wants a variable before sysobjects which equals a table name, so itll be: cursor for select name from @variable..sysobjects where type='P' AND category='0' how do i include a variable within a cursor statement
View Replies !
Bind Variable In CURSOR
SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.I have a stored procedure which is not working the way I think itshould be.I have a CURSOR which has a variable in the WHERE clause:DECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameIt won't return anything, even when I verify that @dbname has a valueand if I run the query in Query Analyzer with the value, it returnsrows:SELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = 'Archive'ORDER BY tablenameDB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-2909:52:11.823DtsAdtStdArchive_DataSourceType5175768822006-03-2909:52:11.8702006-03-29 09:52:11.887DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-2909:52:12.103I've taken out most of the guts for simplicity, but here's what I'vegot:--CREATE TABLE dbo.db_ind--(--db_ind_tkintIDENTITY,-- id int NULL,-- tablename sysname NOT NULL,-- indid int NULL,-- indexname sysname NOT NULL,-- shcontig1dt datetime NULL,-- defragdt datetime NULL,-- shcontig2dt datetime NULL,-- reindexdt datetime NULL--)ALTER PROCEDURE IDR(@hours int)AS--SET NOCOUNT ON--SET ANSI_WARNINGS OFFDECLARE @tabname varchar(100),@indname varchar(100),@dbname varchar(50),@vsql varchar(1000),@v_hours varchar(4),@shcontig1dtdatetime,@shcontig2dtdatetime,@defragdtdatetime,@reindexdtdatetime,@idint,@indidint,@rundbcursorint,@runtabcursorint,@runindcursorintDECLARE get_dbs CURSOR local fast_forward FORSELECT dbnameFROM db_jobsWHERE idrdate < getdate() - 4or idrdate is nullORDER BY dbnameDECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameDECLARE get_inds CURSOR local fast_forward FORSELECT indid, indexname, defragdt, reindexdtFROM db_indWHERE dbname = @dbnameAND tablename = @tabnameORDER BY indexnameOPEN get_dbsFETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0SELECT @v_hours = CONVERT(varchar,@hours)--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====WHILE @rundbcursor = 1BEGIN -- db whilePRINT '============================='PRINT @dbnamePRINT '============================='--================================================== ================================================== =====--================================================== ================================================== =====OPEN get_tabsFETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0BEGINPRINT 'table: ' + @tabnameSELECT @runtabcursor = 1endELSEBEGINPRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITSSELECT @runtabcursor = 0endWHILE @runtabcursor = 1BEGINPRINT @dbnamePRINT @tabname--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END-- 1st loop through indexesCLOSE get_inds--================================================== ================================================== =====--==========PRINT 'db.tab: ' + @dbname + '.' + @tabname--==========--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'dbname: ' + @dbnamePRINT 'tabname: ' + @tabnamePRINT 'indname: ' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END -- 2nd loop through indexesCLOSE get_inds--================================================== ================================================== =====FETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0SELECT @runtabcursor = 1ELSESELECT @runtabcursor = 0END-- loop through tablesCLOSE get_tabs--================================================== ================================================== =====--================================================== ================================================== =====PRINT 'Index Maintenence complete. Job report in[DB_Rpt_Fragmentation]'PRINT ''FETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0END -- loop through databasesCLOSE get_dbsdeallocate get_dbsdeallocate get_tabsdeallocate get_inds--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====GOAnd this is what I'm getting:=============================Archive=============================(0 row(s) affected)not getting any tables!Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]......etc.Am I missing something obvious?Thank you for any help you can provide!!
View Replies !
Set Value For Variable In A Declared Cursor
Hi, I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue. ------------------------------------------------------------------------ DECLARE @str_var VARCHAR(10) DECLARE @field_val VARCHAR(10) DECLARE c1 CURSOR LOCAL FOR SELECT field1 FROM tableA WHERE field1 = @str_var WHILE (Sometime TRUE) BEGIN .... SET @str_var = 'set to some values, eg. ABC123, XYZ123' OPEN c1 FETCH c1 INTO @field_val WHILE (@@fetch_status != -1) BEGIN PRINT @field_val ... FETCH c1 INTO @field_val END CLOSE c1 END DEALLOCATE c1 ---------------------------------------------------------------------- Thanks a lots, Vincent
View Replies !
Using Cursor Variable As A Tablename
I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns. I then need to run the following script: declare Detail_Cursor cursor for select @colname, max(len(@colname)) from @table The message I receive is "must declare variable @table". Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated. Can anyone let me know what I'm doing wrong or how I can get the same result. Thanks
View Replies !
Cursor Variable Problem
My code below is supposed to pull the destination and then work out 2 calculations per month on each destination. The results returns the string '@destination' instead of the value from the table and also the month value is returned completely wrong. My main problem though is the getting the correct destination to return. If anybody has any pointers I would be very grateful. This is driving me mad!! Thanks a lot in advance Here some sample code: DECLARE @Destination char(50) DECLARE AVGCallsToDest INSENSITIVE scroll CURSOR FOR SELECT destination from table1 OPEN AVGCallsToDest FETCH First FROM AVGCallsToDest INTO @Destination WHILE (@@FETCH_STATUS = 0 ) BEGIN exec("Insert into NewTable(Card, [Month], NumberOfCalls, AverageLength, Destination) select 'Phone Card' as 'Card', DATEPART(mm,adetdate) as 'Month',count(*) as 'NumberOfCalls', avg(bdur)/60 as 'AverageLength', "" +@Destination+ "" as 'Destination' from table1 (nolock) whereDATEPART(mm,adetdate) = 5 and DATEPART(yyyy,adetdate) = DATEPART(yyyy,getdate()-1) group by DATEPART(mm,adetdate),bdur order by Month") FETCHNext FROM AVGCallsToDest INTO@Destination End CLOSE AVGCallsToDest DEALLOCATE AVGCallsToDest
View Replies !
'One Table' Record To Separate 'two Or Three Tables' Using Cursor?
I would like to 'one table' record to separate 'two or three tables' . I just know use the DTS , try to import and export again and agian. So trouble. Could you give me some suggestions for me? For example , 'Cursor' write in new table . But I try to SQL Server Books Online which is not suitable for me solving problems. One table separate two or three tables. Can you wirte the detail example for me? Thx a lot.
View Replies !
How To Iterate Through Table Variable Without Using Cursor
Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...
View Replies !
Variable As Column In Cursor Select
I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example: declare @col varchar(50) set @col = 'Temperature' declare notifycurs cursor scroll for select @col from Table Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC (ie. exec('select '+@col+' from Table' ) but that gave me an error. Is there a way to pass in a variable for a column name for a curor select statement????
View Replies !
Variable As Field Name In CURSOR FOR UPDATE
I'm trying something like: UPDATE tbl SET @varFieldName = @varValue The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd. I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then. To get the field name, I : SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars' Thanks, Carl
View Replies !
Adding Variable To SELECT For CURSOR
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable. The following fails to parse: Declare Cursor1 Cursor For 'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name' Open cursor1 That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.
View Replies !
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.
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 !
How To Read The Rows In A Cursor Variable
Hi, I have a dynamic query that returns its values in a cursor variable. How do I read each row from this cursor in a loop ? Eg.: use AdventureWorks go DECLARE @sqlnvarchar(4000), @paramsnvarchar(4000), @tables_cursorcursor, @db_namenvarchar(50), @table_namenvarchar(4000), @schema_namenvarchar(50); set @db_name = 'AdventureWorks'; set @schema_name = 'Production'; set @table_name = 'BillOfMaterials, Product'; set @sql = ' select a.name table_name ' + ' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' + ' on (a.schema_id = b.schema_id) ' + ' where b.name= @schema_name1 ' + ' and @table_name1 is null ' + ' order by 1; ' SELECT @params = N' @table_name1 nvarchar(3000) ,' + N' @schema_name1 nvarchar(100) ,' + N' @cursor cursor output' EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT
View Replies !
Variable Assignment In Cursor Declaration
Hi, here is the code segment below; ... DECLARE find_dates CURSOR FOR SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC' EXEC (@SQL) but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help. thanks
View Replies !
How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim
View Replies !
Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such: SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2' Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as: EXEC (@BuildDBPageUsed) The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed? *Thanks* for any help in this matter. Brad
View Replies !
In Cursor: Help To Perform Proper Comparison Using Variable
Hi All, I failed to find record when using variable in cursor in WHERE clause: ID is uniqueidentifier field in the table DECLARE @EncounterID uniqueidentifier ........ WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger WHERE ID = 'E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012' -> this works fine and record found Tried to CAST(@EncounterID as char(40)) but still no luck. I would greatly appreciate any advise hot to make it working. Thank you very much in advance Roman
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 !
Question About Assigning Cursor Variable Data Types...
Hi, I'm just curious about something...when assigning a datatype to a cursor variable in ORACLE, you can use the keyword TYPE to automatically grap the datatype from the associated column (or use ROWTYPE to create a cursor variable to represent an entire row in a table). It's so much easier and if the datatype of one of the cursor columns is altered, the associated cursor variable assumes the new datatype. Is there a way to do this in SQL Server 7.0 ? many thanks :)
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
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 !
Miss Match Between Column Type In A Cursor. Msg 8114, Level 16, State 5
hi I have a float column . only in cursor I get a type missmatch. does any one knows it ? the error Msg 8114, Level 16, State 5, Line 14 Error converting data type varchar to float. the code DECLARE @RON_FLOAT FLOAT DECLARE RON_CURSOR CURSOR FOR SELECT RON_FLOAT FROM RON_TABLE1 OPEN RON_CURSOR FETCH NEXT FROM RON_CURSOR INTO @RON_FLOAT WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'VALUE IS ' + @RON_FLOAT FETCH NEXT FROM RON_CURSOR INTO @RON_FLOAT END CLOSE RON_CURSOR DEALLOCATE RON_CURSOR the code for the table CREATE TABLE [dbo].[RON_TABLE1]( [RON_FLOAT] [float] NULL, [RON_CHAR] [nchar](10) COLLATE Hebrew_BIN NULL )
View Replies !
SQL Server 2005 Issue - The Cursor Type/concurrency Combination Is Not Supported.
Hi I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this. Below is the exception from my log file The cursor type/concurrency combination is not supported. com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source) The following is the piece of code where the problem I'm assuming is happening, how can I correct it. varStmt1 = varConnection.prepareStatement(varCitationSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); Have tried using both JDC v1.1 and 1.2 but of no use.
View Replies !
Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors declare Q cursor for select systudentid from satrans declare @id int open Q fetch next from Q into @id while @@fetch_status = 0 begin declare c cursor for Select b.ssn, SaTrans.SyStudentID, satrans.date, satrans.type, SaTrans.SyCampusID, Amount = Case SaTrans.Type When 'P' Then SaTrans.Amount * -1 When 'C' Then SaTrans.Amount * -1 Else SaTrans.Amount END From SaTrans , systudent b where satrans.systudentid = b.systudentid and satrans.systudentid = @id declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money set @arbalance = 0 open c fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount while @@fetch_status = 0 begin set @arbalance = @arbalance + @amount set @before = @arbalance -@amount insert c2000_utility1..tempbalhistory1 select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance where( convert (int,@amount) <= -50 or @amount * -1 > @before * .02) and @type = 'P' fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount end close c deallocate c fetch next from Q into @id end close Q deallocate Q select * from c2000_utility1..tempbalhistory1 truncate table c2000_utility1..tempbalhistory1
View Replies !
Client Side Cursor Vs Sever Side Cursor?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim
View Replies !
Variable In CURSOR Sql Statement (was &"Please Help Me&")
Hi All, What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @Where. (I tried with DEBUGGING option of Query Analyzer also). ============================================= SET @Where = '' if IsNull(@Input1,NULL) <> NULL Set @Where = @Where + " AND studentid='" + @input1 +"'" if isnull(@Input2,NULL) <> NULL Set @Where = @Where + " AND teacherid =' " + @Input2 +"'" DECLARE curs1 CURSOR SCROLL FOR SELECT firstname FROM school WHERE school ='ABC' + @where ============================================= Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ? Thanks !
View Replies !
How Cursor Used In Asp.net
hii have creted cursor but i want to use in my asp.net programming when some insert or delete command is work that time i want to excute my cursor how can i do that using asp.net with c# waiting for replaythanks
View Replies !
Is This Possible Without A Cursor?
I have something like update table set field = ... where field = ... and for each entry that was effected by this query I want to insert an entry into another table. I have always done this with cursors is there a more effecient way? For some reason cursors run a lot slower on my sql2005 server than the sql2000 server...
View Replies !
Use Of Cursor
I need some help with the concept of a Cursor, as I see it being used in a stored procedure I need to maintain. Here is some code from the stored proc. Can someone tell me what is going on here. I haveleft out some of the sql, but have isolated the Cursor stuff. Open MarketCursor -- How is MarketCursor loaded with data ? FETCH NEXT FROM MarketCursorINTO ItemID, @Item, @Reguest WHILE @@FETCH_STATUS = 0BEGIN DEALLOCATE MarketCursor
View Replies !
Cursor For
Hi, Declare wh_ctry_id CURSOR FOR Is "cursor for" is a function or datatype or what is this? Regards Abdul
View Replies !
Need Help With A Cursor
I hope this is the appropriate forum for this question, if not then I apologize. I've got a SQL Server 2000 stored procedure that returns data to be used in a crystal report in Visual Studio 2005. Most of the stored procedure works well, but there is a point where I need to calculate an average number of days been a group of date pairs. I'm not familiar with cursors, but I think that I will need to use one to achieve the result I am looking for so I came up with the code below which is a snippet from my stored procedure. In this part of the code, the sp looks at the temporary table #lmreport (which holds all of the data that is returned at the end to crystal) and for every row in the table where the terrid is 'T' (the territory is domestic), it selects all of those territories from the territory table and loops through them to determine the date averages (by calling a nested stored procedure, also included below) for each territory and then updates #lmreport with that data. When I try to run the stored procedure, I get "The column prefix '#lmreport' does not match with a table name or alias name used in the query." on the line indicated. Does anyone have any idea what might be wrong or if this will even work the way I need it to? Thank you in advance.
View Replies !
Need Cursor Help
Hello, I'm trying to construct a cursor that will sequentually increment a number and then update a column with the incremented number. My propblem is that all the rows in the table are being updated with the base number +1. So all rows are updated with 278301. BUT, what I really want is for only the items with adrscode of 'bill to' to be given an incremented number. For example, if there are only five rows of 100 with an adrscode = 'bill to' then only five rows will be updated and the value of the custnmbr should be, 278301, 278302, 278303 ..... I could really use some help with this cursor: Declare @CustomerName as char (60), @seqno as int, @BaseSeqno as intset @Baseseqno = 278300 declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbropen cfetch next from c into @CustomerNamewhile @@fetch_status=0begin set @seqno = @BaseSeqno + 1 update NXOFcustomers set custnmbr = @seqnoWhere custnmbr = @CustomerName fetch next from c into @CustomerNameend close cdeallocate c
View Replies !
Cursor And UDf
Hello: I am trying to define a cursor as follows: DECLARE EmployeeList CURSOR FOR dbo.GetRecord(@EmployeeID,@CurrentDate)Can't I use a UDF in the CURSOR FOR ?Help please.thank you.
View Replies !
Is Cursor Best Way To Go?
I need to get two values from a complex SQL statement which returns a singlerecord and use those two values to update a single record in a table. Inorder to assign those two values to variables and then use those variablesin the UPDATE statement, I created a cursor and used Fetch Next.... Into.This way, I only have to call the complex SQL once instead of twice.This seems like the best way to go. However, I've always used cursors forscrolling through resultsets. In this case, though, there is just a singlerecord being returned, and the cursor doesn't scroll.Is that the most efficient way to go, or is there a better way to be able touse both values from the SQL statement without having to call it twice?Thanks.
View Replies !
To Use A Cursor Or To Not Use A Cursor
I need to loop through a set of records to build a string. I can dothis without using a cursor by inserting the records into a temporarytable with an identity column. Count the number of records in thetemporary table and loop though the table selecting the values andbuilding the string where the identity column = the loop number.Is this more or less efficient than just using a cursor? If so why isit more or less efficient?Please explain in detailThank You,Jim Lewis
View Replies !
Cursor Help PLEASE!
Yes, I know that cursors are gauche, but I can't see a solution usingqueries and I'm pretty adept at them. This will be running once a dayin the wee small hours when minimal server activity will be takingplace, it will be handling 700-1000ish records. The box is a P4/SQL2000 with lots of ram and multiple CPUs.The code for creating and populating the table in question follows thecursor code.The application is for medical transport billing. We take people tothe doctor and home again and the health plan pays us. A one-waytrip, home -> doctor, is a single ‘line item'. A round trip, home ->doctor -> home is rolled up, the charge summed, and billed as a singleline item. A three-leg, home -> doctor -> pharmacy -> home, is billedas three line items.This code is just for identifying the data, once this works correctlyI'll add the code in for doing the rollup and I'm confident I canhandle that.If I ruled the world, every leg of every trip would be a single lineitem and I wouldn't have to deal with this rollup BS, but I don't makethe policy, I just have to code it. <g>A trip is a round trip if the date, account number, and passenger nameis the same and the origin street of record X equals the destinationstreet of record X+1. The problem is that X+1 could be the start of around trip and X+2 could be the completing leg of a round trip. Soyou could have a scenario of hospital -> home -> doctor -> home inwhich X is one-way and X+1 & X+2 form a round trip.BLERG! Any help is greatly appreciated, it has me stumped./*select trip_id, acct_number, passenger, street, dest_street,flat_rate, screated, smeteroff, remark1, remark2from zvoucherstodbf order by cast(screated as smalldatetime),acct_number, passenger, smeteroff*/declare @roundtrip integerdeclare @cmsg char(200)declare @Trip_ID char(11)declare @acct_number char(11)declare @passenger char(12)declare @created char(22)declare @meter_off char(22)declare @street char(32)declare @dest_street char(32)declare @remark1 char(32)declare @remark2 char(32)declare @flat_rate smallintdeclare @prev_cmsg char(200)declare @prev_Trip_ID char(11)declare @prev_acct_number char(11)declare @prev_passenger char(12)declare @prev_created char(22)declare @prev_meter_off char(22)declare @prev_street char(32)declare @prev_dest_street char(32)declare @prev_remark1 char(32)declare @prev_remark2 char(32)declare @prev_flat_rate smallintdeclare cVoucher cursor scroll forselect trip_id, acct_number, passenger, street, dest_street, screated,smeteroff, remark1, remark2, flat_ratefrom zVouchersToDBF--where screated = '12/03/03'order by screated, acct_number, passenger, smeteroffopen cVoucher--Load the @Prev_ (previous record) variables with the first recordfetch from cVoucher into @prev_trip_id, @prev_acct_number,@prev_passenger,@prev_street, @prev_dest_street, @prev_created, @prev_meter_off,@prev_remark1, @prev_remark2, @prev_flat_rate--Initialize current variablesselect @trip_id = @prev_trip_idselect @acct_number = @prev_acct_numberselect @passenger = @prev_passengerselect @street = @prev_streetselect @dest_street = @prev_dest_streetselect @created = @prev_createdselect @meter_off = @prev_meter_offselect @remark1 = @prev_remark1select @remark2 = @prev_remark2while @@fetch_status = 0--not EOFbeginselect @roundtrip = 0if (@prev_created = @created) and (@prev_acct_number = @acct_number)and (@prev_passenger = @passenger)and (@prev_street =@dest_street)-- MATCH! Apparent round tripbeginselect @roundtrip = 1select @prev_trip_id = @trip_idselect @prev_acct_number = @acct_numberselect @prev_passenger = @passengerselect @prev_street = @streetselect @prev_dest_street = @dest_streetselect @prev_created = @createdselect @prev_meter_off = @meter_offselect @prev_remark1 = @remark1select @prev_remark2 = @remark2endif (@roundtrip = 0)beginfetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateif (@prev_created = @created) and (@prev_acct_number = @acct_number)and (@prev_passenger = @passenger)and (@prev_street =@dest_street)-- MATCH! Apparent round tripselect @roundtrip = 1else--definitely one-way tripselect @roundtrip = 0fetch prior from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendif (@roundtrip = 1)beginselect @cMsg = 'Start: ' + rtrim(@prev_created) + ' ' +rtrim(@prev_trip_id) + ': ' + @prev_acct_number + ' ' +rtrim(@prev_meter_off) + ' ' + @prev_passenger + ' ' +rtrim(@prev_street) + ' to ' + @prev_dest_street + ' ' + @prev_remark1+ ' ' + @prev_remark2print @cMsgselect @cMsg = 'End: ' + rtrim(@created) + ' ' + rtrim(@trip_id) +': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + ' '+ rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +@remark2print @cMsgprint ''endelsebeginselect @cMsg = 'One-Way: ' + rtrim(@created) + ' ' + rtrim(@trip_id)+ ': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + '' + rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +@remark2print @cMsgprint ''endselect @prev_trip_id = @trip_idselect @prev_acct_number = @acct_numberselect @prev_passenger = @passengerselect @prev_street = @streetselect @prev_dest_street = @dest_streetselect @prev_created = @createdselect @prev_meter_off = @meter_offselect @prev_remark1 = @remark1select @prev_remark2 = @remark2fetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendclose cVoucher--/*--This code works for displaying the data set as a whole-- so you can visually identify what constitutes a round trip.open cVoucherfetch from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_ratewhile @@fetch_status = 0beginselect @cMsg = rtrim(@created) + ' ' + rtrim(@trip_id) + ': ' +@acct_number + ' ' + @passenger + ' ' + @street + ' ' + @dest_street +' ' + @meter_off + ' ' + @remark1 + ' ' + @remark2print @cMsgfetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendclose cVoucher--*/deallocate cVoucherCREATE TABLE [zVouchersToDBF] ([house] [char] (5) NULL ,[street] [char] (32) NULL ,[district] [char] (8) NULL ,[passenger] [char] (12) NULL ,[remark1] [char] (32) NULL ,[remark2] [char] (32) NULL ,[dest_house] [char] (5) NULL ,[dest_street] [char] (32) NULL ,[dest_district] [char] (13) NULL ,[acct_number] [char] (11) NULL ,[sub_acct_number] [char] (15) NULL ,[flat_rate] [money] NULL ,[car] [char] (3) NULL ,[driver_id] [char] (9) NULL ,[meter_on] [char] (5) NULL ,[meter_off] [char] (5) NULL ,[fare] [char] (9) NULL ,[cancelled] [char] (21) NULL ,[no_trip] [char] (7) NULL ,[no_trip_reason] [int] NULL ,[auth_number] [char] (32) NULL ,[auth_name] [char] (32) NULL ,[trip_id] [char] (7) NULL ,[created] [char] (8) NULL ,[patient_birthday] [char] (16) NULL ,[waittime] [char] (3) NULL ,[sNoTrip] [char] (22) NULL ,[sMeterOn] [char] (22) NULL ,[sMeterOff] [char] (22) NULL ,[sCancelled] [char] (22) NULL ,[sCreated] [char] (22) NULL ,[sPatientBirthday] [char] (22) NULL ,[SystemRate] [money] NULL) ON [PRIMARY]GOInsert zvoucherstodbfValues (null,'100 E 1st',null,'A','X','Y',null,'200 W2nd',null,'1000',null,5.00,null,null,null,'13:20', '5',null,null,null,null,null,'1',null,null,null,nu ll,null,'13:20',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'200 W 2nd',null,'A','X','Y',null,'100 E1st',null,'1000',null,5.00,null,null,null,'14:20', '5',null,null,null,null,null,'2',null,null,null,nu ll,null,'14:20',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'101 E 101',null,'B','X','Y',null,'202 W202',null,'2000',null,10.00,null,null,null,'13:50' ,'10',null,null,null,null,null,'3',null,null,null, null,null,'13:50',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'123 N 456',null,'C','X','Y',null,'234 S321',null,'2000',null,5.00,null,null,null,'13:51', '5',null,null,null,null,null,'4',null,null,null,nu ll,null,'13:51',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'999 N 666',null,'D','X','Y',null,'666 S999',null,'1000',null,7.50,null,null,null,'14:00', '7.5',null,null,null,null,null,'5',null,null,null, null,null,'14:00',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'666 S 999',null,'D','X','Y',null,'999 N666',null,'1000',null,8.00,null,null,null,'14:30', '8',null,null,null,null,null,'6',null,null,null,nu ll,null,'14:30',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'123 n 456',null,'E','X','Y',null,'456 s789',null,'3000',null,5.00,null,null,null,'14:30', '5',null,null,null,null,null,'7',null,null,null,nu ll,null,'14:30',null,'12/8/2003',null,null)
View Replies !
What Is A Cursor?
I had a friend write a stored procedure to perform a function for oneof my clients. What he wrote doesn't fully do what I need, and I hopeto finish it myself. I have programming sense, but not so much withSQL.I'm trying to figure out the code, and he has used something called a"cursor." I'm not sure whether this is an SQL construct or a structurethat he has just labeled "cursor." My guess is that it is an SQLconstruct. Can anyone give me a quick run down of how this works?sincerely,Tyler H.-----------------------------------------------<a href="http://www.seearoomhawaii.com/bed-breakfasts/">bed &breakfasts in Hawaii</a>
View Replies !
It Is Possible With Cursor?
I need to fill a cursor with 3 columns. A want to use a Select sprocs (for re-use de code), but this sproc return 15 columns and the 3 a need was not the 3 frist. :confused: Do I need to map the 15 columns with 15 variables locally? Or they have a way easier? Thanks
View Replies !
Can I Use SQL Instead Of Cursor?
Hi, I'm currently converting a VB function to SQL-Server. The function uses a cursor to find the "terms of delivery" (TOD) with the highest priority. I have a table with articlenumber, tod (and lots of other columns that doesn't matter now) ABC123 , AFG ABC123 , AFG ABC123 , BGH ABC123 , BGH ABC123 , CDD "CDD" has the highest priority and therefore ALL with the same articlenumber should use that tod. The existing function uses a cursor and loops through a recordset and updates every row with the same articlenumber as the current row with the tod with the highest priority (of the ones read) with the same articlenumber. One update per row takes "forever" to run... I figured it would be possible to select the tod with the highest priority for one articlenumber into a temp table and then do ONE update to set the tod on all rows...
View Replies !
Can't Use Cursor With SP
Hi I have a SP and in it I call another SP which returns one row in one column, I need to concatenate the value (varchar) to the query in the first SP. I tried to use cursor with FAST_FORWARD to fetch the result and concatenate it, but I get an error, here is what I tried: DECLARE Cur CURSOR FAST_FORWARD FOR SP_Something @SomeValue So is it possible to use cursor on SP ? And if it's possible so how ??? Thanks, Inon.
View Replies !
Get Value From CURSOR
I'm writing a stored procedure that involves looping through a recordset and using the values from the recordset as parameters for a second stored procedure. Here's what I've got so far... My question is, how do I get the value out of the Cursor? There's only one field. Declare @Day as int Declare @Plant as varchar(30) SET NOCOUNT ON CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime) Declare Facility_Cursor CURSOR For Select Distinct(Facility) From ProductionHistory OPEN Facility_CURSOR Declare @Facility_Cursor as sysname FETCH NEXT From Facility_CURSOR into @Facility_Cursor WHILE @@FETCHSTATUS = 1 --YESTERDAY Set @Day = -2 Insert Into #Temp exec sp_GetDailyProductionByPlantAndCategory @Day, @Facility, 'NAP' --TODAY SET @Day = -1 Insert Into #Temp exec sp_GetDailyProductionByPlantAndCategory @Day, @Facility, 'NAP' FETCH NEXT FROM Facility_CURSOR into @Facility_Cursor CLOSE Facility_Cursor DEALLOCATE Facility_CURSOR SET NOCOUNT OFF Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESC
View Replies !
How To Do This Without Using A Cursor?
I am in the last stages of designing a forecasting "engine" for my company, and I'm stuck on something that seems simple in comparison to everything else I've done so far. I have product ABC, and it's total sales forecast is 15 units. I split the forecast into 2 different locations, based on an established percentage. In this case, I'll say 67% in location 'OH', and 33% in location 'AL' That's 10 units 'OH' and 5 units 'AL'. Then I get my actual orders by location, and compare them to the forecast. If the orders exceed the forecast, I'll use orders, otherwise, I use forecast. Whenever I do that, I need to reduce the forecast for the other location, in order to keep the total forecast of 15 whole. (It is not possible for total orders to exceed total forecast, I've already dealt with that.) CREATE PROCEDURE tempSelect AS CREATE TABLE #tmpTest ( parent char(2), proj_ship real, open_ord real, ) insert into #tmpTest (parent, proj_ship, open_ord) select 'OH', 10, 4 insert into #tmpTest (parent, proj_ship, open_ord) select 'AL', 5, 7 SELECT PARENT, 'UNITS' = ??? FROM #TMPTEST DROP TABLE #TMPTEST GO I need help with '???' in the query. The result set I am looking for is: OH 8 AL 7
View Replies !
Trying To NOT Use A Cursor...
I need to write a sproc to supply records for a report. The boss has asked "Of all the tons on order right now, how much is already in inventory, and how much needs to be produced." And "Apply the same logic to just the orders that came in yesterday." It would have been easy, if he hadn't asked for the second part, because now I have to look at each product on each order, rather than comparing total orders for a product to total available inventory. Here's some sample data for what I need to do: CREATE TABLE ORDER_ITEM ( ORDER_NUM VARCHAR(10), SHIP_DATE SMALLDATETIME, PRODUCT VARCHAR(10), ORD_TONS REAL) INSERT INTO ORDER_ITEM (ORDER_NUM, SHIP_DATE, PRODUCT, ORD_TONS) SELECT '001', '3/1/2006', 'ABC', 4 UNION ALL SELECT '002', '3/4/2006', 'ABC', 2 UNION ALL SELECT '002', '3/4/2006', 'DEF', 6 UNION ALL SELECT '003', '3/7/2006', 'DEF', 8 CREATE TABLE PROD_INVENTORY ( PRODUCT VARCHAR(10), INV_TONS REAL) INSERT INTO PROD_INVENTORY (PRODUCT, INV_TONS) SELECT 'ABC', 5 UNION ALL SELECT 'DEF', 13 The final recordset needs to be something like: PRODUCT ORDER_NUM ORD_TONS SFI SFP END_INV ABC 001 4 4 0 1 ABC 002 2 1 1 0 DEF 002 6 6 0 7 DEF 003 8 7 1 0 SFI = Sales from Inventory SFP = Sales from production I need a little help in how to do a running inventory balance (END_INV) for each item. Once I have that, then I can calculate SFI and SFP. I could figure out how to do it with a cursor, but it would probably be pretty slow. I'll have about 10,000 records to sort thru, and of course there will be more columns than what I show here. Any ideas would be appreciated.
View Replies !
|
TRACKER
