CODEWITHSUNDEEP
sql-serverstored-procedures
SƲmmēr Aƥ
SƲmmēr Aƥ

Reputation: 2504

Problems with stored procedure in SQL Server

I would like to retrieve total rows and also all values through a stored procedure in SQL Server. I have something like below. However, it mentioned of it is not contained in either an aggregate function or the GROUP BY clause. Appreciate for any reply. Thanks.

ALTER PROCEDURE abc.testing
    @facility varchar(50), 
    @date datetime
AS

SELECT count(*), * 
FROM ss_table 
WHERE _start = @date 
      And fid IN (SELECT id 
                  FROM ss_facilities 
                  WHERE name = @facility)

Upvotes: 1

Views: 264

Answers (3)

Bill Martin
Bill Martin

Reputation: 4943

You need a group clause when using an aggregate function like count. If ss_table has an id column, do the count function on it.

It would work like this:

SELECT count(id), <explicitly define column names except the id here> 
FROM ss_table 
WHERE _start = @date 
And fid IN (SELECT id FROM ss_facilities WHERE name = @facility)
group by <enter every column except the id from ss_table here>

Should work for you.

Upvotes: 1

Igor Borisenko
Igor Borisenko

Reputation: 3866

Try this

SELECT SS.cnt, ss_table.* 
FROM ss_table
     cross apply (SELECT count(*) cnt
                  FROM ss_table 
                  WHERE _start = @date 
                        And fid IN (SELECT id 
                                    FROM ss_facilities 
                                    WHERE name = @facility)                
                 )  ss 
WHERE _start = @date 
      And fid IN (SELECT id 
                  FROM ss_facilities 
                  WHERE name = @facility)

Upvotes: 1

bob
bob

Reputation: 390

You can't usefully mix and match aggregate queries and non-aggregate queries - split them up into two separate stored procedures.

(this specific error message is being presented because in order to return columns in an aggregate query, every column must either be in the "group by" fields, or have an aggregate function applied to it ("max", "count", etc). In this query, "*" meets neither of these criteria).

Upvotes: 0

Related Questions