In is unlimited AFAIK. But usually a join is a better way to go as rsharma already stated.

Not with a sp, you'd have to use a function for that, but dynamic sql is forbiden in functions (in the current conditions).

Now you know why you should stay away...

We can have a look if you want.

Please post the ddl of the table (including indexes).

Post all the queries (with execution plan) ran on that table and their frequency, their...

No chance, function on a column = SCAN.

If you are inferring that a math operation is faster than a convert between datatypes, then you are right. But I would...

check out dbcc inputbuffer, @@spid

once you have the next value > >

Update table set col = right(12, replicate('0', 11) + convert(varchar(12), @NextID))

Depends on the other queries. I don't have the info required to make that decision for you. Is 1 second aceptable for this query??

If so I don't see...

They can both return something...

I don't have a list of difference but there are a lot of small differences between those 2.

Yes, but that was corrected later on.

You're concatenating a string for the server to execute. This string must be a valid statement or else it fails.

try print [concatenation here] and see if you can execute...

Try adding one and see if an index seek is used to get the count.

Exactly..., they both find the first usefull index and scan it to get the row count.

Is there an index on the sdate column?

...and you must tripple the quotation marks between the concatenation so that the exec statement is valid.

The scope of the cursor starts and ends in the exec() statement, you must declre it global if you want to use it after the exec... or include the while...