CODEWITHSUNDEEP
sqlfirebird
zac
zac

Reputation: 4918

Convert select into stored procedure best approach

I use this SQL to get count for every group of type.

select 
    mytype, count(mytype)
from types1
group by 1

The result is 5 records with count for each type. I need to convert this to a stored procedure; should I write the above SQL using For...Select or should I return single value using Select...Where...Into 5 times for each type?

I will use the return counts to update a master table and types may increase in the future.

Upvotes: 0

Views: 769

Answers (1)

lc.
lc.

Reputation: 116528

That depends on what you want out of the procedure:

  • If you want the same output as your select with five rows, use a FOR SELECT. You will get one row for each type and an associated count. This is probably the "standard" approach.

  • If however you want five output variables, one for each count of each type, you can use five queries of the form SELECT COUNT(1) FROM types1 WHERE mytype = 'type1' INTO :type1. Realize though that this will be five queries and you may be better off doing a single FOR SELECT query and looping through the returned rows in the procedure. Also note that if you at some point add a sixth type you will have to change this procedure to add the additional type.

  • If you want to query a single type, you can also do something like the following, which will return a single row with a single count for the type in the input parameter:

    CREATE PROCEDURE GetTypeCount(
    TypeName VARCHAR(256)
)
RETURNS (
    TypeCount INTEGER
)
AS
BEGIN
    SELECT COUNT(1)
    FROM types1
    WHERE mytype = :TypeName
    INTO :TypeCount;

    SUSPEND
END

Upvotes: 2

Related Questions