Sql：我有一个要求，我的所有输入参数都是可选的。所以我必须检查输入参数是null还是notnull [英] Sql : I have a requirement where all my input parameters are optional.so I have to check whether the input parameters are null or notnull

问题描述

说我有4个参数作为输入

clientid，provider，status和timeinterval

如果所有参数都为null然后我将返回表中的所有记录

如果clientid = 22且其他参数为null则返回clientid = 22的记录将返回

如果client = 22且provider = xxx则记录clientid = 22且provider = xxx将被退回



有人可以根据上述条件构建一个SQL查询

请帮助

提前致谢



我尝试过：



如果clientid = isnull，提供者= isnull，status = isnull，timeinterval = isnull然后select * from tablename elseif clientid = notnull，provider = notnull，status = notnull，timeinterval = isnull then select clientid = @ clientid，provider = @ provider，status = @ provider



同样我还要写更多的组合有没有其他方法可以得到它？

解决方案

那甚至不是有效的SQL ...

一次做一个：

  IF   @ ClientID   IS   NULL  那么 
  BEGIN  
   -   检查其他人。 
  IF   @ Provider   IS   NULL   AND   @ Status   IS   NULL   AND   @ TimeInterval   IS   NULL  那么 
  SELECT  *  FROM  MyTable 
  ELSE  
 ... 
  END  
  ELSE  
  BEGIN  
  IF   @ ClientID  =  22  那么 
 ... 
  END < /跨度> 

say I have 4 parameters as input
clientid,provider,status and timeinterval
if all the parameters are null then I will return all the records from table
if clientid=22 and other parameters are null then records having clientid=22 will be returned
if client =22 and provider =xxx then records having clientid =22 and provider =xxx will be returned

Can someone frame a sql query with the above conditions
Please help
Thanks in advance

What I have tried:

if clientid=isnull, provider = isnull ,status=isnull ,timeinterval=isnull then select * from tablename elseif clientid=notnull,provider = notnull, status=notnull ,timeinterval=isnull then select clientid=@clientid,provider=@provider,status=@provider

likewise i have to write many more combinations is there any other way to get it ?

解决方案

That isn't even valid SQL...
Do them one at a time:

IF @ClientID IS NULL THEN
   BEGIN
   -- check the others.
   IF @Provider IS NULL AND @Status IS NULL AND @TimeInterval IS NULL THEN
      SELECT * FROM MyTable
   ELSE
      ...
   END
ELSE
   BEGIN
   IF @ClientID = 22 THEN
      ...
   END

这篇关于Sql：我有一个要求，我的所有输入参数都是可选的。所以我必须检查输入参数是null还是notnull的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！