title: Must declare the scalar variable "@status" "User defined table type"
body: <p>I am using user-defined table types like below. Note , Please i am generating my query string dynamically and execute the string at the end. You may notice some quotes</p>
  
  <pre><code>      ALTER PROCEDURE MyPROC
          @phoneNo nvarchar(30) = null,
          @status dbo.StatusViewTableType READONLY
  
          AS 
          BEGIN
       if(@option = 1)
      begin
        set @sql = 'SELECT   c.id,c.name,c.NewIc,c.OldIc,c.Title,c.nationality,c.CreatedDate,c.companyNo,c.NonIc,c.Email
                          FROM    customer c WITH (NOLOCK)
                          left outer join
                          dbo.Address with(nolock) on c.id =  dbo.Address.CustomerID 
                          left outer join dbo.ContactNo on c.id=dbo.ContactNo.CustomerID
                          WHERE     (c.id &gt; 0) and ('
  
  
          if @oldIC &lt;&gt;'' and @phoneNo=''
              set @sql = @sql + ' (oldic ='''+ @oldIC + ''' )or'
  
          if @newIC &lt;&gt;'' and @phoneNo=''
              set @sql = @sql + ' (newIC ='''+ @newIC + ''' )or'
  
          if @companyno &lt;&gt;'' and @phoneNo=''
              set @sql = @sql + ' (companyno ='''+ @companyno + ''' )or'
  
          if @nonic &lt;&gt;'' and @phoneNo=''
              set @sql = @sql + ' (nonic ='''+ @nonic + ''' )or'
  
          If @phoneNo &lt;&gt;'' and @oldIC = '' and @newIC = '' and @companyno = '' and @nonic ='' 
          begin
              set @sql ='SELECT     c.id,c.name,c.NewIc,c.OldIc,c.Title,c.nationality,c.CreatedDate,c.companyNo,c.NonIc,c.Email
          FROM         dbo.Customer WITH (NOLOCK) c INNER JOIN
                                dbo.ContactNo WITH (NOLOCK)  ON c.id = dbo.ContactNo.CustomerID
          where   ContactNo = '''+ @phoneNo + ''' and  dbo.ContactNo.Contactability in (SELECT s.status   from  ' + [@status] + '  AS s ) '
          end
          if  @phoneNo='' 
          begin
          set @sql = Left(@sql,Len(@sql)-2)
          set @sql = @sql + ')'
          set @sql = @sql + '  and ( dbo.Address.Contactable in (SELECT  s.status   from ' + [@status] + '   AS s) or  dbo.ContactNo.Contactability in (SELECT  s.status from ' + [@status] + ' AS s) )'
          end 
  print @sql
  
  exec (@sql)
  end
  </code></pre>
  
  <p>END </p>
  
  <p>In my backend code I pass the parameters like below:</p>
  
  <pre><code>SqlConnection myConnection = null;
  SqlCommand myCommand = null;
  SqlDataReader myReader = null;
  myConnection = new SqlConnection(connectionString);
  myCommand = myConnection.CreateCommand();
  myCommand.CommandText = "GETCUSTOMER";
  myCommand.CommandType = CommandType.StoredProcedure;
  myCommand.CommandTimeout = 0;
  SqlParameter parameter;
  
  int l = ids.FirstOrDefault().Length;
  if (ids.FirstOrDefault().Length &gt; 0)
  {
      if (useDataTable)
      {
          parameter = myCommand.Parameters.AddWithValue("@status", CreateDataTable(ids));
      }
      else
      {
          parameter = myCommand.Parameters.AddWithValue("@status", CreateSqlDataRecords(ids));
      }
  
      parameter.SqlDbType = SqlDbType.Structured;
      parameter.TypeName = "dbo.StatusViewTableType";   
  }
  
  myCommand.Parameters.Add(new SqlParameter("@phoneNo", SqlDbType.NVarChar));    
  myCommand.Parameters["@phoneNo"].Value = phoneNo;
  </code></pre>
  
  <p>I am having an issue with the parameter <code>@status</code>. Its not recognized in the stored procedure. </p>
  
  <p>It's throwing an exception:</p>
  
  <blockquote>
    <p>Must declare the scalar variable "@status". </p>
  </blockquote>
  
  <p>Below is my <code>StatusViewTAbleType</code>.</p>
  
  <pre><code>CREATE TYPE [dbo].[StatusViewTableType] AS TABLE(
      [Status] [int] NOT NULL
  )
  GO
  </code></pre>
  
  <p>Any help would be appreciated.</p>