title: Passing parameters to declated variable T-SQL
body: <p>I have a small version of my problem in a stored procedure i would like to pass the parameters to as follows:</p>
  
  <pre><code>  create procedure VariableTest
    @Date1 DATETIME,
    @Date2 DATETIME,
    @CustomName1 NVARCHAR(100),
    @CustomNum2 INT
  AS
  
   DECLARE @Condition NVARCHAR(MAX)
   DECLARE @SQL NVARCHAR(MAX)
  
   IF(LEN(@CustomName1) &gt; 0)
      SET @Condition = 'CustomerName = ' + @CustomName1;
  
   IF(LEN(@CustomNum2) &gt; 0 AND @CustomNum2 &gt; 0)
      SET @Condition = 'ClientNumber = ' + @CustomNum2;
  
   SET @SQL =
    'SELECT * FROM MyExampleTable ex
     WHERE DateEx1 between ' + @Date1 + ' AND ' + @Date2 + '
     AND ' + @Condition + '
     ORDER BY ex.Sort'
  
  exec @SQL
  </code></pre>
  
  <p>The problem i am running into, is that when i print the sql the dates do not have the a quotation around them and they are in datetime format, the other issue is that in the condition when the CustomerName is available the customer name comes out without quotes around them as well...</p>
  
  <p>How can i make it so that in the sql string i can include those quotes around the parameters passed?.</p>
  
  <p><strong>OPTED SOLUTION:</strong></p>
  
  <pre><code>  create procedure VariableTest
    @Date1 DATETIME,
    @Date2 DATETIME,
    @CustomName1 NVARCHAR(100),
    @CustomNum2 INT
  AS
  
   DECLARE @Condition NVARCHAR(MAX)
   DECLARE @SQL NVARCHAR(MAX)
  
   IF(LEN(@CustomName1) &gt; 0)
      SET @Condition = 'CustomerName = @MyVal1';
  
   IF(LEN(@CustomNum2) &gt; 0 AND @CustomNum2 &gt; 0)
      SET @Condition = 'ClientNumber = @MyVal2';
  
   DECLARE @MyParams NVARCHAR(200)
   SET @MyParams = N'@date1 datetime, @date2 datetime, @MyVal1 NVARCHAR(200), @MyVal2 int';
  
   SET @SQL =
    'SELECT * FROM MyExampleTable ex
     WHERE DateEx1 between @date1  AND @date2
     AND ' + @Condition + '
     ORDER BY ex.Sort'
  
  EXECUTE exec sp_executesql 
      @SQL,
      @MyParams,
      @date1 = @Date1,
      @date2 = @Date2,
      @MyVal1 = @CustomName1,
      @MyVal2 = @CustomNum2;
  </code></pre>