Select statement and WITH statement error

Question

I would like to select deptUserCount and childDeptCount at once in one procedure.

ALTER PROCEDURE [dbo].[sp_dept_user_select]     
@dept_no        INT,
@company_no     INT

AS
BEGIN   
SET NOCOUNT ON;

SELECT
(   SELECT COUNT(U.user_no) 
    FROM [dbo].[tb_user] U WITH(NOLOCK)
    INNER JOIN [dbo].[tb_dept_user] DU WITH(NOLOCK)
    ON U.user_no = DU.user_no
    WHERE DU.dept_no = @dept_no
    AND U.company_no = @company_no
    AND U.user_level < 200
) AS deptUserCount

,(  WITH tblChild AS
    (
        SELECT dept_no, parent_dept_no, name
        FROM [dbo].[tb_dept] D WITH(NOLOCK)
        WHERE parent_dept_no = 2
        AND D.company_no = 1

        UNION ALL
        SELECT D.dept_no, D.parent_dept_no, D.name
        FROM [dbo].[tb_dept] D  WITH(NOLOCK)
        INNER JOIN tblChild  
        ON D.parent_dept_no = tblChild.dept_no
        WHERE D.company_no = 1
    )
    SELECT COUNT(D.dept_no)
    FROM [dbo].[tb_dept] D WITH(ROWLOCK)
    WHERE dept_no IN (SELECT dept_no FROM tblChild)
) AS childDeptCount

END

This is the code that I made right now. But there is an error at the WITH statement saying

incorrect syntax error

How should I fix this error? Or do I have to make a separate procedure to get what I want?

Adding the **exact error message** to your post would make it much easier for you to get an answer. Please [edit] your post to include it. Thanks. — Ken White, Mar 07 '19 at 02:50
[`WITH [cte]` cannot be used like that](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017) - it must appear at the top-level. — user2864740, Mar 07 '19 at 02:53
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Mar 07 '19 at 04:51

score 1 · Answer 1 · answered Mar 07 '19 at 02:52

1

You should go for something like this

WITH 
deptUserCount AS (
   SELECT ...
),
tblChild AS (
   SELECT ...
   UNION ALL
   SELECT ... FROM tblChild ...
)
SELECT ...
FROM tblChild JOIN deptUserCount...

answered Mar 07 '19 at 02:52

Malta

544
3
8

score 1 · Accepted Answer · answered Mar 07 '19 at 02:53

you can try like below

with deptUserCount as
(   SELECT COUNT(U.user_no) as dptcount 
    FROM [dbo].[tb_user] U WITH(NOLOCK)
    INNER JOIN [dbo].[tb_dept_user] DU WITH(NOLOCK)
    ON U.user_no = DU.user_no
    WHERE DU.dept_no = @dept_no
    AND U.company_no = @company_no
    AND U.user_level < 200
) ,tblChild as
(
       SELECT dept_no, parent_dept_no, name
        FROM [dbo].[tb_dept] D WITH(NOLOCK)
        WHERE parent_dept_no = 2
        AND D.company_no = 1

        UNION ALL
        SELECT D.dept_no, D.parent_dept_no, D.name
        FROM [dbo].[tb_dept] D  WITH(NOLOCK)
        INNER JOIN tblChild  
        ON D.parent_dept_no = tblChild.dept_no
        WHERE D.company_no = 1
), childDeptCount as
(
SELECT COUNT(D.dept_no) as childdptCnt
FROM [dbo].[tb_dept] D WITH(ROWLOCK)
WHERE dept_no IN (SELECT dept_no FROM tblChild)
) select a.childdptCnt,b.dptcount  from childDeptCount a,deptUserCount  b

Select statement and WITH statement error

2 Answers2