title: How to use variables for parameter in execute a stored procedure?
body: <p>I've declared and set some variables. Then I want to use that variable value as a parameter in executing a stored procedure. But I always get error </p>
  
  <blockquote>
    <p>Must declare the scalar variable.</p>
  </blockquote>
  
  <p>Here are my variables:</p>
  
  <pre><code>--declare variables for parameter @listweek
  declare @tgl_other varchar(50);
  
  --set variables for parameter @listweekset 
  set @tgl_other = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash where year(dates) = year(getdate()) order by numweek desc);
  
  --the variable values should be like this 30-(01-03-2012 - 08-03-2012) 
  --and this value that I want to use as parameter value in executing stored procedure
  </code></pre>
  
  <p>and this is the executing stored procedure:</p>
  
  <pre><code>insert into my_table (field1, field2, field3)
  EXEC my_store_procedure @tgl_other
  -- the parameter for my_store_procedure is like '30-(01-03-2012 - 08-03-2012)'
  </code></pre>
  
  <p>Is it my variables not correct? or I've wrong way to use variable for parameter? </p>
  
  <p>UPDATE:</p>
  
  <p>if I execute this query:</p>
  
  <pre><code>declare @tgl_other varchar(50);
  set @tgl_other = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash where year(dates) = year(getdate()) order by numweek desc);
  EXEC my_store_procedure @tgl_other
  </code></pre>
  
  <p>it works fine, but if I add <code>INSERT INTO..</code> statements it won't work. Why it happens?</p>
  
  <p><br/>
  UPDATE II:
  here is the whole query that I try to run</p>
  
  <pre><code>declare @tgl_other varchar(50);
  set @tgl_other = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash where year(dates) = year(getdate()) order by numweek desc);
  TRUNCATE TABLE mytable
  GO
  INSERT INTO mytable (field1, field2)    
  EXEC my_store_procedure @tgl_other
  GO
  </code></pre>