title: Get the Count of a SubQuery
body: <p>I need to count the result of some SQL query which I have no control over.  My idea is to wrap the inner SQL with a wrapper count query.  The outer SQL is quite simple, here is what I got:</p>
  
  <pre><code>select count(*) from ( x ) as CountQuery
  </code></pre>
  
  <p>where x is whatever the inner SQL goes.  The problem with this is some query would crash, namely IF ELSE END.</p>
  
  <p>How do I craft a wrapper SQL that will successfully wrap around any SQL?</p>
  
  <p>Here is an example of one of the inner SQL I need to count:</p>
  
  <pre><code>IF null &lt;&gt; 'PIPELINE_STAGE'
  BEGIN
  WITH cnts AS
  (
  SELECT 
         o.[OPPORTUNITY_ID]
        ,[OPPORTUNITY_NAME]
        ,[OPPORTUNITY_DETAILS]
        ,[IMAGE_URL]
        ,OPPORTUNITY_VALUE
        ,[PROBABILITY]
        ,[BID_CURRENCY]
        ,[BID_AMOUNT]
        ,[BID_TYPE]
        ,[BID_DURATION]
        ,[FORECAST_CLOSE_DATE]
        ,o.[CATEGORY_ID]
        ,c.CATEGORY_NAME
        ,c.BACKGROUND_COLOR
        ,o.[PIPELINE_ID]
        ,o.[STAGE_ID]
        ,[OPPORTUNITY_STATE]
        ,[RESPONSIBLE_USER_ID]
        ,u.[First_Name]
        ,u.[Last_Name]
        ,o.[VISIBLE_TO]
        ,o.VISIBLE_TEAM_ID
        ,o.[DATE_CREATED_UTC]
        ,o.[DATE_UPDATED_UTC]
        ,o.OWNER_USER_ID
        ,o.IMPORT_ID
        ,Follow_id
         ,ROW_NUMBER() OVER( ORDER BY CASE WHEN @sortOrder = 'OPPORTUNITY_NAME' THEN OPPORTUNITY_NAME END,
    CASE WHEN @sortOrder = 'RESPONSIBLE_USER' THEN ISNULL(u.[FIRST_Name], 'zz') END,
    CASE WHEN @sortOrder = 'FORECAST_CLOSE_DATE' THEN Forecast_Close_Date END,
    CASE WHEN @sortOrder = 'DATE_CREATED' THEN o.[DATE_CREATED_UTC]  END,
    CASE WHEN @sortOrder = 'FORECAST_CLOSE_DATE_DESC' THEN Forecast_Close_Date END DESC,
    CASE WHEN @sortOrder = 'DATE_CREATED_DESC' THEN o.[DATE_CREATED_UTC]  END DESC
         ) as intRow, 
       COUNT(o.INSTANCE_ID) OVER() AS Count
    FROM [Insightly].[dbo].[Opportunity] o with (nolock)
    LEFT JOIN [Insightly].[dbo].[Reference.Category] c with (nolock)
    on o.CATEGORY_ID = c.CATEGORY_ID AND c.INSTANCE_ID = @instanceId
    LEFT JOIN [Insightly].[dbo].[User] u WITH (NOLOCK)
    ON u.USER_ID = o.RESPONSIBLE_USER_ID AND u.INSTANCE_ID = @instanceId
    LEFT JOIN [Insightly].[dbo].[User.Follow] uf WITH (NOLOCK)
    ON (o.OPPORTUNITY_ID = uf.OPPORTUNITY_ID AND uf.USER_ID = @currentUserId AND uf.INSTANCE_ID = @instanceId)
  WHERE o.INSTANCE_ID = @instanceId AND TEMPORARY = 0 
  AND (@userId IS NULL OR [RESPONSIBLE_USER_ID] =@userId) AND (@categoryId IS NULL OR c.CATEGORY_ID = @categoryId)
  AND (@importId IS NULL OR o.IMPORT_ID = @importId)
  AND OPPORTUNITY_STATE in @opportunityState)
  SELECT * FROM cnts
  WHERE intRow BETWEEN @skip AND @take
  END
  ELSE
  BEGIN
  WITH cnts AS
  (
  SELECT 
         o.[OPPORTUNITY_ID]
        ,[OPPORTUNITY_NAME]
        ,[OPPORTUNITY_DETAILS]
        ,[IMAGE_URL]
        ,OPPORTUNITY_VALUE
        ,[PROBABILITY]
        ,[BID_CURRENCY]
        ,[BID_AMOUNT]
        ,[BID_TYPE]
        ,[BID_DURATION]
        ,[FORECAST_CLOSE_DATE]
        ,o.[CATEGORY_ID]
        ,c.CATEGORY_NAME
        ,c.BACKGROUND_COLOR
        ,o.[PIPELINE_ID]
        ,o.[STAGE_ID]
        ,[OPPORTUNITY_STATE]
        ,[RESPONSIBLE_USER_ID]
        ,u.[First_Name]
        ,u.[Last_Name]
        ,o.[VISIBLE_TO]
        ,o.VISIBLE_TEAM_ID
        ,o.[DATE_CREATED_UTC]
        ,o.[DATE_UPDATED_UTC]
        ,o.OWNER_USER_ID
        ,o.IMPORT_ID
        ,Follow_id
         ,ROW_NUMBER() OVER( ORDER BY ISNULL(p.Pipeline_Name, 'zz'), ISNULL([Pipeline.Stage].Stage_Order, 999)
         ) as intRow, 
       COUNT(o.INSTANCE_ID) OVER() AS Count
    FROM [Insightly].[dbo].[Opportunity] o with (nolock)
    LEFT JOIN [Insightly].[dbo].[Reference.Category] c with (nolock)
    on o.CATEGORY_ID = c.CATEGORY_ID AND c.INSTANCE_ID = @instanceId
    LEFT JOIN [Insightly].[dbo].[User] u WITH (NOLOCK)
    ON u.USER_ID = o.RESPONSIBLE_USER_ID AND u.INSTANCE_ID = @instanceId
    LEFT JOIN [Insightly].[dbo].[User.Follow] uf WITH (NOLOCK)
    ON (o.OPPORTUNITY_ID = uf.OPPORTUNITY_ID AND uf.USER_ID = @currentUserId AND uf.INSTANCE_ID = @instanceId)
  LEFT OUTER JOIN [Pipeline.Stage] with(nolock) ON o.PIPELINE_ID = [Pipeline.Stage].PIPELINE_ID AND o.STAGE_ID = [Pipeline.Stage].STAGE_ID
  LEFT OUTER JOIN [Pipeline] p with(nolock) ON o.PIPELINE_ID = p.PIPELINE_ID AND p.INSTANCE_ID = @instanceId
  WHERE o.INSTANCE_ID = @instanceId AND TEMPORARY = 0 
  AND (@userId IS NULL OR [RESPONSIBLE_USER_ID] =@userId) AND (@categoryId IS NULL OR c.CATEGORY_ID = @categoryId)
  AND (@importId IS NULL OR o.IMPORT_ID = @importId)
  SELECT * FROM cnts
  WHERE intRow BETWEEN @skip AND @take
  END
  </code></pre>