存储过程中的空值参数 [英] null value parameter in stored procedure

问题描述

大家好，

我对存储过程中的查询有疑问.
当我的where子句中使用的参数@parm为null时，语法应为

 和列名 in (从 otherTable中选择 idfield 
如果@parm用值填充，则语法应为:

  and 列名=  @ parm  
 



解决方案
 
 
 声明  @ Query   varchar ( 1000 )

设置  @ Query  = ' 从TableName1中选择ColumnName1，columnName2，....，其中ColumnName1 ="Test"和'

 IF  isull( @ parm ，' ')= ' '
设置  @ Query  =  @ Query  + ' (从TableName2中选择CoulmnName2)中的CoulmnName2'
 ELSE 
设置  @ Query  =  @ Query  + '  CoulmnName2 =' +  @ parm 

执行  @ Query  
 
 
 
您好，



如果可以的话请尝试... 



如果@parm为NULL
  开始
   和columnname在(从otherTable中选择idfield)
   别的
     和columnname = @parm
  END 
 



问候，

Al Moje 
 
 
您可以执行以下操作:



和(isnull(@ parm，0)= 0或(从otherTable中选择idfield的)中的列名)



这对两种情况都适用.
 
 
Hello everyone,



I have a question about a query in a stored procedure.

When the used parmeter @parm is null in my where clause the syntax should be

and columnname in (select idfield from otherTable)


if the @parm is filled with a value the syntax should be:

and columnname = @parm




How to handle this problem in the where clause?
解决方案
Declare @Query varchar(1000)

Set @Query = 'Select ColumnName1, columnName2, ..... From TableName1 Where ColumnName1 = ''Test'' and '

IF isnull(@parm, '') = ''
	Set @Query = @Query + 'CoulmnName2 in (Select CoulmnName2 from TableName2)'
ELSE
	Set @Query = @Query + 'CoulmnName2 = ' + @parm 

Execute @Query

Hi,



Try this if could help...



IF @parm  is NULL        
  BEGIN        
   and columnname in (select idfield from otherTable) 
   else
     and columnname = @parm
  END  




Regards,

Al Moje

You can do something like the following:



and (isnull(@parm,0) = 0 OR columnname in (select idfield from otherTable))



This will work for both the conditions.


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