title: OUTPUT TO MULTIPLE FILES FROM A SINGLE QUERY
body: <p>I am a little stuck... I am trying to take the output from a query and break it into numerous files based on a single criteria.  I am getting an error of converting a varchar type to int and I cannot figure out why.  Working in SQL Server 2008...</p>
  
  <pre><code>DECLARE @LOOP AS INT;
  DECLARE @SQL AS VARCHAR(MAX);
  DECLARE @BCP AS VARCHAR(MAX);
  DECLARE @COUNTER AS INT;
  DECLARE @FILENAME AS VARCHAR(MAX);
  
  SET @COUNTER='1'
  
  SELECT @LOOP = COUNT(DISTINCT LIST_ID) FROM DATA_TABLE
  WHERE STATUS='2' AND LIST_ID IS NOT NULL ;
  
  SET @SQL=(SELECT CUSTOMER_NO FROM CUSTOMER A, DATA_TABLE B
  WHERE A.CUSTOMER_ID=B.CUSTOMER_ID AND A.STATUS='2' AND LIST_ID='+@LOOP+');
  
  SET @FILENAME='QUERYOUT C:\Projects\FILE_"'+@LOOP+'.TXT'
  
  WHILE @COUNTER&lt;=@LOOP
  BEGIN
  SELECT 
  @BCP='BCP "'+@SQL+'+'+@FILENAME+''
  SET @COUNTER=@COUNTER+1
  END
  GO
  </code></pre>
  
  <p>The error I am getting is:</p>
  
  <pre><code>Msg 245, Level 16, State 1, Line 10
  Conversion failed when converting the varchar value '+@LOOP+' to data type int.
  </code></pre>
  
  <p>I am trying to use the LOOP value to let me know the contents of each file.  For example, LOOP='1' would mean the file contains the customer records associate with LIST_ID='1'</p>
  
  <p>Thoughts on the error?</p>