Cursor to Array

Can you Assign a cursor to an array

For example:

Declare AuditInfo cursor
for
Select top 2 TRAKRID,MeterID,DelCount,ContactID,Type,Active,Cre atedDateTime,ModifiedBy,DateModified
from TRAKRAudit
where TRAKRID = @.ID
order by createdDatetime desc
--Print @.ID
open AuditInfo

-- Get all the newest values
fetch next From AuditInfo into Array()

Thanks
LJdeclare @.tbl table (...)
insert @.tbl Select top 2 TRAKRID,MeterID,DelCount,ContactID,Type,Active,Cre
atedDateTime,ModifiedBy,DateModified
from TRAKRAudit
where TRAKRID = @.ID
order by createdDatetime desc|||Ok this leads me to another ?

Is there any way to dynamically go through the columns in this Temp table.

The 2 records represent Old and New Values from Table1 and Table1_audit.

I am building a string:

Select @.NewStr = Coalesce(convert(varchar(25),@.MeterIDNew),'null')
Select @.NewStr = ('New MeterID = ' + @.NewStr)
Select @.OldStr = Coalesce(convert(varchar(25),@.MeterIDOld),'null')
Select @.OldStr = ('Old Meter ID = ' + @.OldStr)
Select @.StrOutput = @.StrOutput + @.NewStr + @.OldStr + Char(10)

Select @.NewStr = Coalesce(convert(varchar(25),@.MV90IDNew),'null')
Select @.NewStr = ('New MV90 ID = ' + @.NewStr)
Select @.OldStr = Coalesce(convert(varchar(25),@.MV90IDOld),'null')
Select @.OldStr = ('Old MV90 ID = ' + @.OldStr)
Select @.StrOutput = @.StrOutput + @.NewStr + @.OldStr + Char(10)
.
.
.

To put in the body of an e-mail.

Sample
-----------------------
New SCADA ID = 58 Old SCADA ID = 58
New MeterID = null Old Meter ID = null
New MV90 ID = Entergy Old Meter ID = Entergy
New Name = TestNamejon Old Name = TestNamejon
------------------------

I need to do this for 10 tables. I am looking for a way to do this programitaclly so I do not have to create a stored procedure for each of these Tables. And if the table def changes I do not have to change the SP.

Thanks for the speedy reply
LJ|||try gettin the names of the table from sysobjects (usign a cursor) and make generic code for ur process which will use the tablenames from the names in sysobject.

so u can do sumthin for as many tables as u want. with just one sp

Cursor Help

Hi,

