title: Must declare the scalar variable "@cnt"
body: <p>Please see the code below:</p>
  
  <pre><code>declare @Classification as varchar(5)
  set @Classification =''
  declare @ClassificationSQL as nvarchar(4000)
  declare @PersonSQL as nvarchar(4000)
  set @ClassificationSQL=''
  
  declare @counts int
  
  DECLARE NicheDeletionOffenderCursor CURSOR FOR  
      select classification from dbnicheoffenderclassificationlookup
      Open NicheDeletionOffenderCursor
      FETCH NEXT FROM NicheDeletionOffenderCursor INTO @Classification
  
      WHILE @@FETCH_STATUS = 0 
      BEGIN
      If @ClassificationSQL=''
          set @ClassificationSQL='classification like ' + char(39) + '%' + @Classification + '%' + char(39)
      else
          set @ClassificationSQL=@ClassificationSQL + ' OR classification like ' + char(39) + '%' + @Classification + '%' + char(39)
      FETCH NEXT FROM NicheDeletionOffenderCursor INTO @Classification
      END
  CLOSE NicheDeletionOffenderCursor 
  DEALLOCATE NicheDeletionOffenderCursor 
  
  SET @ClassificationSQL = 'select @cnt=count(*) from tbl_goccivgperson where lid=@LID and (' + @ClassificationSQL + ')' 
  SET @PersonSQL = 'SELECT * FROM tbl_goccivgperson WHERE LID=@LID and (' + @ClassificationSQL + ')' 
  
  DECLARE @CoreSystemIndexValue varchar(100)
  SET @CoreSystemIndexValue=''
  
  DECLARE NicheDeletionCursor CURSOR FOR  
  SELECT TOP 1 coresystemindexvalue from dbdisposals where datasetname='NICHECI'
  
      Open NicheDeletionCursor 
      FETCH NEXT FROM NicheDeletionCursor INTO @CoreSystemIndexValue
      WHILE @@FETCH_STATUS = 0  
          BEGIN
              EXECUTE sp_executesql @ClassificationSQL, N'@LID decimal(25,0),@cnt int OUTPUT', @LID=@CoreSystemIndexValue, @cnt=@Counts OUTPUT 'Line 38
              --If @Counts &gt; 0
                  --begin
                      --Check that all the nominals in Niche are ready for deletion
                      EXECUTE sp_executesql @PersonSQL, N'@LID decimal(25,0)', @LID=@CoreSystemIndexValue 'Line 42
                  --end
              --Else
                  --Check that all Persons (all are RCWs) in CIS and Niche are ready for deletion
  
          FETCH NEXT FROM NicheDeletionCursor INTO @CoreSystemIndexValue
          END
      CLOSE NicheDeletionCursor
  
  DEALLOCATE NicheDeletionCursor
  </code></pre>
  
  <p>The error I get is: Must declare the scalar variable "@cnt".  Commenting out either line 38 or lin2 42 resolved the problem.  The problem seems to be with line 42.  There is not reference to: @cnt in @PersonSQL so I cannot see why I am getting the problem.</p>