title: How do I pass paramters to function dynamically and execute stored procedure dynamically
body: <p>I have two questions to ask at this point</p>
  
  <ol>
  <li>How do I pass parameters to a function where the number of parameters is unknown until the function is invoked?(SQL Server 2008)</li>
  <li>How do I execute a stored procedure dynamically inside a function and return table?</li>
  </ol>
  
  <p>To make the question more clear I am adding a small piece of code for calling method in <code>C#</code> for  where the number of parameters is unknown until the function is invoked. Please excuse.</p>
  
  <pre><code>public void DemoFunction(string name,params string[] list)
  {
      for (int i = 0; i &lt; list.Length; i++)
      {
          // Do whatever we want
      }        
  }
  </code></pre>
  
  <p>In C# we can invoke the above method like</p>
  
  <pre><code>DemoFunction("MyName","A","B",....etc etc)
  </code></pre>
  
  <p><strong>QUESTION 1</strong></p>
  
  <p>Can I define parameters to be dynamic in the Sql Server function as like the <code>C#</code> code I have given above (<code>params string[] list</code>)?</p>
  
  <pre><code>CREATE FUNCTION [dbo].ExecStoredProc ( @ProcName varchar(40),@Param1 INT,@Param2 VARCHAR(50))
  (
      ....
      ....
      ....
  )
  </code></pre>
  
  <p><strong>QUESTION 2</strong></p>
  
  <ul>
  <li>The name of <code>stored procedure</code> to execute will be passed to this <code>function</code>.</li>
  <li>The parameters for executing <code>stored procedure</code> will be passed to this <code>function</code> but should be dynamic(as in Question 1)</li>
  <li>I need to execute <code>stored procedure</code> that is passing as parameter to this function dynamically inside the <code>function</code> and return the result as a table. </li>
  <li>How do I declare a table for returning the result where the number of columns are unknown until the procedure is executed?</li>
  </ul>
  
  <p>Here is the code I have tried so far</p>
  
  <pre><code> -- Here the parameters should be dynamic as like params string[] list
      CREATE FUNCTION [dbo].ExecStoredProc (@ProcName varchar(40),@Param1 INT,@Param2 VARCHAR(50))    
      RETURNS @ReturnTable TABLE
         (
             COLUMN1 INT
            ,COLUMN2 DATETIME
            ,COLUMN3 VARCHAR(50)      
         )
      AS
      BEGIN
         -- Is it possible like SELECT * INTO @ReturnTable FROM(Exec @ProcName)?
         INSERT INTO @ReturnTable  
  
         -- This should be dynamic including the number of parameters      
         Exec @ProcName 'Params'
  
         RETURN
      END
  </code></pre>