codice:
create procedure AuthorRigaPerRiga
/* variabile n contiene riga di partenza */
/* variabile m contiene numero di righe */
@n int, @m int
as
/*
creo una tabella temporanea con la stessa struttura
dell'originale su cui faccio le query
eliminando eventuali campi non interessanti per la query,
volendo aggiungo un campo per il numero di riga
*/
if exists (Select [name] from tempdb..sysobjects
where id=OBJECT_ID('tempdb..#authors'))
drop table #authors
CREATE TABLE #authors (
au_id varchar (11) NOT NULL ,
au_lname varchar (40) NOT NULL ,
au_fname varchar (20) NOT NULL ,
phone char (12) NOT NULL ,
address varchar (40) NULL ,
city varchar (20) NULL ,
state char (2) NULL ,
zip char (5) NULL ,
contract bit NOT NULL ,
riga int
)
/* dichiaro una variabile dello stesso tipo nella tabella
temporanea per ogni campo */
declare @au_id id,
@au_lname varchar(40),
@au_fname varchar (20),
@phone char (12),
@address varchar (40),
@city varchar (20),
@state char (2),
@ip char (2),
@contract bit
/* cursore */
declare k SCROLL cursor for select * from authors
/* apro */
open k
/* recupero una riga nelle variabili*/
fetch absolute @n from k into
@au_id,
@au_lname,
@au_fname,
@phone,
@address,
@city,
@state,
@ip ,
@contract
insert into #authors values
(
@au_id,
@au_lname,
@au_fname,
@phone,
@address,
@city,
@state,
@ip ,
@contract,
@n+@m
)
while (@@fetch_status = 0 And @m>1)
begin
set @m = @m-1
fetch next from k into
@au_id,
@au_lname,
@au_fname,
@phone,
@address,
@city,
@state,
@ip ,
@contract
insert into #authors values
(
@au_id,
@au_lname,
@au_fname,
@phone,
@address,
@city,
@state,
@ip ,
@contract,
@n+@m
)
end
close k
deallocate k
select * from #authors
Rispondi quotando