Output Parameter incorrect syntax near SELECT

Question

Im working on stored procedure in SQL SERVER. From this query im getting list of Tests with pagination (skip and size). I want to get total count of Tests in Tests table. I want to do it by OUTPUT parameter. My query looks like this:

@size INT,
@skip INT,
@orderDir VARCHAR(4),
@orderBy VARCHAR(20),
@totalCount INT OUTPUT

AS 
BEGIN
    CREATE TABLE #Ids (
        TestId INT
    );

    INSERT INTO #Ids SELECT TestId FROM Tests;

    SELECT SomeStuff
    FROM Tests t JOIN TestLines tl ON t.TestId = tl.TestId 
    GROUP BY SomeStuff
    ORDER BY 
        CASE WHEN @orderDir = 'asc'AND @orderBy = 'TestId' THEN t.TestId END,
        CASE WHEN @orderDir = 'desc' AND @orderBy = 'TestId' THEN t.TestId END DESC,
        -- more case when here
    OFFSET @skip ROWS
    FETCH NEXT @size ROWS ONLY;

    SET @totalCount = SELECT COUNT(TestId) FROM #Ids

    DROP TABLE #Ids;

END

I cant create procedure with query above. On line SET @totalCount = SELECT COUNT(TestId) FROM #Ids i get this error:

Incorrect syntax near 'SELECT'

My qustion is, how to make it work properly? How to get that value as OUTPUT from stored procedure?

Does this answer your question? [How to set variable from a SQL query?](https://stackoverflow.com/questions/3974683/how-to-set-variable-from-a-sql-query) — Nguyễn Văn Phong, Jan 16 '20 at 13:51

score 3 · Accepted Answer · answered Jan 16 '20 at 13:45

3

Use SELECT directly :

SELECT @totalCount = COUNT(TestId) 
FROM #Ids;

Or you can use subquery :

SET @totalCount  = (SELECT COUNT(TestId) FROM #Ids)

answered Jan 16 '20 at 13:45

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 2 · answered Jan 16 '20 at 13:47

0

You should fix by this way

Using SELECT

    SELECT @totalCount = COUNT(TestId) FROM #Ids

Using SET

   SET @totalCount = (SELECT COUNT(TestId) FROM #Ids)

answered Jan 16 '20 at 13:47

Nguyễn Văn Phong

13,506
17
39
56

Output Parameter incorrect syntax near SELECT

2 Answers2