I have two different column to check the condition and return the result based on the conditon. The values may one value or more than one values.
So I better choose Union All condition to get the result for the both the columns accordingly.
The column value in the below format will be passed from fr/end page.
//For multi values
@EmpName='Alex,John,Edwards' //for single value @EmpName='Alex' @City='City1,City2,City3' //for single value @City='City1'
The following code, i used to make the query in order to meet multi value selection.
SET @tempCity='''' + REPLACE(@City,',',''',''')+ '''';
And,Finally uses the below statement to get the result.
SELECT * FROM EMP WHERE EmpName IN (@EmpName)
UNION ALL
SELECT * FROM EMP WHERE City IN (@City)
But, the problem is if am using the single value condition then no issue,will get the result as expected.
Otherwise,If I am trying to pass more than one value condition I am fails to get the result.No record returns
Then, do like this. There is no need of of "UNION ALL", the same can be achieved with a single table scan
SET @EmpName='Alex,John,Edwards'
SET @City='City1,City2,City3'
SELECT *
FROM EMP
WHERE (EmpName IN (select val from dbo.split(@EmpName,','))
OR City IN (select val from dbo.split(@City,',')))
If you want to match value in any of the column, either City or EmpName, 'OR' will definitely work.... If you want something different please elaborate more clearly...
declare @City varchar(max)
set @City='City1,City2,City3'
SET @City='''' + REPLACE(@City,',',''',''')+ '''';
declare @EmpName varchar(max)
set @EmpName='Name1,Name2,Name3'
SET @EmpName='''' + REPLACE(@EmpName,',',''',''')+ '''';
create table #emp ( id int identity(1,1),EmpName varchar(10),City varchar(10))
insert into #emp
select 'Name1','City1' union all
select 'Name2','City5' union all
select 'Name3','City3' union all
select 'Name4','City2' union all
select 'Name5','City4'
declare @string varchar(1000)
set @string = '
SELECT * FROM #EMP WHERE EmpName IN ('+@EmpName+')
UNION
SELECT * FROM #EMP WHERE City IN ('+@City+')'
print @string
exec(@string)
drop table #emp
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
In such a case if I am not having the value for any of input name or city. I also want to return the blank data by mentioning its search name or city input as field name.
Ex: If I am giving @Empname=name1,name2,name3
In that case name1 and name3 have the records and the name2 has no record in table. In such a case I just want to return the following way,
Name City Occupation
name1 CA Manager
name2 // I just want to mention name2 with blank data as a information to the user.
You can use Split function to store your @Empname in a table, then use left join to get what you want.
declare @City varchar(max)
set @City='City1,City2,City3'
--SET @City='''' + REPLACE(@City,',',''',''')+ '''';
declare @EmpName varchar(max)
set @EmpName='Name1,Name2,Name6'
--SET @EmpName='''' + REPLACE(@EmpName,',',''',''')+ '''';
select * from dbo.Split(@EmpName,',')
create table #emp ( id int identity(1,1),EmpName varchar(10),City varchar(10))
insert into #emp
select 'Name1','City1' union all
select 'Name2','City5' union all
select 'Name3','City3' union all
select 'Name4','City2' union all
select 'Name5','City4'
select items as Name, City from (select * from dbo.Split(@EmpName,',')) as value
left join #emp on value.items = #emp.EmpName
drop table #emp
--dbo.Split
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 12/20/2012 17:24:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (id int identity (1,1), items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Best Regards,
Please mark the replies as answers if they help or unmark if not.
Feedback to us
John Federer
Member
193 Points
312 Posts
How to get the result from multi values parameter in 'IN' clause?
Nov 28, 2012 05:43 AM|LINK
Hi,
I have two different column to check the condition and return the result based on the conditon. The values may one value or more than one values.
So I better choose Union All condition to get the result for the both the columns accordingly.
The column value in the below format will be passed from fr/end page.
//For multi values
@EmpName='Alex,John,Edwards' //for single value @EmpName='Alex'
@City='City1,City2,City3' //for single value @City='City1'
The following code, i used to make the query in order to meet multi value selection.
SET @tempCity='''' + REPLACE(@City,',',''',''')+ '''';
And,Finally uses the below statement to get the result.
SELECT * FROM EMP WHERE EmpName IN (@EmpName)
UNION ALL
SELECT * FROM EMP WHERE City IN (@City)
But, the problem is if am using the single value condition then no issue,will get the result as expected.
Otherwise,If I am trying to pass more than one value condition I am fails to get the result.No record returns
-Thanks
CruzerB
Contributor
5399 Points
1098 Posts
Re: How to get the result from multi values parameter in 'IN' clause?
Nov 28, 2012 05:49 AM|LINK
Hi,
Select * From EMP where City in (@City1, @City2, @City3.... )
You gonna construct these parameters in a loop and also during the value assignment to the SQLParameters object.
My Technical Blog
sandeepmitta...
Contributor
6803 Points
1060 Posts
Re: How to get the result from multi values parameter in 'IN' clause?
Nov 28, 2012 06:01 AM|LINK
First create a split function
Refer this link for split function -> http://itdeveloperzone.blogspot.in/2012/07/split-function-in-sql.html
Then, do like this. There is no need of of "UNION ALL", the same can be achieved with a single table scan
Sandeep Mittal | My Blog - IT Developer Zone
John Federer
Member
193 Points
312 Posts
Re: How to get the result from multi values parameter in 'IN' clause?
Nov 29, 2012 07:25 AM|LINK
Hi Sandeep,
Some times I would like to get the records from both columns City and EmpName, such a case 'OR' operator may not work.
What might be the other way to achieve the same?
-Thanks
sandeepmitta...
Contributor
6803 Points
1060 Posts
Re: How to get the result from multi values parameter in 'IN' clause?
Nov 29, 2012 08:44 AM|LINK
If you want to match value in any of the column, either City or EmpName, 'OR' will definitely work.... If you want something different please elaborate more clearly...
Sandeep Mittal | My Blog - IT Developer Zone
Chen Yu - MS...
All-Star
21600 Points
2493 Posts
Microsoft
Re: How to get the result from multi values parameter in 'IN' clause?
Nov 29, 2012 09:02 AM|LINK
Hi,
Please check below query, it works fine.
declare @City varchar(max) set @City='City1,City2,City3' SET @City='''' + REPLACE(@City,',',''',''')+ ''''; declare @EmpName varchar(max) set @EmpName='Name1,Name2,Name3' SET @EmpName='''' + REPLACE(@EmpName,',',''',''')+ ''''; create table #emp ( id int identity(1,1),EmpName varchar(10),City varchar(10)) insert into #emp select 'Name1','City1' union all select 'Name2','City5' union all select 'Name3','City3' union all select 'Name4','City2' union all select 'Name5','City4' declare @string varchar(1000) set @string = ' SELECT * FROM #EMP WHERE EmpName IN ('+@EmpName+') UNION SELECT * FROM #EMP WHERE City IN ('+@City+')' print @string exec(@string) drop table #empThanks.
Feedback to us
Develop and promote your apps in Windows Store
John Federer
Member
193 Points
312 Posts
Re: How to get the result from multi values parameter in 'IN' clause?
Dec 20, 2012 06:42 AM|LINK
Hi Chen Yu - MSFT,
In such a case if I am not having the value for any of input name or city. I also want to return the blank data by mentioning its search name or city input as field name.
Ex: If I am giving @Empname=name1,name2,name3
In that case name1 and name3 have the records and the name2 has no record in table. In such a case I just want to return the following way,
Name City Occupation
name1 CA Manager
name2 // I just want to mention name2 with blank data as a information to the user.
name3 CU Accountant
How it is possible?
Chen Yu - MS...
All-Star
21600 Points
2493 Posts
Microsoft
Re: How to get the result from multi values parameter in 'IN' clause?
Dec 20, 2012 08:25 AM|LINK
Hi,
You can use Split function to store your @Empname in a table, then use left join to get what you want.
declare @City varchar(max) set @City='City1,City2,City3' --SET @City='''' + REPLACE(@City,',',''',''')+ ''''; declare @EmpName varchar(max) set @EmpName='Name1,Name2,Name6' --SET @EmpName='''' + REPLACE(@EmpName,',',''',''')+ ''''; select * from dbo.Split(@EmpName,',') create table #emp ( id int identity(1,1),EmpName varchar(10),City varchar(10)) insert into #emp select 'Name1','City1' union all select 'Name2','City5' union all select 'Name3','City3' union all select 'Name4','City2' union all select 'Name5','City4' select items as Name, City from (select * from dbo.Split(@EmpName,',')) as value left join #emp on value.items = #emp.EmpName drop table #emp --dbo.Split /****** Object: UserDefinedFunction [dbo].[Split] Script Date: 12/20/2012 17:24:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (id int identity (1,1), items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return endBest Regards,
Feedback to us
Develop and promote your apps in Windows Store