如何在SQL Server中的动态查询中处理空值？ [英] How to handle null values in dynamic query in SQL server?

问题描述

大家好，

我想在SQL Server过程中动态管理空值。我准备了以下查询，其中假设@storecode是存储过程的输入参数

Hi all,
I want to manage dynamically null values in SQL Server procedure. I have prepared following query, where assumed @storecode is the input parameter of stored procedure

declare @sql nvarchar(2000)
declare @storecode int =null

SET @sql = 'SELECT * FROM mch WHERE storecode = '+convert(varchar,@storecode)+' OR '+convert(varchar,@storecode)+' IS NULL'

print @sql 
EXECUTE sp_executesql @sql

当我在@storecode中传递有效整数值时它给出了欲望输出，但当我试图传递@ storecode = NULL时，它没有给出任何输出。我想传递@ storecode = NULL



我尝试过：



如何在SQL Server中的动态查询中处理空值？

When I pass valid integer value in @storecode it gives desire output, but when I tried to pass @storecode=NULL then it is not giving any output. I want to pass @storecode=NULL

What I have tried:

How to handle null values in dynamic query in SQL Server?

推荐答案

declare @sql nvarchar(2000)
declare @storecode int =null
set @storecode = null
SET @sql = N'SELECT * FROM mch WHERE ' + CASE WHEN cast ( @storecode as varchar) IS NULL THEN 'storecode IS NULL' ELSE 'storecode = ' +   cast( @storecode as varchar) END
 print @sql 
 exec( @sql)

更新解决方案，根据Chill的评论

updated solution, as per Chill's comment

如果 @storecode 但如果它为null，那么你想要返回整个表。



可能最简单/最清晰的方法是

It would appear that you want to filter the results if @storecode is provided but if it is null then you want the entire table returned.

Probably the easiest/clearest way is this

declare @sql nvarchar(2000)
declare @storecode int = null
 
SET @sql = 'SELECT * FROM mch '  
IF @storecode IS NOT NULL 
	SET @sql = @sql + 'WHERE storecode = ' + CAST(@storecode as varchar)

你没有得到任何当前查询的原因是因为在字符串结构中使用NULL会使整个字符串变为NULL。

The reason that you are not getting anything with your current query is because the use of NULL within the construction of the string renders the entire string NULL.

简单。 USE ISNULL函数处理空值

Its simple. USE ISNULL function to handle null value

declare @sql nvarchar(2000)
declare @storecode int =null
 
SET @sql = 'SELECT * FROM mch WHERE storecode = '+convert(varchar,ISNULL(@storecode,1))+''
 
print @sql 
EXECUTE sp_executesql @sql

谢谢。

Thanks.

这篇关于如何在SQL Server中的动态查询中处理空值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！