-
cursor in procedure
Hi
I am trying to write one cursor which is been made from join of two tables.
and then i want to update one table by using where current of cursor.
Can i do that..?
or should i create the view by joining the two tables and then try to update it.
or should i create the temp table and put the records in that and then try to update that.
Please guide me in this.
Thanks,
Harish
====
CREATE PROCEDURE SP_P4
AS
declare c123 cursor for
select
COMPANY.COMPANY,
COMPANY.COMPANYNAME,
COMPANY.CUSTOMERNUMBER,
COMPANY.FEDTAXID,
COMPANY.DUNSNUMBER,
COMPANY.S1099,
COMPANY.DUNSSUFFIX,
COMPANYADDRESS.ADDRESSSEQ,
COMPANYADDRESS.COUNTRY,
COMPANYADDRESS.ADDRESSTYPE,
COMPANYADDRESS.ADDRESS1,
COMPANYADDRESS.ADDRESS2,
COMPANYADDRESS.ADDRESS3,
COMPANYADDRESS.ADDRESS4,
COMPANYADDRESS.CITY,
COMPANYADDRESS.STATE,
COMPANYADDRESS.ZIP
from COMPANY,COMPANYADDRESS
where company.company=COMPANYADDRESS.company
order by company.company
declare @company char
declare @COMPANYNAME char
declare @CUSTOMERNUMBER char
declare @FEDTAXID char
declare @DUNSNUMBER char
declare @S1099 char
declare @DUNSSUFFIX char
declare @ADDRESSSEQ int
declare @COUNTRY char
declare @ADDRESSTYPE char
declare @ADDRESS1 char
declare @ADDRESS2 char
declare @ADDRESS3 char
declare @ADDRESS4 char
declare @CITY char
declare @STATE char
declare @ZIP char
open c123
fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099,
@DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY,
@STATE,@ZIP
while @@fetch_status=0
begin
update companyaddress set ADDRESSSEQ=@ADDRESSSEQ where current of c123
update companyaddress set COUNTRY=@COUNTRY where current of c123
update companyaddress set ADDRESSTYPE=@ADDRESSTYPE where current of c123
update companyaddress set ADDRESS1=@ADDRESS1 where current of c123
update companyaddress set ADDRESS2=@ADDRESS2 where current of c123
update companyaddress set ADDRESS3=@ADDRESS3 where current of c123
update companyaddress set ADDRESS4=@ADDRESS4 where current of c123
update companyaddress set CITY=@CITY where current of c123
update companyaddress set STATE=@STATE where current of c123
update companyaddress set ZIP=@ZIP where current of c123
fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099,
@DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY,
@STATE,@ZIP
end
close c123
deallocate c123
declare @v datetime
declare @filename varchar(32)
select @v=getdate()
select @filename = 'XAPIALTMKT'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2)
+ SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT'
declare @cmdstring varchar(255)
Select @cmdstring = 'bcp conversion..expt1 out d: est' + @filename + ' -c -t"|" -r
-S -Usa -Psa'
exec master..xp_cmdshell @cmdstring
drop table expt1
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|