title: Pass string variable into procedure and add it to a query
body: <p>I have an application written in C#, which connects to database and analyze its data, database stores information about execution of automated tests, what I would like to do is to retrieve those tests that fulfill the above given conditions. But we are having different projects and will be supporting more and more so I do not want to construct different procedure for each one, but pass the name - 2nd parameter deploy as the parameter so the query will depend on the project and return the data to the application, then I will send it in a report.</p>
  
  <p>For the time being it looks like this:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[SuspectsForFalsePositive](@build_id INT, @deploy VARCHAR(25)) 
  AS
  BEGIN
  
      SET NOCOUNT ON;
  
      DECLARE @i int, @build int, @deployname varchar(25), @SQL varchar(max)
      DECLARE @result table (tc int, fp float)
      SET @i = 0
      SET @build = @build_id
      SET @deployname = @deploy
  
      SET @SQL = 'insert '+@result+'select testcase_id, fail_percentage FROM [BuildTestResults].[dbo].['+@deployname+'TestCaseExecution]
                     where build_id = @build and fail_percentage &gt;= 70'
  
  --INSERT @result select testcase_id, fail_percentage FROM [BuildTestResults]
  --.[dbo].[ABCTestCaseExecution]
  --where build_id = @build and fail_percentage &gt;= 70
  --commented works
      EXEC(@SQL)
      WHILE (@@rowcount = 0)
      BEGIN
      SET @build = @build - 1
      EXEC(@SQL)
  --INSERT @result select testcase_id, fail_percentage FROM [BuildTestResults].[dbo]. --[ABCTestCaseExecution]
  --where build_id = @build and fail_percentage &gt;= 70
  --commented works
      END
      select * from @result order by fp DESC
  END
  GO
  </code></pre>
  
  <p>Thanks for any advice !</p>