Cursors And Dynamic SQL
Jul 23, 2005
On this site:
http://www.sommarskog.se/dynamic_sql.html
I have found an example how to use cursor with dynamic SQL:
DECLARE @my_cur CURSOR
EXEC sp_executesql
N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN
@my_cur',
N'@my_cur cursor OUTPUT', @my_cur OUTPUT
FETCH NEXT FROM @my_cur
But when I tried to do this:
IF (@Naziv <> '')
SET @sql_where = @sql_where + N' AND Naziv LIKE ' + @Naziv
IF (@Funk <> '')
SET @sql_where = @sql_where + N' AND Funkcija LIKE ' + @Funk
IF (@Mj <> '')
SET @sql_where = @sql_where + N' AND NazivMjesta LIKE ' + @Mj
IF (@Drz <> '')
SET @sql_where = @sql_where + N' AND (drzava1 LIKE ' + @Drz +
' OR drzava2 like ' + @Drz + ' OR drzava3 LIKE ' + @Drz + ')'
DECLARE @CursSearch CURSOR
SET @sql = N'SET @CursSearch = CURSOR FOR
SELECT CvorID, NadCvorID,
IzvorisniCvorID, Naziv, TipCvora,
NasljednaLinija, Funkcija, NazivMjesta,
drzava1, drzava2, drzava3
FROM dbo.Pretrazivanje
WHERE NasljednaLinija LIKE @NasljednaLinija'
+ @sql_where + N'; OPEN @CursSearch'
EXEC sp_executesql @sql, N'@CursSearch CURSOR OUTPUT',
@CursSearch OUTPUT
....by fetching cursor i got this message:
The variable '@CursSearch' does not currently have a cursor allocated to it.
Can anybody tell me what i did wrong?
View 5 Replies
ADVERTISEMENT
Jun 24, 2002
Hi!
I am trying to build a Cursor using an string I am passing into a Stored Procedure. This string I am passing contain my Filters parameter of the query.
Example: Set sSQL = ' And LName like ' + '''S% '''
I am passing sSQL into a stored procedure.
I want to be able to use that when building the cursor.
Something like;
DECLARE SampleCrsr CURSOR FOR
SELECT empl, lname
FROM stm_employee
Where Empl <> NULL + sSQL <-- This does not work.
Or something like this.
The idea is to build something like a Dynamic Cursor. I have done this in Sybase and I think ORACLE. U need help for SQL SEVER.
Do you have any idea how to go around or make it happen???
View 1 Replies
View Related
Sep 22, 2005
Hi!
I'm moving from firebird to MS SQL and have many problems. In firebird, I did many things with FOR...DO..SUSPEND loops. That loop enabled me to scan through the result set, do many calculations and create new result set (defined as variabled in RETURNS part of procedure and actually returned with SUSPEND).
In MS SQL, I can accomplish same thing with cursors. I did read cursors are slow, but I really can't imagine how to perform all this calculations without them (writing software for production planning).
One thing I couldn't do. Declaring cursors with dynamic SQL (in firebird that was for...execute statement(statement) do.
Later I found out about sp_cursoropen and related procedures, but that get's me with "Only functions and extended stored procedures can be executed from within a function."
Here is the code of function with latest modifications to sp_ procedures. Note: This is REALY out-of-context procedures since it is part of 18 procedures which together creates rough production plan.
How to make this function work?
CREATE function [dbo].[plan_narudzbe] (
@koja_narudzba int,
@tip int)
returns @ReturnTable table (
ID_NARUDZBA_STAV int,
ID_ARTIKL VARCHAR (20),
KOLICINA float,
ID_RADNI_PLAN int,
TERMIN_NARUDZBE DATETIME)
as
begin
declare
@U_PROIZVODNJI float, @RASPISANI_PLAN float, @D float, @STR VARCHAR(1000),
@NARUDZBA_PLAN_FINI_PLAN int, @STATUS_NARUDZBE SMALLINT,
@ID_NARUDZBA_STAV int, @ID_ARTIKL VARCHAR (20), @KOLICINA float,
@ID_RADNI_PLAN int, @TERMIN_NARUDZBE DATETIME
SELECT
@NARUDZBA_PLAN_FINI_PLAN = VRIJEDNOST
FROM PARAMETRI
WHERE NAZIV='NARUDZBA_PLAN_FINI_PLAN'
set @NARUDZBA_PLAN_FINI_PLAN=COALESCE(@NARUDZBA_PLAN_FINI_PLAN,0);
if ((@TIP=1) AND (@KOJA_NARUDZBA IS NULL)) return;
set @STR= '
SELECT Q.ID,Q.ID_ARTIKL,
COALESCE(Q.POTVRDA_KOLICINA,Q.KOLICINA,0) AS KOLICINA,
COALESCE(Q.POTVRDA_TERMIN_ISPORUKE,Q.TERMIN_ISPORUKE) AS TERMIN,
N.STATUS ,
SUM(RN.KOLICINA_NALOGA) AS U_PROIZVODNJI
FROM NARUDZBA_STAV Q
LEFT JOIN RADNI_NALOG RN ON (RN.ID_NARUDZBA_STAV=Q.ID)
LEFT JOIN NARUDZBA N ON (Q.ID_NARUDZBA=N.ID)
WHERE Q.STATUS NOT IN (2,5,8,9) ';
if (@KOJA_NARUDZBA IS NOT NULL) set @STR=@STR+' AND Q.ID='+cast(@KOJA_NARUDZBA as varchar)+' ';
set @STR=@STR+' GROUP BY Q.ID,Q.ID_ARTIKL, COALESCE(Q.POTVRDA_KOLICINA,Q.KOLICINA,0),
COALESCE(Q.POTVRDA_TERMIN_ISPORUKE,Q.TERMIN_ISPORUKE), N.STATUS ';
-- Create a dynamc read-only cursor
DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, @STR, 2, 8193
-- Name the cursor
EXEC sp_cursoroption @cursor, 2, 'narudzba_stav'
DECLARE @narudzba_stav CURSOR
EXEC sp_describe_cursor @narudzba_stav out, N'global', 'narudzba_stav'
while (1=1) begin
fetch next
from narudzba_stav
INTO
@ID_NARUDZBA_STAV, @ID_ARTIKL, @KOLICINA, @TERMIN_NARUDZBE, @STATUS_NARUDZBE, @U_PROIZVODNJI
if (@@fetch_status<>0) break;
set @KOLICINA=@KOLICINA-COALESCE(@U_PROIZVODNJI,0);
if (@STATUS_NARUDZBE=12) BEGIN
if (@NARUDZBA_PLAN_FINI_PLAN=1) BEGIN
set @RASPISANI_PLAN=NULL;
SELECT
@RASPISANI_PLAN = SUM(KOLICINA)
FROM NARUDZBA_STAV_PLAN
WHERE ID_NARUDZBA_STAV_PLAN=@ID_NARUDZBA_STAV
set @KOLICINA=@KOLICINA-COALESCE(@RASPISANI_PLAN,0);
END
ELSE set @KOLICINA=0;
END
if (@KOLICINA>0) BEGIN
set @ID_RADNI_PLAN=NULL;
SELECT TOP 1 @ID_RADNI_PLAN = ID
FROM RADNI_PLAN
WHERE ID_ARTIKL=@ID_ARTIKL
ORDER BY AKTIVAN_PLAN DESC,ID DESC
insert @ReturnTable values (
@ID_NARUDZBA_STAV, @ID_ARTIKL, @KOLICINA, @ID_RADNI_PLAN, @TERMIN_NARUDZBE)
END
END
-- Close the cursor
EXEC sp_cursorclose @cursor
return
end
View 20 Replies
View Related
Mar 7, 2008
Hi All
I wrote the following script. Its gets list of tables left joint to compare data between 2 table and insert the difference.
My problem is its taking for ever to Run.
What could be the problem.
CREATE PROCEDURE [dbo].[Update_D_Tables_InBlake_DBN12]
AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @Sql VARCHAR(MAX)
DECLARE @Column VARCHAR(1000)
DECLARE @ID VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name in ('D_Address',
'D_AddressBankaccount',
'D_AddressCustomer',
'D_AddressEmployer',
'D_Application',
'D_ApplicationCustomerPri',
'D_ApplicationCustomerSec',
'D_ApplicationDetail',
'D_ApplicationDetailStatusHistory',
'D_ApplicationDetailUpdateHistory',
'D_ApplicationHistory',
'D_ApplicationQuestions',
'D_ApplicationSendReports',
'D_ApplicationStatusHistory',
'D_BankAccount',
'D_BlackHorseBranch',
'D_BlackHorseResponses',
'D_BrokerReferralInfo',
'D_BrokerWorkflow',
'D_Calls',
'D_CallTable',
'D_CCJs',
'D_CitiResponses',
'D_Contact',
'D_ContactCustomer',
'D_ContactEmployer',
'D_Customer',
'D_DataExtracts',
'D_DNCList',
'D_DuplicateApps',
'D_DuplicateMatches',
'D_EDLResponses',
'D_Employer',
'D_ExistingCredit',
'D_FinancialCustomer',
'D_FinancialStatements',
'D_HomeownerDetails',
'D_IncompleteResults',
'D_IncompleteRules',
'D_LenderOtherDetail',
'D_LenderOwnerRoles',
'D_LenderPlans',
'D_LenderResponse',
'D_LenderSender_Report',
'D_LenderStatusRole',
'D_LenderStatusRoleAssign',
'D_LoanDetails',
'D_Mortgage',
'D_Occupations',
'D_OccupationsCustomer',
'D_PhoneLogin',
'D_PhoneLogin_Skills',
'D_ProvidentResponses',
'D_ReportAllianceLeicHistory',
'D_SkillSet',
'D_Tenancy',
'D_Users',
'D_WelcomeResponses'
)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Line VARCHAR(1000)
SET @Line = ''
SET @ID = ''
SELECT top 1 @ID = @ID + QUOTENAME(c.Column_Name)
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName
SELECT @Line = @Line + 'b.'+ QUOTENAME(c.Column_Name) + ' , '
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName
SELECT @Column = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @Sql = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @Sql = 'INSERT'+ ' '
SELECT @Sql = @Sql + 'INTO Staging.dbo.'+ @TableName +' ' + 'SELECT' + ' ' + @Column
SELECT @Sql = @Sql + ' '+ 'FROM MyAPOLLO_101.dbo.'
SELECT @Sql = @Sql + @TableName + ' ' +'B' + ' '
SELECT @Sql = @Sql + 'LEFT JOIN' + ' '
SELECT @Sql = @Sql + 'Staging.dbo.'
SELECT @Sql = @Sql + @TableName + ' ' +'C'+ ' '
SELECT @Sql = @Sql + 'ON' + ' '
SELECT @Sql = @Sql + 'B.'+ @ID + ' '+ '=' + ' ' + 'C.'+ @ID + ' '
SELECT @Sql = @Sql + 'WHERE ' + ' ' + 'C.'+ @ID + ' ' + 'IS NULL' --Edited 04/03
EXEC (@Sql)
PRINT @TableName
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END
View 3 Replies
View Related
Oct 3, 2006
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 3 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
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 3 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Jul 16, 2006
Hello,
Can anyone direct me a good article that is about why we should try avoiding using cursors and what are the alternatives?
View 1 Replies
View Related
Feb 16, 2008
can we call cursors from asp.net as we can call stored procedures from asp.net.
View 1 Replies
View Related
Jan 7, 2005
Hi,
I'm writting a stored procedure to insert new rows into a table, but I need to loop through an exsiting table and create a new record for every row in the old table. So I think what I'm needing to use is a cursor, but I've never worked with cursors before, and I just want to check that I have the right idea on what they are used for and if what I have so far looks ok.
This what I have so far, the StoreFees table only has 5 rows:
DECLARE @StoreFee as smallmoney
DECLARE @StoreLineID as int
DECLARE MyStoreFees CURSOR
FORWARD_ONLY
FOR
SELECT LineId, StoreFee FROM StoreFees ORDER BY StoreFee
OPEN MyStoreFees
FETCH NEXT FROM MyStoreFees
INTO @StoreLineId, @StoreFee
--Do my Inserts into other tables
INSERT INTO OtherTable (...,...,.., @StoreFee)
...
...
-- Done working with that row
CLOSE MyStoreFees
View 5 Replies
View Related
Feb 28, 2005
I am trying to pull the data via fetching rows into a variable.
Begin
Fetch Next Into @temp...
Select @MainVariable = @MainVariable + @temp < @temp doesn't refresh with next fetch)
Print @temp < this works fine and returns the value for each fetch
End
I can print @temp and the data returns fine, but when I try to cancantenate with the above select, it doesn't refresh @temp wit the column in the table.
Any ideas?
DotNetNow
View 5 Replies
View Related
Jun 19, 2001
I have two procedures. One works well, and the other has a small glitch I cannot figure out. I have placed >>>> at the place where the problem is occuring.
The first procedure, which is working great, is applying a stored procedure to many servers (remote procedure call), but is also polling the local server via a linked server connection. This way, all servers are polled equally.
The second procedure is actually using a SELECT statment to query a system table. This procedure works well on all servers except the local one. I get this error message:
Server: Msg 3910, Level 16, State 1, Line 1
Transaction context in use by another session.
[OLE/DB provider returned message: Unspecified error]
There seems to be a connection issue. Can someone help me work around this?
Thank you,
Neal
FIRST PROCEDURE (this one works perfectly):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbownervarchar(50),
dbid smallint,
createdate datetime,
statusvarchar(75) )
declare@servernamevarchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>INSERT INTO ##dbtemp
>>>exec (@servername + '.master..sp_helpdb')
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
exec spal_db_files_update
SECOND PROCEDURE (see >>>> to note problem area):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbownervarchar(50),
dbid smallint,
createdate datetime,
statusvarchar(75) )
declare@servernamevarchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>>INSERT INTO ##dbtemp
>>>>exec (@servername + '.master..sp_helpdb')
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
View 1 Replies
View Related
Nov 28, 2000
I am receiving this error when running my cursor:
Error Messages...
Server: Msg 16933, Level 16, State 1, Line 0
The cursor does not include the table being modified.
The statement has been terminated.
Server: Msg 16933, Level 16, State 1, Line 5 (this message repeats)...
The cursor does not include the table being modified.
The statement has been terminated.
query:
declare cursor_test CURSOR for
select emp_ssn, effective_date1 from temp_employee_benefit_load
open cursor_test
declare @ssn char(9), @process_date char(8)
fetch next from cursor_test into @ssn, @process_date
while (@@fetch_status=0)
update test_cursor
set ssn = @ssn, process_date = @process_date
where current of cursor_test
fetch next from cursor_test into @ssn, @process_date
close cursor_test
deallocate cursor_test
Any help is appreciated:
Thanks,
View 3 Replies
View Related
Sep 7, 2000
I have stored procedure in that I am using a cursor to fetch the row by row.
cursor is fetching 75000 records so that the procedure is taking long time.
Is there any way to replace the cursor to fetch the records row by row.
He needed.
Ranch
View 1 Replies
View Related
Apr 14, 2000
Please help resolve a problem (and debate!).
We have an app using VB with Access as the front end to SQL. SQL statments were built and sent
directly to the server obtaining set-oriented results. Response was fair.
We have a new app developed by an "expert" using VB & Interdev that was to be ported for use on
the internet. The app was designed using the same logic to build the SQL statements, but it is also
using cursors to retrieve all data. RESPONSE IS TERRIBLE!
The designer says that to access SQL over the Web, cursor use is a MUST!
True? Not True?
If true, and I am stuck with an app using cursors, any basic suggestions on where to look to improve
response time?
Thanks for any input.
FJ
View 1 Replies
View Related
Apr 23, 1999
Hi,
I am using SQL Server 6.5 and I have a VB routine that updates a field in a tble so it can be used in a primary key. This is run after input of data using bcp. I have noticed on several posts re: using cursors to move through a recordset and was wondering if I could use this functionality to replace my VB routine. I checked the archives and found several messages but I am not sure where to start. My VB routine is:
I first would query the data to return all rows where the value of R08SegmentValue= Null. Then I move through the resultset changing the value of the field R08Segments.
***********************Start of VB Code****************
'Set initial value for sTReportHeaderIdx
With rs1
sTReportHeaderIdx = !ReportIdx & !HeaderIdx
sPReportHeaderIdx = sTReportHeaderIdx
End With
bCount = 1
'loop through rs and when streportheaderidx changes reset bCount to 1
'otherwise increment bcount by 1 and write to field R08SegmentValue
Do While rs1.EOF = False And blnContinue = True
DoEvents
Do While sPReportHeaderIdx = sTReportHeaderIdx
With rs1
.Edit
!R08SegmentValue = bCount
.Update
bCount = bCount + 1
sPReportHeaderIdx = sTReportHeaderIdx
.MoveNext
If Not rs1.EOF Then sTReportHeaderIdx = !ReportIdx & !HeaderIdx
End With
Loop
sPReportHeaderIdx = sTReportHeaderIdx
bCount = 1
Loop
In the end my data should look like
ReportIdx HeaderIdx R08SegmentValue
1 1 1
1 1 2
2 1 1
2 1 2
2 1 3
3 1 1
3 1 2
3 1 3
1 2 1
1 2 2
1 2 3
etc..
Can this be done via an sp? How would I go about writing something like this?
Thanks in advance...any assistance you could provide would be appreciated!
Kevin
View 1 Replies
View Related
Aug 6, 2002
I hope someone can help me with this, as BOL didn't get me the answer I am looking for.
I want to write an update step that will transform the following:
1 abc
1 def
2 lmn
3 rst
4 tuv
4 xyz
4 jkl
5 pqr
into this:
1 abc,def
2 lmn
3 rst
4 tuv,xyz,jkl
5 pqr
In other words, I want to concatenate multiple instances into 1 record per row, but I am not familiar with working with cursors or loops.
Thanks in advance!
bv
View 1 Replies
View Related
Nov 16, 1998
According go textbooks and T-SQL developers experience - using cursors is not recommended, more over they say: avoid cursors where it's possible.
Could someone please recommend any other way to go through the recordset(resultset) forward and backward to perform some search or calculations, if there are a specific requirement for not using front-end tools such as VB or MS Access(please don't ask why), other words - all the work must be performed in T-SQL stored procedure.
Thanks
View 3 Replies
View Related
Feb 24, 2008
Hi
I defined a cursor and executed it...but now i exactly forgot what select statement i had run in the cursor..(forgot the columns that i am extracting )
how do I view the contents of the cursor ?
View 1 Replies
View Related
Aug 25, 2004
I have a stored proc that merges records from an undeduped table to a deduped table. It is running really slowly. Merging 70 million records against a deduped 70 million is taking 115 hours and counting on decent hardware. Very slow.
I suspect there is significant room for optimization here. Some of my ideas:
- Write/update back to the cursor rather than executing separate UPDATE statements.
- Try a dynamic cursor instead of a READ ONLY cursor.
- Dump new elements to a separate table or a file and then do a single large INSERT.
Anyone else think any of these ideas will work? Can anyone think of something better?
BTW, I've tried to replaced the procedural cursor code with set based UPDATES/INSERTS but the short version of the story is that that route just didn't pan out. I know that is very common optimization advice.
I've made minor simplifications to the code:
- Took out code to handle last line
- Took out progress logging code
- Removed some DECLARE statements. These are needed to run but it should be obvious what they were supposed to be.
View 10 Replies
View Related
Aug 27, 2004
Trying to understand cursors a little better, found this in one of the dbs I inherited. Just trying ot figure out why they put it there cause no one else knows anything about it.
DECLARE [TM #] CURSOR
FOR SELECT * FROM [2004 TERMS];
View 10 Replies
View Related
Jan 14, 2005
nevermind forget I asked thanks anyways
View 11 Replies
View Related
Feb 10, 2005
These guys I work with have some sql scripts they run over night and they bog down the server and the machine will be gummed up in the morning etc..
Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.
Using a cursors to pull records and update them.
vs
Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.
Be nice. I have to work with these guys.
View 9 Replies
View Related
Jan 24, 2006
In what situations would you use a cursor over a t-SQL update/select statement
View 3 Replies
View Related
Jun 26, 2007
Hey guys
I have heard cursors are not the way to go. But I am wondering if/how to get out of a situation that I am using a cursor in...in order to make my stored proc run more effieciently.
I am quite novice in my abilities and I am completely stumped on how to get around using them.
As far as INSERTs go, I think I can work around that, but how would I write UPDATE statements for all lines of a table to say pull a key from another table to reference them together?
I usually make my SELECT statement in the cursor, then update against the criteria from the SELECT statement. Now this is quite a slow process when I am updating 100K records.
Any help or pointers or a link to a good tutorial would be woderful.
Thanks
tibor
View 6 Replies
View Related
Jan 23, 2004
declare BadRecords cursor dynamic for select lngZipCodeID
from ZipCodes where lngZipCode=@ZipCode and lngZipCodeID<>@NewZipID
/* this is the syntax in Sybase
open BadRecords;
BadRecordsLoop: loop
fetch next BadRecords into BadID;
if sqlstate<>0 then
leave BadRecordsLoop
end if
*/
I have to convert it to sql , I m just checking whether my syntax is correct or not
open BadRecords
while @@fetch_status = 0
begin
set @BadId = fetch next BadRecords
if @@Error = 0
end
I have doubt in my syntax. Can you help me out in this?
Thx
View 4 Replies
View Related
Feb 11, 2004
I have a requirement to check a value which MUST be unique forever even if it is removed and readded.I have created a seperate table to maintain this value.
Without using a cursor how would I be able to append a duplicate base value (i.e. smith.j@here.now) with the next sequential value (i.e. smith.j02@here.now)
Any takers?
Oh ya, These values are not manually entered but populated through a DTS script. The existing values are repopulated from historic tables and new entries are added automatically. Initially the values would be populated without a number but a number needs to be generated on duplicates.
View 8 Replies
View Related
Apr 8, 2008
Could not complete cursor operation because the table schema changed after the cursor was declared.
View 5 Replies
View Related
Sep 28, 2006
I have a Stored Procedure that show RentArrears for a tenant only
eg Execute RentArrears '88' where 88 is the tenantID
Now I want to execute it for other tenants, I want think of using Cursors to loop thru tenent table , but everybody is frowning at it, what other method can I use to loop thru tenant so as to use my stored procedure
Thanks
If it is that easy, everybody will be doing it
View 5 Replies
View Related
Jul 24, 2007
Can someone tell me what is wrong with this code?
I just want to get the account_number field from the accounts table and put it in the results table. Let me clarify, there are over 500 rows in the accounts table and I want to loop through those while setting the accounts.account_number = results.account_number.
declare @account nvarchar
DECLARE Accounts_Cursor CURSOR FOR
SELECT account_number FROM accounts
OPEN accounts_Cursor
FETCH NEXT FROM accounts_Cursor into @account
WHILE @@FETCH_STATUS = 0
BEGIN
update results
set account_number = @account
FETCH NEXT FROM accounts_Cursor into @account
END
CLOSE accounts_Cursor
DEALLOCATE accounts_Cursor
View 10 Replies
View Related
Feb 3, 2008
declare @sal char(50)
DECLARE Employee_Cursor1 CURSOR static for
SELECT salary
FROM test2
OPEN Employee_Cursor1
FETCH FROM Employee_Cursor1 into @sal
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor1 into @sal
PRINT 'this is NOT the desired output which i wanted from so long :'+@sal
END
CLOSE Employee_Cursor1
DEALLOCATE Employee_Cursor1
Rahul
View 5 Replies
View Related
Feb 3, 2008
declare @sal char(30),
@name char(30),
@sal2 char(30),
@i int
set @i=0
DECLARE Employee_Cursor4 CURSOR for
SELECT salary
FROM test2
OPEN Employee_Cursor4
fETCH FROM Employee_Cursor4 into @sal
while @@fetch_status=0
BEGIN
declare emp10 cursor for
select salary, fname from test2
open emp10
fetch from emp10 into @sal2, @name
PRINT 'this is NOT the desired output which i wanted from so long :'+@sal
while @@fetch_status=0 and @sal2=@sal
begin
print 'this is the name '+@name
fetch next from emp10 into @sal2,@name
end
close emp10
deallocate emp10
fetch next from employee_cursor4 into @sal
print @i
set @i=@i+1
end
close employee_cursor4
deallocate employee_cursor4
i HAVE CREATED A TEST TABLE WITH TWO COLUMNS SALARY AND FNAME
TABLE STRUCTURE IS LIKE THIS:
salary fname
10 r
20 m
30 n
40 p
when i run the above query it produces this result
this is NOT the desired output which i wanted from so long :10
this is the name r
0
this is NOT the desired output which i wanted from so long :20
1
this is NOT the desired output which i wanted from so long :30
2
this is NOT the desired output which i wanted from so long :40
3
i want it to say the name after every row:
this is NOT the desired output which i wanted from so long :10
this is the name r
0
this is NOT the desired output which i wanted from so
long :20
this is the name n 1
this is NOT the desired output which i wanted from so long :30
this is the name m
2
this is NOT the desired output which i wanted from so long :40
this is the name p
3
if any other way to do it please helpppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
Rahul
View 6 Replies
View Related
