Transact SQL :: Alternative For Cursor
Oct 28, 2015
I have a Stored Procedure. In that SP, I am calling 10 table-valued user defined function to calculate different pricing charges (lets say delivery charge, fuel surcharge, etc). In that SP, there is one Temporary table to store the data required for calculating charges and some columns will hold the calculated data in the same temp table.
1. First step is loading the columns in Temporary table in the SP which are required for calculating charges (will be having 1000 records for example)
2. Second step is to calculate charges ( delivery charge, fuel surcharge, etc). for each rows(1000 rows) from the temporary table using table-valued user defined functions one by one*Call function to calculate DeliveryCharge in the SP and calculate DeliveryCharge for all 1000rows. For this step i am using cursor to loop through 1000records and find DeliveryCharge for each row and update it in the DeliveryCharge column in the same temporary table. I am using 10 cursors for 10 different price calculations in the SP
3. Finally, the SP will return that 1000 records with calculated prices. The question is how to avoid Cursor for these operations. How to pass all 1000 records to a function and get table valued results from that function and update those results in the Temporary table without using cursors?
View 5 Replies
ADVERTISEMENT
Mar 30, 2006
Hi,
In a stored procedure I'm processing, via a cursor, a table of, potentially, 100,000 rows on a daily basis. The only column in each row is a 12-byte transaction control number. I know that using cursors can cause performance issues. Is there an alternative to using a cursor that has less of a performance impact ?
Thanks,
Jeff
View 5 Replies
View Related
Jul 18, 2006
Hi All,
Beside cursor, what else can i use to speed up my processing? Now i have about 2mils rows need to update using one daily reference table(30k rows).
Thanks,
Jack
View 10 Replies
View Related
Oct 23, 2006
Hi all
I wanted to workout with while loop as an alternative of cursor. I am working in SQL Server 2005.
I have a table TEST with following values
tid tname
1 John
2 Sam
3 Peter
I wanted to insert these records into same table or another table using following while loop. But I ended of inserting only last record in the loop, not each one above.
--DELETE FROM test1
DECLARE @id int,
@lc int,@rc int
--SET ROWCOUNT 0
SELECT tid,tname INTO #Temp FROM Test
SET @rc=@@ROWCOUNT
--SET ROWCOUNT 1
--SELECT @id=tid FROM #TEMP
SET @lc=1
WHILE @lc<=@rc
BEGIN
SELECT @id=tid FROM #TEMP
INSERT INTO TEST
SELECT @id,'ppp'
FROM (SELECT tid FROM #TEMP WHERE tid=@id) T
--SELECT * FROM #Temp
SET @lc=@lc+1
END
Any help how to insert each record using while loop will be much appreciated.
Thanks all
Vict
View 3 Replies
View Related
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Jul 1, 1999
Hi all,
I need to run a long process in a cursor. I'd like to perform a special action every 10,000th row. How would you implement that using Transact-SQL?
Many thanks,
-Kevin Kline
View 1 Replies
View Related
Jun 16, 2015
Am I using the cursor feature properly here? -- of course there would be actual processing (replacing a while loop) going on and not simple print statements.
Declare @BadgeNumber varchar(20), @name varchar(100), @phone int, @status varchar(25)
Declare cursor Cursor For
Select jt.BadgeNumber, tj.Name, jt.Phone, zt.status
From employees jt
join employeestatus zt
On jt.id = zt.id
[code]....
View 6 Replies
View Related
Jun 16, 2015
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
View 5 Replies
View Related
Sep 14, 2015
I write few lines to do a bottom-up calculation, with 'fetch last' and 'fetch prior'.
It seems that the condition 'WHILE @@FETCH_STATUS = 0' does not work when cursor arrives at the first line, as there is an error message:
'(1 row(s) affected)
6255.84
(1 row(s) affected)
Msg 16931, Level 16, State 1, Line 18
There are no rows in the current fetch buffer.
The statement has been terminated.'
how to fix the error?
Here is my code:
DECLARE @lastprice real
DECLARE @updatedprice real
DECLARE @updatedRe real
DECLARE @updatedAUX real
SET @lastprice = (
SELECT Close_P from #ClosePrice where #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice)
)
[code].....
View 4 Replies
View Related
May 20, 2015
I have a table which table has :Identity Column (Identity), Schema name and Table_Name.
So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.
Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :
how can I write a cursor and Import Those results into to Another table.
View 3 Replies
View Related
Aug 12, 2015
I have a table which is a configuration table, I have declared cursors whereby the cursor doesn't get to all the rows in the configuration table even though there is no where clause in the select statement and the cursor ought to loop/go through every single row. Changed the cursor to the below and it started to go through all rows.
DECLARE XXX CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY TYPE_WARNING FOR
Now with the definition above, I am now having a situation whereby a column in row 1 which is a bit data type and has a value of 0, then the cursor gets to row 2 the same column but with a value of 1 and an if statement in the cursor saying
IF @row2columnX = 0 set @myval = 99
For some reason within the cursor the IF statement is being implemented even though the value in row2 columnX is = 1
I find it so weird. Is there a database property that affects the way cursors react or is there something that I am doing incorrectly ? Lastly, I would like to have a simple cursor template which simply goes through a configuration table or any table.
View 11 Replies
View Related
Jul 23, 2015
i read in the SET ROWCOUNT documentation URL.... that 'The ROWCOUNT option does not affect dynamic cursors', it does affect my dynamic cursor created in a table function which looks like this :
CREATE FUNCTION MyTableFunction()
RETURNS @MyTable TABLE (MYFIELD INTEGER)
AS
BEGIN
DECLARE @xxx INTEGER
DECLARE My_Cursor CURSOR DYNAMIC FOR
[code]...
I would like the number of rows retruned by MyTableFunction limited to 2, but NOT the inside proc cursor's select !Set Rowcount 0 is forbidden in table function. I cannot use TOP in select * from MyTableFunction instead of setting ROWCOUNT to 2. I'm using SQL Server 2008 or 2012.
View 3 Replies
View Related
Jul 15, 2015
After parsing unformatted XML file, we are loading XML in formatted for into a SQL table rows, so that SSIS can read it and load it to DW tables.
We have a flag column in the above table, which gets updated after each row is extracted successfully by the Procedure(cursor inside Proc) used in SSIS, but cursor inside Procedure is taking 16 hours to load 100k xml source files, if we remove cursor and use bulk load then it takes only 1.5 Hrs. but with bulk load we cannot update the flags.
View 3 Replies
View Related
Jun 29, 2015
The requirement is create a sql script(1 proc or cursor) which will create multiple procedures dynamically.
Table A
Col1
Col2
A
Alpha
For Example: If table A has 3 rows(distinct) so 3 procedures will be created dynamically.
Result:
1 PROC_A_ALPHA
2 PROC_B_BETA
3 PROC_C_charlie
View 6 Replies
View Related
Sep 18, 2015
I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade]
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,
[Code] ....
And below is the "test query" I'm using:
-- *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
@Numeric int, -- Numeric Grade
@Result int -- Procedure Status (0 = OK)
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,
[Code] ....
This is resulting in an output of:
A+ 97
A+ 97
C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
A+ 97
No Find
C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
View 6 Replies
View Related
Sep 29, 2015
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
View 20 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Sep 20, 2007
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 7 Replies
View Related
Sep 25, 2007
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 1 Replies
View Related
Jul 30, 2007
Hi,
i am using the DTC in my code to connect to two different servers on the network through a SQL query which is unfortunately very slow; can u please guide me with an alternative for the same
Thanks
View 17 Replies
View Related
Jul 20, 2005
SELECT *FROM organizationWHERE (departmentID = divisionID) AND (divisionID = branchID) AND(branchID = sectionID) AND (sectionID = unitID)Is there anyway I can make this query more simlified w/o repeating thesame column in the where clause?thankss/RC
View 3 Replies
View Related
Dec 30, 2007
Hi everyone,
I'm trying to come up with a replacement for @@IDENTITY, because I have SQL code I want to make more portable.
Original:ID = MyDataLayer.Execute("INSERT INTO X(a,b,c) VALUES(A,B,C); SELECT @@IDENTITY")
Proposed solution:
lock(MyDataLayer)
ID = MyDataLayer.Execute("SELECT MAX(id)+1 FROM X")
if(ID==null) ID=1
MyDataLayer.Execute("INSERT INTO X(id,a,b,c) VALUES(ID,A,B,C)")
unlock(MyDataLayer)
(This is of course pseudocode, for SQL Server I'd need SET IDENTITY_INSERT.)
Do you think the preceding solution is equivalent to the original?
Do you know something better?
Equivalent should mean here, not necessarily generating the same ID's,
but maintaining functionality and consistence all over the database.
View 9 Replies
View Related
Apr 7, 2004
is there a way to get around not using USE in a PROCEDURE?
I need to because I have a main site that inserts information into other DB's that i use for various subdomains. But without being able to use USE i cant select which database is needed.
thx in advance
View 2 Replies
View Related
May 14, 2001
Hi!
I wrote a query in MS-ACCESS using IIF. Is there any way to convert it to SQL Server Query to do the same job as it do in MS-ACCEESS
e.g. Here is manipulation with one column that I did in MS-ACCESS
IIf(InStr(1,Destinations.[Destination Name],"-",1)-1<0,Destinations.[Destination Name],Left(Destinations.[Destination Name],InStr(1,Destinations.[Destination Name],"-",1)-1)) AS COUNTRY,
View 1 Replies
View Related
Jan 26, 2004
My company develops software that is distributed to thousands of customers. We chose MSDE as the database engine. Over the past 4 months, we have spent countless hours with customers, Microsoft, Installshield and web searches trying to resolve issues with installing MSDE. The issues seem to vary by customer and most take a great deal of support time. We understood MSDE to be a product that requires little support but in hindsight, it appears that it requires a great deal of knowledge just to get installed. We make small steps but no leaps forward.
It has come time to evaluate other products. If there is a magic bullet, I would love to hear about it. In its absence, does anyone have success to share with other products?
Phil
View 13 Replies
View Related
May 2, 2008
Hi,
Just curious, is there any alternative to SQLXMLBULKLOAD for shredding and loading very large (800 megs) XML files ? Due to the nature of the XML data sent to me (which I have no control over)I am having great difficulty loading data into tables. More specifically, I can load parent data but not the child data beneath it despite using sql:relationships.
Thanks,
Jeff
View 2 Replies
View Related
May 21, 2008
I have a situation where my SQL works everywhere else but my COBOL compiler complains wherever I use PARTITION BY. I can't find a workaround for that problem so I would like to remove all the PARTITION BYs. I'm not confident that I can do this accurately and would like some help getting started.
Here is my simplest example:
SELECT FESOR.REGION, FESOR.TYPE,
COUNT(*) OVER (PARTITION BY FESOR.REGION, FESOR.TYPE)
FROM FESOR, FR
where FESOR.phase = 'Ref'
and FESOR.assign is null
and FESOR.comp_date is null
and FESOR.region = FR.REGION
and FESOR.type = FR.TYPE
and FR.REP_ROW='A'
GROUP BY FESOR.REGION, FESOR.TYPE
What I'm looking for is a modified version of the SQL above which returns the same result set without using PARTITION BY.
Thanks in advance for your assistance.
View 20 Replies
View Related
Feb 14, 2007
Cameron writes "Thanks for taking a look @ my question....
Basically, is there an alternative to indexing that maintains the fast searching capability (or possibly faster)?
We maintain over 500 databases on a single SQL server and currently (the way I am told) the server is limited to indexing 256 databases, so we have to basically create a new database with ALL the searchable data and use it for searches. While this works, it seems like there should be an alternate method. Any suggestions?
Thank you for your time!"
View 3 Replies
View Related
Mar 1, 2007
are there any alternatives to linking my db to webserver other then ODBC such as direct dsn connection
View 4 Replies
View Related
Sep 24, 2007
i need some alternative to sqlvarient..
View 2 Replies
View Related
Oct 24, 2007
Hey guys,
What would be a better / more elegant solution to something like
IF(@areaCode = '111' OR @areaCode = '222' 0R @areaCode = '333')
BEGIN
//do something
END
that uses less OR's in an IF statement?
View 6 Replies
View Related
Feb 1, 2008
Hi all
I work as a production dba and our development team are trying to push a project which involves using triggers. The aim is to transfer information between to databases (on two differents servers) because currently users have to type in the same info into the two different systems.
The triggers will be defined on a couple of tables, checking for inserts, updates, deletes, and then insert this into staging tables within teh same database. However the trigger does more complex processing than just inserting the same records from the production table into the staging table. Because the schema between the source database and destination database is different, the trigger needs to do some manipulation before it updates the staging tables. It basically does massive selects from a number of different tables to get the desired column list & then puts that into the staging tables.
We have basically asked them to reimplement this solution using other methods (such as timestamping the necessary tables and then putting the trigger login into a stored proc and scheduling it to run through a job).
However, we've found out the triggers make use of the 'deleted' and 'inserted' special trigger tables to compare new data to old data - i.e. not all inserts/updates/deletes need to be pushed to the staging tables - it depends on certain criteria based on this comparison of old and new data.....that throws a spanner in the works. What alternatives could provide this functionality, without just making the whole process a a headache to maintain - which is why we recommended not using triggers in the first place!!
Sorry for the long post - needed to explain the issue properly. Hopefully some of you will be able to provide some feedback - teh sooner the better as I have a meeting with the developers later today and would like to offer some alternatives.
Thanks!
Div
View 2 Replies
View Related
Mar 17, 2006
Hi All,I want to pass XML and the data in the XML should be stored in thetables of the database. However, I do not want to use the OpenXMLstatement. Please let me know.Regards, Shilpa
View 2 Replies
View Related
