Stored procedure taking an array of values

One of the arguments I need for my stored procedure is a list of acct numbers. The list could have 'All', which means all acct numbers in the database. It could have 2 acct numbers, or 20 acct numbers. Each acct# is 9 characters long. Since I don't know how many acct numbers they will select, the nvarchar[4000] may not be enough ( although it probably will be ). So, inside the stored procedure it would do a "select * from transactions where acctno in ('123456789', '223456789','323456789'), etc. What's the best data type to use for this? Thanks.

You can use Varchar datatype, I don't see any reason to use nVarchar because you are passing the number but not special characters...when you use Varchar you it will be 8000 max...or if you want bigger than this then you can try Varchar(max)...
Check the following...
http://sqlblogcasts.com/blogs/tonyr...dure-without-parsing-string-manipulation.aspx
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Hi Mohammed - thanks for the help. One last part to this problem ( I promise ). I have the code taking in a list of values ( like you mentioned ). How can perform an action where if the temp table is empty select these 5 columns else if the temp table has rows, select these 7 columns. I just need to know how to switch on whether the temp table has values in it or not. Should I check on if @@rowcount <> 0?

Yes. That did the trick. One last question ( I promise !!). If you make a temp table inside a stored procedure, do you need to explicitly drop it at the end of the procedure or does it get deleted when the stored procedure ends?
Thanks for all your help.

Have you tried the Table Valued Parameter in 2008?