You must Sign In to post a response.
  • Category: SQL Server

    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

    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.

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

    Blog :

  • #766009
    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*"');

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

  • #767587
    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

    select 'i am inside condition'

    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*/

Sign In to post your comments