sql可选参数 [英] sql optional parameter

问题描述

你好



我正在尝试写一个程序。





从table1中选择*



内连接表2
table1.col1 = table.col1上的


其中1 = 1

和table1.col2 = @ col2value

和table2.col3 = @ col3value





如果用户的@ col2value为null

i想从where子句中删除table1.col2 = @ col2value？



我怎么能做到这一点？



我试过（（@ col2value IS NULL）或（table1.col2 = @ col2value））



这似乎不起作用。

请帮助。

解决方案

尝试：

  SELECT  *  FROM 表1 t1 
  INNER   JOIN  table2 t2  ON  t1。 col1 = t2.col1 
  WHERE （ @ col2value   IS   NULL  或 t1.Col2 =  @ col2Value ）
  AND  t2.Col3 =  @ col3Value  

删除虚假关闭括号 - OriginalGriff [/ edit]

试试这个...

 选择 * 来自 table1 t1 内部 加入 table2 t2   t1.col1 = t2.col1 
 其中 t1。 col2 =  CASE   WHEN  ISNULL（ @ col2Val  ，' '）= ' ' 那么 t1.col2  ELSE   @ col2Val   END  
  AND  t2.col3 =  @ col3Val  

希望这会对你有所帮助。





干杯

如果解决方案1不起作用，还有一个解决方法..它会增加几行代码但是可以解决你的问题

  IF （ @ col2value     Null ）
  BEGIN  
  SELECT  *  FROM 表1 t1 
  INNER   JOIN  table2 t2  ON  t1.col1 = t2 .col1 
  WHERE  t2.Col3 =  @ col3Value  
  END  
  ELSE  
  BEGIN  
  SELECT  *  FROM 表1 t1 
  INNER   JOIN  table2 t2  ON  t1.col1 = t2.col1 
  WHERE  t1.Col2 =  @ col2Value  
  AND  t2.Col3 =  @ col3Value  
 
  END  

Hello

Im trying to write a procedure.


Select *
from table1
inner join table2
on table1.col1= table.col1
where 1=1
and table1.col2= @col2value
and table2.col3 =@col3value


if @col2value is null from the user
i want to drop that table1.col2= @col2value from the where clause?.

how can i accomplish this?.

I tried ((@col2value IS NULL) OR (table1.col2 = @col2value))

this doesnt seem to work.
please help.

解决方案

Try:

SELECT * FROM Table1 t1
INNER JOIN table2 t2 ON t1.col1= t2.col1
WHERE (@col2value IS NULL OR t1.Col2 = @col2Value)
  AND t2.Col3 = @col3Value

[edit]Removed spurious close bracket - OriginalGriff[/edit]

Hi,

Try this...

Select * from table1 t1 inner join table2 t2 on t1.col1=t2.col1
where t1.col2 = CASE WHEN ISNULL(@col2Val,'') ='' THEN t1.col2 ELSE @col2Val END
AND t2.col3 = @col3Val

Hope this will help you.


Cheers

There is a work around if solution 1 is not working.. It will add few more lines of code but might solve you issue

IF(@col2value Is Null)
	BEGIN
		SELECT * FROM Table1 t1
		INNER JOIN table2 t2 ON t1.col1= t2.col1
		WHERE t2.Col3 = @col3Value
	END
	ELSE
	BEGIN
		SELECT * FROM Table1 t1
		INNER JOIN table2 t2 ON t1.col1= t2.col1
		WHERE t1.Col2 = @col2Value
		AND t2.Col3 = @col3Value
	
	END

这篇关于sql可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！