SQL Server Performance

Stored procedure taking an array of values

Discussion in 'SQL Server 2008 General Developer Questions' started by KathyTheGreat, Jun 15, 2009.

  1. KathyTheGreat New Member

    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.
  2. MohammedU New Member

  3. KathyTheGreat New Member

    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?
  4. FrankKalis Moderator

    If the final output is based on the existance of rows in the temp table, I would probably do something like:
    IF EXISTS (SELECT 1 FROM < temp table >)
    ... do stuff here
    ... do stuff there.
  5. KathyTheGreat New Member

    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.
  6. FrankKalis Moderator

    In a way, I would say that it is best practise to drop it explicitly yourself at the end of the procedure. But SQL Server automatically droppes the table when the session that created the table closes.
  7. ndinakar Member

    Have you tried the Table Valued Parameter in 2008?

Share This Page