Can somebody help me with this code. I have a table of 1000 records wiith unque key Contactid. This table has some duplicates on First, Name and ZipCode. What I am trying to do is open a cursor with the name ContactsDedup and if it is the first record insert it into another table just contactid and personid(It is just some value so i am giving it from my row counter @.counter. When my cursor goes to next record I am supposed to check if it is the same record as the first an dif it is look for ts personid in the other table and give the same personid to that contact also, if not then insert the that contactid with new personid(which I can use @.counter).

After writing this code I am getting as syntax error near else.

Can somebody help me with this. I really willl appreciate I

Create Procedure sp_ContactsDedupProcedure as
Declare @.Cid int
Declare @.FName varchar(100)
Declare @.LName varchar(100)
Declare @.ALine varchar(100)
Declare @.CC varchar(50)
Declare @.St varchar(50)
Declare @.Zip varchar(50)
Declare @.WA varchar(50)
Declare @.WP varchar(50)
Declare @.HA varchar(50)
Declare @.HP varchar(50)
Declare @.Email varchar(50)
Declare @.Login varchar(50)
Declare @.PID int
Declare @.Counter int
Declare @.RowCounter int
Declare @.FID int
Declare @.PPID int
Set @.counter = 0
set @.rowcounter = 0

Declare ContactsDedup Cursor
Scroll Dynamic For
Select Contactid, FirstName, LastName, AddressLine1, City, StateOrProvince, PostalCode, WorkArea, Workphone,
HomeArea, HomePhone, EmailAddress, LoginID, Personid from ContactsSample

Declare @.Contactid Int
Declare @.FirstName varchar(100)
Declare @.LastName varchar(100)
Declare @.AddressLine1 varchar(100)
Declare @.City varchar(50)
Declare @.Stateorprovince varchar(50)
Declare @.PostalCode varchar(50)
Declare @.WorkArea varchar(50)
Declare @.WorkPhone varchar(50)
Declare @.HomeArea varchar(50)
Declare @.HomePhone varchar(50)
Declare @.EmailAddress varchar(50)
Declare @.LoginID varchar(50)
Declare @.Personid varchar(50)

Declare @.Contactid1 Int
Declare @.FirstName1 varchar(100)
Declare @.LastName1 varchar(100)
Declare @.AddressLine11 varchar(100)
Declare @.City1 varchar(50)
Declare @.Stateorprovince1 varchar(50)
Declare @.PostalCode1 varchar(50)
Declare @.WorkArea1 varchar(50)
Declare @.WorkPhone1 varchar(50)
Declare @.HomeArea1 varchar(50)
Declare @.HomePhone1 varchar(50)
Declare @.EmailAddress1 varchar(50)
Declare @.LoginID1 varchar(50)
Declare @.Personid1 varchar(50)

Declare PersonContacts Cursor For
Select Contactid, Personid

Declare @.PContactId int
Declare @.PPersonID int

open ContactsDedup

Fetch Next from ContactsDedup into
@.Contactid, @.FirstName, @.LastName, @.AddressLine1, @.City, @.StateOrProvince, @.PostalCode, @.WorkArea, @.Workphone,
@.HomeArea, @.HomePhone, @.EmailAddress, @.LoginID, @.Personid

While @.@.Fetch_Status = 0
Begin

set @.Counter = @.counter + 1

If @.Counter = 1

Insert into PersonContactID(Personid, Contactid)
select @.counter, @.contactid

else

set @.counter = @.counter + 1
set @.Cid = @.contactid
set @.FName = @.FirstName
Set @.LName = @.LastName
Set @.ALine = @.AddressLine1
set @.CC = @.City
set @.St = @.Stateorprovince
set @.zip = @.PostalCode
set @.WA = @.WorkArea
set @.WP = @.WorkPhone
set @.HA = @.HomeArea
set @.HP = @.HomePhone
Set @.Email = @.EmailAddress
Set @.Login = @.loginid
set @.Pid = @.Personid

Declare ContactsDedupCheck Cursor For
Select Contactid, FirstName, LastName, AddressLine1, City, StateOrProvince, PostalCode, WorkArea, Workphone,
HomeArea, HomePhone, EmailAddress, LoginID, Personid from ContactsSample

open ContactsDedupCheck

Fetch Next from ContactsDedupCheck into @.Contactid1, @.FirstName1, @.LastName1, @.AddressLine11, @.City1, @.StateOrProvince1, @.PostalCode1, @.WorkArea, @.Workphone1,
@.HomeArea1, @.HomePhone1, @.EmailAddress1, @.LoginID1, @.Personid1

While @.rowcounter >= @.counter
Begin

set @.rowcounter = @.rowcounter + 1

If @.FName = @.FirstName

--If @.Lname = @.LastName

--If @.ALine = @.AddressLine1

Set @.FID = @.Contactid1

else


Fetch Next from ContactsDedupCheck into @.Contactid1, @.FirstName1, @.LastName1, @.AddressLine11, @.City1, @.StateOrProvince1, @.PostalCode1, @.WorkArea, @.Workphone1,
@.HomeArea1, @.HomePhone1, @.EmailAddress1, @.LoginID1, @.Personid1
End

Open PersonContacts
Fetch Next from ContactsPerson into @.Contactid , @.Personid
While @.@.Fetch_Status = 0
Begin
If @.FId = @.Contactid

set @.PPId = @.personid
Insert into PersonContacts(Contactid, Personid)
select @.CID, @.PPID
else
Insert into PersonContacts(Contactid, Personid)
select @.CID, @.counter
Fetch Next from ContactsPerson into @.Contactid , @.Personid

End


Fetch Next from ContactsDedup into
@.Contactid, @.FirstName, @.LastName, @.AddressLine1, @.City, @.StateOrProvince, @.PostalCode, @.WorkArea, @.Workphone,
@.HomeArea, @.HomePhone, @.EmailAddress, @.LoginID, @.Personid


End

Close ContactsDedup
Deallocate ContactsDedup

Close ContactsDedupCheck
Deallocate ContactsDedupCheck

Close PersonContacts
Deallocate PersonContacts

I will appreciate any help. This project d due today, please help as i am a novice to this.Check this part:

If @.FId = @.Contactid
-- begin -- I guess you have to add begin
set @.PPId = @.personid
Insert into PersonContacts(Contactid, Personid)
select @.CID, @.PPID
--end -- the same
else
Insert into PersonContacts(Contactid, Personid)
select @.CID, @.counter
Fetch Next from ContactsPerson into @.Contactid , @.Personid

End