title: SQL Server : using a LEN or variable in where clause that contains a join
body: <p>I have created a map table to find various unique strings within a large list of unique hostnames.</p>
  
  <p>The initial code works if I enter the various lengths i.e. <code>varchar(2)</code>, <code>varchar(11)</code>, etc. It's trying to reference the variable lengths is where my issues began.</p>
  
  <p>I have tried several different combinations before attempting to use a variable.</p>
  
  <p>For example in the where clause, substituting the <code>varchar(2)</code> with the <code>m.[HostNameAlias_IDLength]</code></p>
  
  <p>I am also having difficulty using variables.</p>
  
  <p>Any thoughts would be much appreciated.</p>
  
  <p>TM</p>
  
  <p>P.S. A listing of the code and sample tables are listed below.</p>
  
  <p>Table1</p>
  
  <ul>
  <li><code>HostNameAlias_id</code> (pk, varchar(5), not null)</li>
  <li><code>ProjectName_ID</code> (int, not null)</li>
  <li><code>HostnameAlias_IDLength</code> (computed, int, null) </li>
  </ul>
  
  <p>Data</p>
  
  <pre><code>HostNameAlias_ID    ProjectName_ID  HostNameAlias_IDLength
  ----------------------------------------------------------
  H123456789023456            16009   16
  B123456789023               16005   13
  C1234567890                 16009   11
  d12345678                   16009   9
  e123456                     16009   8
  f12345                      16003   6
  g1234                       16035   5
  h123                        16035   4
  j12                         16005   3
  k1                          16007   2
  </code></pre>
  
  <p>Table2</p>
  
  <ul>
  <li>[<code>host name</code>] (pk, nvarchar(50), not null</li>
  <li><code>Projectname_id</code> (int, not null)</li>
  </ul>
  
  <p>Sample data:</p>
  
  <pre><code>Host name          Title           projectname_ID
  --------------------------------------------------
  C1234567890a1      vp                  16009
  C1234567890a2      avp                 16009
  h12335             student             16009
  h12356             teacher             16009
  h12357             prof                16009
  </code></pre>
  
  <p>Query</p>
  
  <pre><code>DECLARE @len = INT()
  DECLARE @slen = VARCHAR(2);
  
  SELECT DISTINCT
      @len = m.[HostNameAlias_IDLength],
      @slen = CONVERT(varchar(2), m.[HostNameAlias_ID]),
      c.[Host Name],
      m.[projectname_id]
  FROM 
      [table1] c
  JOIN 
      [table2] m ON c.[projectname_id] = m.[projectname_id]
  WHERE 
      CONVERT(varchar(2), [Host Name]) IN (SELECT [HostNameAlias_ID] 
                                           FROM [table2])
  </code></pre>