IF OBJECT_ID('[dbo].[fnSplit]') IS NOT NULL DROP FUNCTION [dbo].[fnSplit]
GO
CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1 22 333 444  5555 666', ' ')

create PROCEDURE [dbo].[Get_EMP_Details] 
( 
@dt_Prm_Fromdate datetime, 
@dt_Prm_Todate datetime, 
@vc_prm_country varchar(50), 
@vc_prm_city varchar(50) 
) 



AS 




BEGIN 
SET NOCOUNT ON; 




SELECT Name,city travelled 
FROM EMPloyee INNER JOIN 
city_Master cm ON 
EMPloyee.in_city_ID_pK = cm.in_city_ID_fK 
WHERE (dt_Travelled_Date >=@dt_Prm_Fromdate) 
and (dt_Returned_Date <=@dt_Prm_Todate) 
and ',' + @vc_prm_city + ',' LIKE '%,' + in_city_type + ',%' 
and(country_Master.Country_Name in (@vc_prm_country) 




END 

quote:

---

Originally posted by Cursor join

thanks a lot chandu..it works...

---