SQL Procedure efficency

[lsmyth1717]

I have the following stored procedure and was wondering if anyone could help me. I've not written sql in ages and I'm not sure if i'm working efficently. I'm trying to return a dataset to bind to a asp.net datagrid and then I also want to display the total count of records in a label. Does anyone know if i'l be able to achieve this using this stored procedure?

CREATE PROCEDURE [sp_RegisteredUsers]
AS

SELECT u_user_title, u_first_name, u_last_name
FROM UserObject

Select Count (*)
From UserObject
GO

 

[vongrunt]

Try to avoid "sp_" prefix for stored procedures.

Turn on SET NOCOUNT as a precaution.

This will work, but client code must be able to retrieve multiple recordsets from stored procedure call (something like .NextRecordset method in ADO)

Explicit ORDER BY for SELECT is not required but welcomed.

And depending on nature of client application and amount of data, COUNT(*) thing may not be necessary - but that's your choice.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[JamesLean]

I would not use a separate select just to get the count. Either:

1) Use an output parameter and use @@ROWCOUNT:

CREATE PROCEDURE apRegisteredUsers
  @rows int OUTPUT
AS

SET NOCOUNT ON

SELECT u_user_title, u_first_name, u_last_name
FROM UserObject

SELECT @rows = @@ROWCOUNT
GO

2) Just return the recordset and use something like rs.RecordCount on the app side to get the rowcount.

--James