Help on cursor use - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help on cursor use

Author

Topic

dave-mad
Starting Member

6 Posts

Posted - 2004-11-04 : 11:40:43

I know a bit about cursors and i'd like to know if there is the possibility to perform the fetch into a variable of a single field of the cursor. I know that msdn tells that there is no way to do this with cursor, so in a stored procedure i have to select a "recordset" with multiple fields, but i don't know how many... so i'd like to use an ADO-Like interface or something similar, but into a stored procedure...

Anyone can help me??

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-04 : 12:16:17

A man walks into a bar and orders a glass of milk...

Brett

8-)

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-11-04 : 12:53:04

You should post what your trying to do with a cursor. I bet someone on this forum can find a way that you will not have to use a cursor.

Dustin Michaels

dave-mad
Starting Member

6 Posts

Posted - 2004-11-05 : 06:32:36

Ok, now i'll tell you what i'm trying:

I have a table in a linked server db (SrcTable). I have to do some operations on another table (DestTable) using the SrcTable as the source of data.
In the SrcTable there is a field (Action) that specifies witch is the action (add this record, delete this record, update this record) and an ID field linked to my DestTable. In another table (CfgTable) i have the configuration that tells me the links between fields in the SrcTable and fields in DestTable. CfgTable contains also the name of the ActionField and the name of ID Field in SrcTable.

SrcTable has multiple records, and not all of them are "actions", but only fields with a valid value in the action field.

Now, i found a way with cursors, but is slow and long to program, so, i hope some of you could tell me another way.

The problem in simple words is that in a cursor i have to know in advance the structure of the table, to put recorset into variables. In this case the structure of the table is written in another table, and so...

Tell me something please and if the problem is now clear or not.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-05 : 06:45:32

Well, you need to update/delete/insert data in DestTable based on the data in SrcTable. The column mapping between these tables is defined in CfgTable . Fine!!!.
Now what do you mean by this...

quote:
In this case the structure of the table is written in another table, and so...

Will this not work....?


INSERT INTO DestTable (col1, col2, col3.....)
      SELECT corresponding_col1, corresponding_col2, corresponding_col3 .......
        FROM SrcTable 
       WHERE SrcTable.Action = 'ADD'

UPDATE A
SET COL1 = B.corresponding_col1
   ,COL2 = B.corresponding_col2 .......
FROM DestTable A, SrcTable B
WHERE B.Action = 'UPDATE'
  AND A.ID = B.ID

DELETE A
FROM DestTable A, SrcTable B
WHERE B.Action = 'DELETE'
  AND A.ID = B.ID

you hard-code the "corresponding_col" by referencing the CfgTable manually.

won't this work for your requirement

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

dave-mad
Starting Member

6 Posts

Posted - 2004-11-05 : 11:13:42

Thanks for the hint!!! For the update and delete i'll use this code, but for insert i have to follow another way, because i have to insert records also in other tables, not only in the destination table...

dave-mad
Starting Member

6 Posts

Posted - 2004-11-12 : 10:22:43

Ok... now i've got other problems: the execution of the query fails with the message 7405, that reguards ANSI_NULLS and ANSI_WARNINGS. One of the 2 tables is located in a linked server, and i think that this is the problem. Anyone knows?

Server: Msg 7405, Level 16, State 1, Line 0
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources