SQL 检查存储过程中的参数是否为空或空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9855331/
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
Check if a parameter is null or empty in a stored procedure
提问by user710502
I know how to check if a parameter is null but i am not sure how to check if its empty ... I have these parameters and I want to check the previous parameters are empty or null and then set them like below
我知道如何检查一个参数是否为空，但我不知道如何检查它是否为空......我有这些参数，我想检查前面的参数是空的还是空的，然后像下面这样设置它们
ALTER PROCEDURE [dbo].[GetSummary]
@PreviousStartDate NVARCHAR(50) ,
@PreviousEndDate NVARCHAR(50) ,
@CurrentStartDate NVARCHAR(50) ,
@CurrentEndDate NVARCHAR(50)
AS
BEGIN
IF(@PreviousStartDate IS NULL OR EMPTY)
SET @PreviousStartdate = '01/01/2010' for example..
I would appreciate the help.
我将不胜感激。
回答by Rex Miller
I sometimes use NULLIFlike so...
我有时会像这样使用NULLIF...
IF NULLIF(@PreviousStartDate, '') IS NULL
There's probably no reason it's better than the way suggested by @Oded and @bluefeet, just stylistic preference.
可能没有理由比 @Oded 和 @bluefeet 建议的方式更好，只是风格偏好。
@danihp's method is really cool but my tired old brain wouldn't go to COALESCE when I'm thinking is null or empty :-)
@danihp 的方法真的很酷，但是当我认为是 null 或空时，我疲惫的老大脑不会去 COALESCE :-)
回答by Oded
Here is the general pattern:
这是一般模式：
IF(@PreviousStartDate IS NULL OR @PreviousStartDate = '')
''is an empty string in SQL Server.
''是 SQL Server 中的空字符串。
回答by Taryn
you can use:
您可以使用：
IF(@PreviousStartDate IS NULL OR @PreviousStartDate = '')
回答by Pavel Hodek
What about combining coalesceand nullif?
结合coalesce和nullif怎么样？
SET @PreviousStartDate = coalesce(nullif(@PreviousStartDate, ''), '01/01/2010')
回答by Erik K.
Another option:
另外一个选项：
IF ISNULL(@PreviousStartDate, '') = '' ...
see a function based on this expression at http://weblogs.sqlteam.com/mladenp/archive/2007/06/13/60231.aspx
在http://weblogs.sqlteam.com/mladenp/archive/2007/06/13/60231.aspx查看基于此表达式的函数
回答by Fábio Nascimento
To check if variable is null or empty use this:
要检查变量是否为 null 或为空，请使用以下命令：
IF LEN(ISNULL(@var, '')) = 0
回答by Alam Usmani
If you want to use a parameter is Optional so use it.
如果你想使用一个参数是可选的，那么使用它。
CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'
GO
回答by TracyT
You can try this:-
你可以试试这个：-
IF NULLIF(ISNULL(@PreviousStartDate,''),'') IS NULL
SET @PreviousStartdate = '01/01/2010'
回答by Jodrell
If you want a "Null, empty or white space" check, you can avoid unnecessary string manipulation with LTRIMand RTRIMlike this.
如果你想要一个“空，空或空白”检查，就能避免不必要的字符串操作LTRIM和RTRIM这样。
IF COALESCE(PATINDEX('%[^ ]%', @parameter), 0) > 0
RAISERROR ...
