SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need topass twovalues in singlevariable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cursor join
Starting Member

8 Posts

Posted - 01/06/2013 :  10:43:30  Show Profile  Reply with Quote
HI,
i have created a sp. i need to pass two or three values while executing.
here is Sp..pls guide



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 in_city_type in( @vc_prm_city)
and(country_Master.Country_Name in (@vc_prm_country)




END

--exec [Get_EMP_Detailst] '15/NOV/2012','15/DEC/2012','Australia','perth'(EXCECUTING PROPERLY)
--exec [Get_EMP_Detailst] '15/NOV/2012','15/DEC/2012','Australia','sydney'((EXCECUTING PROPERLY))


I want these two results should come together when i execute my pROC as below

--exec [Get_EMP_Detailst] '15/NOV/2012','15/DEC/2012','Australia','perth,sydney'


I know this is not the proper syntax for the procedure.it will be helpful if the proper solution is provided using cursor or whatever

rgds..
cursor join

Cursor join
Starting Member

8 Posts

Posted - 01/06/2013 :  11:34:26  Show Profile  Reply with Quote
any guidance?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 01/06/2013 :  12:13:04  Show Profile  Visit jackv's Homepage  Reply with Quote
You'll need to split the array so the stored procedure can handle. There are plenty of examples of functions to manage this process.

check
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Edited by - jackv on 01/06/2013 12:15:41
Go to Top of Page

Cursor join
Starting Member

8 Posts

Posted - 01/06/2013 :  12:41:46  Show Profile  Reply with Quote
Thanks 4 de reply jack...i m totally confused with the links...
can u provide me a sample SP.

Thanks in Advance!!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 01/07/2013 :  01:11:06  Show Profile  Reply with Quote
hi first create a function to split the comma separated values and then access function with in your SP..

and in_city_type in(SELECT * FROM dbo.fnSplit(@vc_prm_city, ','))
and(country_Master.Country_Name in (SELECT * FROM dbo.fnSplit(@vc_prm_country, ','))

-- This is the code for fnSplit

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', ' ')


--
Chandu
Go to Top of Page

Cursor join
Starting Member

8 Posts

Posted - 01/07/2013 :  12:02:47  Show Profile  Reply with Quote
thanks a lot chandu..it works...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2013 :  23:43:03  Show Profile  Reply with Quote
if filtering dataset is small you could even use


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 


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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 01/07/2013 :  23:49:31  Show Profile  Reply with Quote
quote:
Originally posted by Cursor join

thanks a lot chandu..it works...


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000