Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Where Clause IN Statement Expand / Collapse
Author
Message
Posted Friday, November 26, 2010 12:08 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 62, Visits: 743
Hi Guys,
i am stuck in listing some of the data,
My issues is i am providing a list of the branchs or the Value from User interface
'''PLEXITECH'' , ''MOMBASA'' , ''WIPRO'''

the following code i have writen in sql i want to implement "Case 2"
Please suggest on it
DECLARE @Parameter varchar(max)
SET @Parameter = '''PLEXITECH'' , ''MOMBASA'' , ''WIPRO''';
-- Case 1
DECLARE @Query NVARCHAR(max)
SELECT @Query = N'SELECT * FROM M_BRANCH WHERE BRANCHNAME IN (' + @Parameter + ')'
EXEC sp_executesql @Query

-- Case 2 I Want to implement the following query how should i execute it, any suggestion regarding same
SELECT * FROM M_BRANCH WHERE BRANCHNAME IN ( @Parameter )


Patel Mohamad
Post #1026649
Posted Friday, November 26, 2010 2:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:49 AM
Points: 45, Visits: 138
Dear Frnd,

You can achieve ur requirement through the following way,


Declare @s varchar(100)
Set @s = 'Subbu,Subbiah'
select * from employee WHERE namess in ( SELECT value COLLATE DATABASE_DEFAULT FROM SplitList(@s , ',') )

First Declare the variable with the PARAMETERS u need
Then give the following line in WHERE CLAUSE

SELECT value COLLATE DATABASE_DEFAULT FROM SplitList(@s , ',')


SplitList is a function. I had given the funtion





Create FUNCTION [dbo].[SplitList]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN

While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End

Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))

Return
END




Please let me know, it helped u r nt
Post #1026691
Posted Friday, November 26, 2010 3:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 62, Visits: 743
Thanks subbusa2050

it works and implemented in my project.

Regards


Patel Mohamad
Post #1026707
Posted Friday, November 26, 2010 3:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:49 AM
Points: 45, Visits: 138
Hi Frnd,

Ya ok fine
Post #1026721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse