Return count in Stored Procedure

问题:

I wrote a stored procedure to return a count. But I got a null value. Can anybody tell me where is the problem in my stored procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ValidateUser]
@UserName varchar(50),
@Password varchar(50),
@Num_of_User int output

AS 
BEGIN
  SET NOCOUNT ON;

  SELECT @Num_of_user =COUNT(*) 
    FROM login
   WHERE username = @UserName 
     AND pw = @Password

  RETURN

END

回答1:

you are setting the value into the variable @num_of_user. add select @num_of_user after the query

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[ValidateUser]
    @UserName varchar(50),
    @Password varchar(50),
    @Num_of_User int output

AS
BEGIN
    SET NOCOUNT ON;
     SELECT @Num_of_user =COUNT(*) 
         FROM login
         WHERE username=@UserName AND pw=@Password

     SELECT @Num_of_user
return
END

回答2:

It doesn't actually return the count, it's an output parameter.

Either change your sproc to return @num_of_user or check the output parameter instead of the return value.

回答3:

Procedures and functions in SQL server are a bit confusing to me, since a procedure can also return a value. However, you can achieve what you wish by removing the output parameter @Num_of_user and replacing

SELECT @Num_of_user =COUNT(*) 
    FROM login
   WHERE username = @UserName 
     AND pw = @Password

with

SELECT COUNT(*) 
    FROM login
   WHERE username = @UserName 
     AND pw = @Password

The last select statement is always returned from a PROCEDURE in SQL server.

回答4:

You do not need the isnull. Select count(*) always return a non-null value. You can even issue a

select count(*) from anytable where 1=2 

and you'll get a 0, not a null. I think that your problem arrives when you exec the sp. You may be lacking the 'output' keyword next to the @Num_of_User. Your call should be something like

declare @outNum int 
exec ValidateUser 'some', 'wrong', @outNum output
print @outNum

Notice the 'output' keyword to the right of the parameter

回答5:

Just another method but outside the Stored Procedure a simple SELECT @@ROWCOUNT can work to get the number of rows.

回答6:

Had the same issue when it returned null; try to use parenthesis:

SELECT @Num_of_user = ( COUNT(*) 
    FROM login
   WHERE username = @UserName 
     AND pw = @Password )