• 0

[SQL] Count Rows in a table


Question

Hi,

Im very new to ASP and SQL so sorry if my post sounds dumb. Im trying to create a stored procedure to count how many rows are in a table. I have searched the net and this is what I came up with;

CREATE PROCEDURE SP_Count

 SELECT COUNT(*) FROM tbl_Items

 END

... and this is the error im getting;

Msg 156, Level 15, State 1, Procedure SP_Count, Line 5
 Incorrect syntax near the keyword 'SELECT'.
 Msg 102, Level 15, State 1, Procedure SP_Count, Line 7
 Incorrect syntax near 'END'.

Any ideas? Thanks

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0

Also, if you're using Microsoft SQL Server, I would name the stored procedure with a prefix that is not "SP_xxxx".

In MSSQL, "SP_" denotes a "System Stored Procedures", and the server will always check the "master" database for it first, before finding it in your database. Making the procedure lookup take longer. It also has the potential to conflict with other stored procedures. You're better off naming your stored procedure "prc_Count" or "upc_Count", instead of "sp_Count".

Link to comment
Share on other sites

  • 0
Rather than counting *, try counting a named column (preferably your PrimaryID).

In MSSQL it doesn't matter, it knows that you're just looking for a count of rows and doesn't actually access each column or miss results that don't have non-null values for all columns.

Link to comment
Share on other sites

  • 0

If you have a primary ID field, which auto increments and starts at 1 (as most people do), you just use count = (select max(primaryID) from [tablename]).

* unless you are deleting rows from this table, then use a count

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.