are stored proc local variables safe and unique for each call

[Glennson]

I need a simple, efficient, safe sequence generator. Will this work? With no transaction?

...
Declare @foo as int

update seq set @foo=seq_val, seq_val=seq_val+1
  where seq_name = 'yowza'

select @foo
...

thanks

[ck9663]

it depends on how you will use this sequence of numbers. This will return a table-like sequence of numbers. Since it's a Common Table Expression (CTE), you can use it to join to other table. Just make sure you don't over use this CTE, it's not a physical table and no index can be created for this one and could degrade the overall performance of your query.

Good Luck!!!

~~ CK