如何在条件SQL中处理一个强制参数和一个可选参数 [英] How to handle One mandatory and One optional parameter in where condition SQL
问题描述
大家好,
我在编写选择查询时遇到问题。
我写了一个存储过程需要两个arguements input1和input2,
其中input2可以为null。在我的存储过程中,我正在编写一个select querry(使用连接)以及应该满足以下条件的条件。
1.当input1和input2不为空时,给出结果(column1 = input1和column2 = input2)(结果应该同时匹配两个值)
2.Input1匹配且input2为空 - 返回结果匹配input1
3.Input1不匹配input2不为null,返回匹配输入2
请帮助我。
尝试使用where子句与
isnull(Column1,0)= 0
我没有尝试过查询,它可能会有效。
尝试
SELECT * FROM yourTable
WHERE Column1 = ISNULL( @ input1 ,Column1) AND Column2 = ISNULL( @ input2 ,Column2)
声明 @ val1 varchar ( 10 );
声明 @ val2 varchar ( 10 );
set @ val1 = ' s'的跨度>;
set @ val2 = ' ur'< /跨度>;
EASIEST WAY TO 获得结果 IS : -
选择 * 来自 table_Name 其中 col1 = isnull( @ val1 ,col1)和 col2 = isnull( @ val2 ,col2)
或
使用动态SQl代码,如
声明@ val1 varchar(10);
声明@ val2 varchar(10);
声明@dynaimcstring varchar(200) ;
声明@executestring nvarchar(max);
set @ val1 ='s'';
set @ val2 =''' ';
if(ltrim(rtrim(@ val1))=''''和ltrim(rtrim(@ val2))!='''')
开始
设置@dynaimcstring =''其中col2 =''''''+ @ val2 +''''''';
结束
if(ltr im(rtrim(@ val1))!=''''和ltrim(rtrim(@ val2))='''')
开始
set @ dynaimcstring = ''其中col1 =''''''+ @ val1 +''''''';
结束
if(ltrim(rtrim(@ val1)) !=''''和ltrim(rtrim(@ val2))!='''')
开始
设置@ dynaimcstring =''其中col1 ='' ''''+ @ val1 +''''''和'col2 =''''''+ @ val2 +''''''';
结束
如果(ltrim(rtrim(@ val1))=''''和ltrim(rtrim(@ val2))='''')
开始
set @ dynaimcstring ='''';
end
set @executestring =''select * from allot_Det''+ @dynaimcstring;
print @ str;
exec sp_executesql @str;
Hi All,
I am having a problem in writing a select query .
I have written a stored procedure which takes two arguements input1 and input2,
where input2 can be null.In my stored procedure I am writing a select querry (which uses joins )and where condition which should ullfill below requirement.
1.when input1 and input2 are not null ,give the result (column1=input1 and column2=input2) (Result should have both vaues matched)
2.Input1 is matched and input2 is null-Return results for matched for input1
3.Input1 is not matched input2 is not null,Return matched for input2
Please help me.
try and use the where clause with
isnull(Column1,0) = 0
I have not tried on query, it may work.
Try
SELECT * FROM yourTable WHERE Column1 = ISNULL(@input1, Column1) AND Column2 = ISNULL(@input2, Column2)
declare @val1 varchar(10) ; declare @val2 varchar(10) ; set @val1='s'; set @val2='ur'; EASIEST WAY TO GET THE RESULT IS :- select * from table_Name where col1=isnull(@val1,col1) and col2=isnull(@val2,col2)
or
use the dynamic SQl code like
declare @val1 varchar(10) ;
declare @val2 varchar(10) ;
declare @dynaimcstring varchar(200);
declare @executestring nvarchar(max);
set @val1 =''s'';
set @val2='''';
if( ltrim(rtrim(@val1))='''' and ltrim(rtrim(@val2))!='''')
begin
set @dynaimcstring=''where col2=''''''+@val2+'''''''';
end
if( ltrim(rtrim(@val1))!='''' and ltrim(rtrim(@val2))='''')
begin
set @dynaimcstring=''where col1=''''''+@val1+'''''''';
end
if( ltrim(rtrim(@val1))!='''' and ltrim(rtrim(@val2))!='''')
begin
set @dynaimcstring='' where col1=''''''+@val1+'''''' and col2=''''''+@val2+'''''''';
end
if( ltrim(rtrim(@val1))='''' and ltrim(rtrim(@val2))='''')
begin
set @dynaimcstring='''';
end
set @executestring=''select * from allot_Det ''+ @dynaimcstring;
print @str;
exec sp_executesql @str;
这篇关于如何在条件SQL中处理一个强制参数和一个可选参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!