-
cursor in procedure
Hi
I AM UPDATING VIEW V1 IN THE PROGRAM.
AND I WANT TO PULL THE UPDATED DATA IN THER FLAT FILE
WHICH I HAVE MARKED 'P' IN THE CURSOR.
PLEAS ETELL ME HOW TO GO FOR THAT.
================================
CREATE PROCEDURE SP_P5
AS
declare c123 cursor for
select * from v1
declare @company char(12)
declare @COMPANYNAME char(60)
declare @CUSTOMERNUMBER char(12)
declare @FEDTAXID char(12)
declare @DUNSNUMBER char(9)
declare @S1099 char(3)
declare @DUNSSUFFIX char(4)
declare @ADDRESSSEQ int
declare @COUNTRY char(30)
declare @ADDRESSTYPE char(12)
declare @ADDRESS1 char(40)
declare @ADDRESS2 char(40)
declare @ADDRESS3 char(40)
declare @ADDRESS4 char(40)
declare @CITY char(30)
declare @STATE char(2)
declare @ZIP char(11)
declare @status char(1)
declare @dateofaction datetime
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,@status,@dateofaction
while @@fetch_status=0
begin
update v1 set ADDRESSSEQ=@ADDRESSSEQ,COUNTRY=@COUNTRY,ADDRESSTYP E=@ADDRESSTYPE,
ADDRESS1=@ADDRESS1,ADDRESS2=@ADDRESS2,ADDRESS3=@AD DRESS3,ADDRESS4=@ADDRESS4,CITY=@CITY,
STATE=@STATE,ZIP=@ZIP,status='P' 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,@status,@dateofaction
end
close c123
deallocate c123
declare @v datetime
declare @filename varchar(32)
select @v=getdate()
select @filename = 'XCUST'+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..V1 out d: est' + @filename + ' -c -t"|" -r
-S -Usa -Psa'
exec master..xp_cmdshell @cmdstring
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
|
|