Operator IN / array in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Operator IN / array in SQL

Author

Topic

eden02
Starting Member

5 Posts

Posted - 2011-10-01 : 20:39:26

Hi,

I need use operator IN in query, but field is array like ('100','200','300').
How can I use operator IN with parameter or user defined function..or what is wrong in this code:

declare @pom varchar(100)
declare @pom2 varchar(100)
declare @var1 varchar(100)
declare @var2 varchar(100)
declare @var3 varchar(100)
set @pom = (stuff((REPLACE('1000;1200;3080',';',''',''')), 1,0,''''))+''''
set @var1= stuff(@pom, 7,50,'')
set @var2= stuff(stuff(@pom,1,7,''),7,50,'')
set @var3= stuff(@pom,1,14,'')

select @pom2=SUM(mi.ANQTY)
from the_moveitem mi, the_move m
where m.ackey=mi.ackey and
(m.acdoctype =(@var1) or m.acdoctype =(@var2)or m.acdoctype =(@var3))

SELECT @pom2

HELP!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 01:59:03

you can use like below


declare @pom varchar(100)
declare @pom2 varchar(100)
set @pom='1000;1200;3080'

select @pom2=SUM(mi.ANQTY) 
from the_moveitem mi, the_move m 
where m.ackey=mi.ackey and
';' + @pom + ';'  LIKE  '%;' + m.acdoctype + ';%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eden02
Starting Member

5 Posts

Posted - 2011-10-02 : 10:54:01

Thank you for helping

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]

m_imran18
Starting Member

14 Posts

Posted - 2011-10-02 : 12:55:19

Hi,
Alternatively, you can use it like this.



declare @pom varchar(100)
declare @pom2 varchar(100)
SET @pom=REPLACE('1000;1200;3080',';',''',''')     

Declare @SQl as nvarchar(Max)

Set @SQl='select @pom2=SUM(mi.ANQTY) 
from the_moveitem mi, the_move m 
where m.ackey=mi.ackey and m.acdoctype  In (''' + @pom + ''')'

Exec sp_executesql @SQL ,N'@Pom2 int output',@Pom2 output 

Select @Pom2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 13:49:31

quote:
Originally posted by eden02

Thank you for helping

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources