November 26, 2010 at 12:08 am
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
November 26, 2010 at 2:19 am
Dear Frnd,
You can achieve ur requirement through the following way,
Declare @s-2 varchar(100)
Set @s-2 = '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
November 26, 2010 at 3:09 am
Thanks subbusa2050
it works and implemented in my project.
Regards
Patel Mohamad
November 26, 2010 at 3:47 am
Hi Frnd,
Ya ok fine 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply