I need to add a row count to the end of this query as the last row. Not sure how to accomplish this

1

CAST(Sls.DIM300 AS Char(3)) + '###' AS Location, CAST(Cust.CustomerId AS char(6)) + '###' AS CustId,
CAST(Sls.Invoice# AS char(6)) + '###' AS InvNum, CAST(CONVERT(varchar, Sls.[Inv-Date], 1) AS char(8)) + '###' AS InvDate,
CAST(X.ASR_UserID AS Char(3)) + '###' AS Sid, CAST(Sku.MFGSkuCode AS Char(14)) + '###' AS MFGCode,
CAST(Sku.Description AS char(35)) + '###' AS Descr, CAST(Sls.DIM103 AS Char(30)) + '###' AS Man,
CAST(Sls.DIM400 AS Char(30)) + '###' AS Class, CAST(Sls.DIM102 AS Char(30)) + '###' AS Category,
CAST(Sls.Units AS Char(8)) + '###' AS QtySold, Sls.Sales AS NetExt
FROM dbo.Sales_Detail Sls LEFT OUTER JOIN
dbo.SkuMaster Sku ON Sls.DIM100 = Sku.SkuCode LEFT OUTER JOIN
dbo.Customer Cust ON Sls.DIM200 = Cust.CustomerId RIGHT OUTER JOIN
dbo.ASRCrossRef X ON Cust.UserId = X.ASR_UserID
WHERE (Cust.CustomerClassId IN ('P&R', 'WHOLESALE')) AND (Sls.DIM900 IN ('Retread', 'Tire')) AND (Sls.[Inv-Date] >= DATEADD(day,
- 1, { fn CURDATE() }))

2

Just return @@ROWCOUNT to the application and let it add it. Do not add it to your result set via T-SQL.

3

Not having your data, I couldn't test but try this:

WITH cte AS (
SELECT   Cast(Sls.DIM300 AS char(3)) + '###' AS Location
       , Cast(Cust.CustomerId AS char(6)) + '###' AS CustId
       , Cast(Sls.Invoice# AS char(6)) + '###' AS InvNum
       , Cast(Convert(varchar, Sls.[Inv-Date], 1) AS char(8)) + '###' AS InvDate
       , Cast(X.ASR_UserID AS char(3)) + '###' AS Sid
       , Cast(Sku.MFGSkuCode AS char(14)) + '###' AS MFGCode
       , Cast(Sku.Description AS char(35)) + '###' AS Descr
       , Cast(Sls.DIM103 AS char(30)) + '###' AS Man
       , Cast(Sls.DIM400 AS char(30)) + '###' AS Class
       , Cast(Sls.DIM102 AS char(30)) + '###' AS Category
       , Cast(Sls.Units AS char(8)) + '###' AS QtySold
       , Sls.Sales AS NetExt
       , Count(*) OVER(ORDER BY (SELECT NULL))  TotalCount
FROM     dbo.Sales_Detail Sls
         LEFT OUTER JOIN dbo.SkuMaster Sku ON Sls.DIM100 = Sku.SkuCode
         LEFT OUTER JOIN dbo.Customer Cust ON Sls.DIM200 = Cust.CustomerId
         RIGHT OUTER JOIN dbo.ASRCrossRef X ON Cust.UserId = X.ASR_UserID
WHERE    ( Cust.CustomerClassId IN ( 'P&R', 'WHOLESALE' ) )
         AND ( Sls.DIM900 IN ( 'Retread', 'Tire' ) )
         AND ( Sls.[Inv-Date] >= DateAdd(DAY, -1, { FN CURDATE() }) )
         )
SELECT 
     cte.Location
   , cte.CustId
   , cte.InvNum
   , cte.InvDate
   , cte.[Sid]
   , cte.MFGCode
   , cte.Man
   , cte.Class
   , cte.Category
   , cte.QtySold
   , 0 TotalCount
FROM cte
UNION ALL
SELECT  TOP 1
     NULL Location
   , NULL CustId
   , NULL InvNum
   , NULL InvDate
   , NULL [Sid]
   , NULL MFGCode
   , NULL Man
   , NULL Class
   , NULL Category
   , NULL QtySold
   , TotalCount
FROM cte;
4

Just because you can do something in T-SQL doesn't mean you should. @@ROWCOUNT already contains the number of rows in that query. Just return that to the application.

5

Whilst I fully agree with you, I think it depends a bit what the APP does / is able to do. Might not be possible / easy to modify that (for example: we have very strict rules about rollout of any change to the APP - it requires a full QA cycle - whereas a change to a single SProc, used by a single function in the APP, can be tested standalone and deployed)

That said, returning a final row, with all the columns of the rest of the resultset, to then include a COUNT (in one of the columns ...) leaves me with the question "What will be output in the other columns?"

We do do this type of thing to get a Final Total at the bottom of a resultset (and typically, in that scenario, we might also add Sub Totals for each "section" of the resulset too), so i suppose I could extend that to have a COUNT too ...