What should I use a variable, cursor or something else ?

[rzirpolo]

Basically I want to be able to intitially setup a variable for the WHOLE database.

For example I set this to 000001. The from various different procedures I would like to call this so it can be input into columns into the database. Straight after in the same procedure I would increment it by 1 so if called again it would be 000002.

When looking at variable it seems I can only set these up as local and within single tables or procedures. And looking at Cursors this is dependent on a SELECT statement ?

I know this syntax is wrong but it hopefully shows what I'm trying to achieve,

DECLARE @BATCHNO INT

SET @BATCHNO = 000001

In a seperate procedure,

UPDATE TABLE01
SET BATCH_NO = @BATCHNO
WHERE......

SET@ BATCHNO = BATCHNO + 1

I attempted to use IDENTITY but this increments on every row insert. I have batches of data which will be put into a table and once that's done I want to update a blank column with a specific batch number.

I hope this makes sense and someone can offer some sound advice.

 

[Glyndwr]

rzirpolo,

try something like this

exec sp_main 5,10

where:

create proc sp_main  @start int, @end int
as
begin 
declare @cnt int


while  @start <= @end
	begin
		exec sp_doBatch (@start)
		set @start = @start +1
	end
	
end


create proc sp_doBatch @id in
as
begin
-- your batch ops
	insert into myTable1
	 select @id, tab2.* 
		from mytable2
		where mySelectField = @id
end

Glyndwr

 

[rzirpolo]

What exactly is this doing then ? Is this the only way I can do this as it seems very complicated.

I used to work with Oracle (now SQL Server) and you used to be able to create SEQUENCES which you could simply call and it would increment automatically.

 

[Glyndwr]

rzirpol.

I have obviously misunderstood your question,


do you want the sequence number to be kept between connections?

if so create a table with the batchno in and read this everytime you need to use it and increment when required.









Glyndwr

 

[SQLSister]

Why don;t you use an identity field to keep the records sequnetially? It autoincrements a number. Look in Books on line for instrcutions on how to do this.

 

[rzirpolo]

In my initial question I did state..

"I attempted to use IDENTITY but this increments on every row insert. I have batches of data which will be put into a table and once that's done I want to update a blank column with a specific batch number."

It's ok now as I have got round this by creating a dummy table to use.

Thanks anyway.