CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Jeremy
Jeremy

Reputation: 46350

query return records or count of records

In an application we are building, in some cases, we want to display a set of indicators on a dashboard that include only counts of records. When the user clicks on those indicators, we want to display those records.

Most of the time, counts will suffice so we don't want to query the database and return the actual records just to get counts. Is there some what to write a single stored procedure which can leverage the same select, but in one case only calculate the count, and in others return the actual records?

Upvotes: 0

Views: 89

Answers (3)

Eric
Eric

Reputation: 5743

I think a SP return different results / multiple results is one of the code smell. As you will need to handle the same SP differently for the results at the run time, which is error prone.

As we all know the SP can return a int value, we can make use of this:

CREATE sp_GetProjectData(@ReturnEmpty bit = 0)
AS
BEGIN
    DECLARE @Count int = (SELECT COUNT(*) FROM Project)
    -- Always return the table columns, but can be empty
    SELECT * FROM Project WHERE @ReturnEmpty = 0

    -- Return the count, always positive
    RETURN @Count
END

Upvotes: 0

Farzan
Farzan

Reputation: 41

You may achieve this by wither setting a parameter and process it with if/case statements in your sproc.

Create procedure sptest_return (@CountOnly bit = 0)
As Begin
    If @CountOnly = 0 
    Begin
        Select * from test_table
    End
    Else
    Begin
        Select count(*) as [Count] from  test_table
    End
End

If you call it like sptest_return it will return all rows and if you call it with 1 value as the argument like sptest_return 1 then it will only show the row count.

Hope this helps.

Upvotes: 4

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

It's not a good idea to select data via stored procedure. Better use UDF, best use a single-statement-UDF.

In this case you could select your data by calling

SELECT * FROM dbo.YourFunction(Prm1, Prm2, ...)

The great fortune with this is, that you can use it this way too:

SELECT COUNT(*) FROM (SELECT * FROM dbo.YourFunction(Prm1, Prm2, ...)) AS tbl

The Query-Plan will not execute more of your function than it is needed to find the correct count. With a stored procedure this is not possible...

If you must stick to the SP-approach do it as suggested before. Use a Parameter stating "GetCountOnly" or something like this.

HTH

Upvotes: 0

Related Questions