title: "Array parameter" TSQL
body: <p>I have a table with call data records one for each call with call data and one of the fields is the CallerId which we use when quering the DB.</p>
  
  <p>We use the following TSQL to simulate an array parameter, is this the way to go or are we way off?</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spStudio_Get_Smdr]
      @beginTime INT,
      @endTime INT,
      @subscribers VARCHAR(MAX) = NULL,
      @exchanges VARCHAR(MAX) = '1:',
      @beginDateValue int, 
      @endDateValue int
  AS
  BEGIN
      SET NOCOUNT ON;
  
      DECLARE @exch TABLE(Item Varchar(50))   
      INSERT INTO @exch
      SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
  
  
      DECLARE @subs TABLE(Item Varchar(19))
      INSERT INTO @subs
      SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
  
      SELECT
        ,[Level]
        ,[Timestamp]
        ,[EndYear]
        ,[EndDate]
        ,[EndTime]
        ,[CallingNumber]
        ,[DialledNumber]
        ..more fields between
        ,[DateValue]
        ,[TimeValue]
    FROM [SmdrFormat] AS S
      WHERE
          (S.[DateValue] BETWEEN @beginDateValue AND @endDateValue)
      AND
          (S.[TimeValue] BETWEEN @beginTime AND @endTime)
      AND
          EXISTS(SELECT [Item] FROM @exch WHERE [Item] = S.[Level])
      AND
          (@subscribers IS NULL OR (EXISTS(SELECT [Item] FROM @subs WHERE [Item] = S.[CallingNumber]
                                              OR [Item] = S.[DialledNumber])))
  
  END
  </code></pre>
  
  <p>I am using a table variable to store the temp table that I split from and | delimited string that we pass in as a parameter. The SplitDelimitedVarChar SQL Function slits a VarChar and returns a Table variable. The time and datevalues are stored as ints.</p>
  
  <p>All fields used in the WHERE clause is indexed.</p>
  
  <p>This works fine when the delimited string parameter is short but when it is becoming big (upp to several hundred strings delimited by |) it takes quite a long time to execute the query.</p>
  
  <p>Since I apperently is no SQL guru I feel that there is probably someone  who can tell me if I am really bad att SQL or just got some part wrong? Any suggestion is appreciated</p>
  
  <p>Thanks in advance
  Johan</p>