Category: SQL Server

16 May 2016 23:59

Joined:	20/10/2011
Level:	Bronze
Points:	87

Points:	5

How to split front end passing variable value in sql

hi below query iam passing @test =test1,test2,test3 when i pass test1 only that if condition working fine when i pass more than one value that condition not working fine can any one help me how to split and how to check if condition

if @Test not in (SELECT company_code FROM RC_Employee_Company_Mapping WHERE employee_number=@Empid)

insert into RC_Company_Mapping_Audit_Details(Emp_ID,Company_Code_Access,Createdby_Userid,Audit_Timestamp,Audit_Action)values(@Empid,@string,@Login_ID,GETDATE(),'P')

#765981

18 May 2016 00:35

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	3

Hi,

You mean that you are looking for Full text search what are all the text you are passing by comma separator you want to search the hole string in the specified table right.

The requirement we are called as "FullText" search, this is the sample link for your search, I already prepare some sample for the same, please go through the below link for more details.
http://www.dotnetspider.com/resources/45401-Creating-FullText-Index-SQL-Server.aspx

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#766009

18 May 2016 23:02

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

so basically You need check multiple variable at a single time in IN sql right ? I think it is not possible I guess
you need to check it with AND condition


Select * from table where 
  columnname like'%David%' and 
  columnname like '%Moses%' and columnname like'%Robi%'

In SQL Server 2005+ with Full-Text indexing switched on, I'd do the following:


SELECT *
  FROM T
 WHERE CONTAINS(C, '"David" OR "Robi" OR "Moses"');

If you wanted your search to bring back results where the result is prefixed with David, Robi or Moses you could do:


SELECT *
  FROM T
 WHERE CONTAINS(C, '"David*" OR "Robi*" OR "Moses*"');

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#767587

21 Aug 2016 04:05

Joined:	23/12/2008
Level:	Gold
Points:	1765

Points:	4

there are two possible way to achieve same in sql server first way is to split your values after comma and make them table/ array and then use that table

as shown in below example

declare @test as varchar(100)
set @test = 'a,b,c,d';--assign your value here
with tbl_for_csv as
(
select left(@test, charindex(',',@test) - 1) as val,
stuff(@test +',',1,charindex(',',@test),'') as col
union all
select cast(left(col, charindex(',',col) - 1) as varchar(100)),
stuff(col,1,charindex(',',col),'') from tbl_for_csv
where col <> ''
)

select * into #test from tbl_for_csv
IF (select count(*) from #test where val not in (SELECT company_code FROM RC_Employee_Company_Mapping WHERE employee_number=@Empid)) = 0

begin
select 'i am inside condition'
end

--
second option is what we say dynamic sql which for your case doesnt look like better solution.

hope this will help you

Many Thanks
Tejinder Singh Barnala
/*I have the simplest tastes. I am always satisfied with the best*/

How to split front end passing variable value in sql

About dotnetspider.com

Quick Links

General

Services