带/不带参数的条件存储过程 [英] conditional stored procedure with/without passing parameter

问题描述

我创建了一个存储过程，当没有任何参数作为参数传递时，它应该返回整个表.但是，如果通过了 studentId，则返回她的详细信息.像这样

I created a stored procedure which when passed nothing as parameter should return the entire table. But if the studentId is passed, then return her details. Something like this

create procedure usp_GetStudents @studentId int = null
as
  if (@studentId = null)
    select * from Student
  else
    select * from Student where studentId = @studentId

输出

exec usp_GetStudents -- No records returned though there are records in the table


exec usp_GetStudents @studentId = null  -- No records returned


exec usp_GetStudents @studentId = 256  -- 1 entry returned

只是想知道返回表中所有条目的语法/逻辑是否有问题?

Just curious to know if anything is wrong in the syntax/logic for returning all the entries of the table?

谢谢

推荐答案

您正在尝试使用 = 测试 null，比较运算符.如果您使用 ANSI 空值，则任何与 null 的比较都是 false.

You're trying to test for null using =, a comparison operator. If you're using ANSI nulls, any comparison against null is false.

如果@studentId 是any 值(或null)，以下表达式都是false:

Where @studentId is any value (or null) the following expressions are all false:

@studentId = null  -- false
@studentId > null  -- false
@studentId >= null  -- false
@studentId < null  -- false
@studentId <= null  -- false
@studentId <> null -- false

因此，为了测试 null，您必须使用一个特殊的谓词，为空，即:

So, in order to test for null you must use a special predicate, is null, i.e.:

@studentId is null

这篇关于带/不带参数的条件存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！