title: Dynamic sql to populate a stored procedure variable
body: <p>I want to add a record in a <code>aTable</code> using a stored procedure. But before that I need to check if it's a duplicate entry. If its not duplicate then only add to <code>aTable</code>. </p>
  
  <p>I have one stored procedure that does two things</p>
  
  <ol>
  <li>checking for duplicate </li>
  <li>and if not duplicate then only add the record. </li>
  </ol>
  
  <p>In this stored procedure, I have </p>
  
  <ol>
  <li><code>@count</code> variable (scope is the stored procedure itself)  </li>
  <li>column values as parameters (7-8  as  <code>varchar</code> datatype)</li>
  </ol>
  
  <p>I am setting <code>@count</code> to <code>1</code> and then building the dynamic <code>@qry</code> string that will hold the <code>@count</code> value as follows using <code>@column</code> values</p>
  
  <pre><code>CREATE PROCEDURE sp_aTable_ADD
     @col1 varchar(20) = null,
     @col2 varchar(20) = null,
     @col3 varchar(20) = null,
     ...
     @colN varchar(20) = null,
  AS
     DECLARE @count int
     SET @count = 1
  
     DECLARE @qry nvarchar(max)
     SET @qry = 'SELECT @count = count(*) FROM aTable WHERE '
  
     IF @col1 IS NOT EMPTY
     BEGIN
         SET @qry = @qry + ' col1 = '+ ''''+@col1+''''
     END
  
     IF @col2 IS NOT EMPTY
     BEGIN
         SET @qry = @qry + ' AND col2 = '+ ''''+@col2+''''
     END
  
     ...
     ...
  
     IF @colN IS NOT EMPTY
     BEGIN
         SET @qry = @qry + ' colN = '+ ''''+@colN+''''
     END
  
     /*once all the stored procedure variables are done processing, close the sql*/
     SET @qry = @qry + ' )'
  
     /*final @qry value would end up looking like as follows
     SELECT @count = count(*) 
     FROM aTable 
     WHERE (col1 = 'val1' AND col2 = 'val2' AND col3 = 'val3')*/
  </code></pre>
  
  <p>Now, within this stored procedure I want to know the value of <code>@count</code> and if it is '0' then continue with the 'INSERT' procedure. I am stuck at getting '@count' value within this stored procedure.</p>
  
  <p>Any thoughts?</p>