To pass string values to in operator in a select statement

hi all,

i have below query

--constants used in condition of source query

declare

@uk_legal_matter_org_unit__potential_e_o_3 nvarchar(20

)

declare

@uk_legal_matter_org_unit__potential_e_o_2 nvarchar(20

)

declare

@uk_legal_matter_org_unit__potential_e_o_1 nvarchar(20

)

declare

@uk_legal_matter_org_unit__us_retail_group nvarchar(50

)

 

set

@uk_legal_matter_org_unit__potential_e_o_3 =

'ameriprise > e&o coverage - lancer'

set

@uk_legal_matter_org_unit__potential_e_o_2 =

'ameriprise > e&o coverage'

set

@uk_legal_matter_org_unit__potential_e_o_1 =

'ameriprise > potential e&o'

set

@uk_legal_matter_org_unit__us_retail_group =

'ameriprise > retail group > advisor group'

--source query

select

text_legal_matter_source

.*

from

text_legal_matter_source

left

join legal_matter

on

text_legal_matter_source.[matter id] = legal_matter.matter_id

where

text_legal_matter_source.[matter id] = 999999

and

(text_legal_matter_source.[organizational unit] in

(

@uk_legal_matter_org_unit__potential_e_o_1

, 

@uk_legal_matter_org_unit__potential_e_o_2

, 

@uk_legal_matter_org_unit__potential_e_o_3

, 

@uk_legal_matter_org_unit__us_retail_group

)

or

legal_matter.matter_id is not null);

-----------------------------------------------------------------------

my problem when put string values directly in operator results displayed. if send through variables, no records selected

kindly in rephrasing query.

 

hi,

in experience works expected..

set nocount on;
use tempdb;
go
create table dbo.test (
 id int not null identity primary key,
 data varchar(10) not null
 );
go
insert dbo.test
 values ( 'a' ), ( 'b' ), ( 'c' ), ( 'd' ), ( 'e' ),
  ( 'f' ) , ( 'g' ), ( 'h' ), ( 'i' ), ( 'm' );
go
print 'actual parameters passed query..';
declare @p1 varchar(10), @p2 varchar(10), @p3 varchar(10), @p4 varchar(10);
select @p1 = 'a', @p2 = 'c', @p3 = 'e', @p4 = 'g';

print '1) using in operator restrict returned rows..';
select *
 from dbo.test t
 where t.data in ( @p1, @p2, @p3, @p4 );

print '2) using temp table populated valid parameters';
print '   perform actual join instead of using in operator';
print '   , allowing more parameters addressed..';
declare @t table (
 rif varchar(10)
 );
if @p1 not null insert @t values ( @p1 );
if @p2 not null insert @t values ( @p2 );
if @p3 not null insert @t values ( @p3 );
if @p4 not null insert @t values ( @p4 );
select *
 from dbo.test t
  join @t x on x.rif = t.data
 where t.id < 100;
go
drop table dbo.test;
--<-----------
actual parameters passed query..
1) using in operator restrict returned rows..
id          data
----------- ----------
1           a
3           c
5           e
7           g

2) using temp table populated valid parameters
   perform actual join instead of using in operator
   , allowing more parameters addressed..
id          data       rif
----------- ---------- ----------
1           a          a
3           c          c
5           e          e
7           g          g

regards

---

http://www.asql.biz - dbamgr2k - dbamgr , further sql tools http://www.hotelsole.com/

SQL Server  >  SQL Server Express