0

I have query which returns single value (i.e) count. I'm exceuting it using the stored procedure in the following way and using execute reader with dataset to get single value 

CREATE PROCEDURE GetCnt
@EmpNo char(4)
AS
BEGIN
   SET NOCOUNT ON;
   Declare @Cnt int
   SELECT @Cnt = count(*)
   FROM employees
   WHERE EMPLNO = @EmpNo
      AND test = 'p'            

   BEGIN 
      SELECT @Cnt
   END
END

is this effcient way or Do I need to use the execute.scalar() and return value directly from the query instead of assigning to @cnt can any one advise me

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user993935
  • 3,563
  • 4
  • 18
  • 15
  • Seems ok, except you don't need an extra `BEGIN .... END` around the final `SELECT` statement .... Since this stored proc returns exactly one row and one column, you would typically use `var result = sqlCommand.ExecuteScalar()` from .NET to call this – marc_s Dec 07 '11 at 22:26
  • Since you are retunring a result set with a single row with a single column, you should use ExecuteScaler. Its optomised for that situation. – user957902 Dec 07 '11 at 22:28
  • try it both ways and see... I doubt it'll make much of a difference anyway. – Conrad Frix Dec 07 '11 at 22:28
  • Ok execute scalar in more optimised than execute reader in this situation. Thank you. – user993935 Dec 07 '11 at 22:33

2 Answers2

1

All ExecuteScalar does is get the first field from the first record.

Can't you just SELECT the count directly?

BEGIN
    SET NOCOUNT ON
    SELECT Count(*) FROM employees WHERE EMPLNO = @EmpNo AND test='p'
END
Cylindric
  • 5,858
  • 5
  • 46
  • 68
0

You do not need to create the variable. You just need the following:

CREATE PROCEDURE GetCnt
@EmpNo char(4)
AS
BEGIN
   SET NOCOUNT ON;
   SELECT count(1)
   FROM employees
   WHERE EMPLNO = @EmpNo
      AND test = 'p'            
END

Since this is only one value being returned from the stored procedure, you will likely want to use ExecuteScalar()

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63