SQL 存储过程中的返回计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5144584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return count in Stored Procedure
提问by user637995
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
回答by atbebtg
you are setting the value into the variable @num_of_user. add select @num_of_user after the query
您正在将值设置到变量@num_of_user 中。在查询后添加 select @num_of_user
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
回答by BenCr
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.
更改您的 sproc 以返回 @num_of_user 或检查输出参数而不是返回值。
回答by Erik A. Brandstadmoen
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
SQL server 中的过程和函数让我有点困惑，因为过程也可以返回一个值。但是，您可以通过删除输出参数@Num_of_user 并替换
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.
最后一个 select 语句总是从 SQL Server 中的 PROCEDURE 返回。
回答by Alberto Gaona
You do not need the isnull. Select count(*) always return a non-null value. You can even issue a
您不需要 isnull。Select count(*) 总是返回一个非空值。你甚至可以发出一个
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
你会得到一个 0，而不是一个空值。我认为当你执行 sp 时你的问题就出现了。您可能缺少@Num_of_User 旁边的“输出”关键字。你的电话应该是这样的
declare @outNum int
exec ValidateUser 'some', 'wrong', @outNum output
print @outNum
Notice the 'output' keyword to the right of the parameter
请注意参数右侧的“输出”关键字
回答by Smeghead Sev
Just another method but outside the Stored Procedure a simple
SELECT @@ROWCOUNTcan work to get the number of rows.
只是另一种方法，但在存储过程之外，一个简单的方法
SELECT @@ROWCOUNT可以用来获取行数。
回答by Farzad Jafarizadeh
Had the same issue when it returned null; try to use parenthesis:
当它返回 null 时有同样的问题；尝试使用括号：
SELECT @Num_of_user = ( COUNT(*)
FROM login
WHERE username = @UserName
AND pw = @Password )
