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
 General SQL Server Forums
 New to SQL Server Programming
 Passing multiple value in the single parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLBoy14
Starting Member

USA
24 Posts

Posted - 03/09/2014 :  14:34:54  Show Profile  Reply with Quote
Hi all,

How do I pass the multiple value in the single parameter?
Here is the select simple select statement with two fields (ID and Map_Area):

SELECT *
FROM TestParamOne

Here is the output:
ID Map_Are
12345 KK45
657463 IIY7
34345 FGD
44342 DFRE
4646 DSAW
424245 DSAW
12121 DSAW
5753 FRDE
575737 FRDE
1121 FRDE
1121 F5FR
646462 F5FR
8568 F5FR

Here is my simple stored proc. Right know I only can execute with one value at the time but I am trying to run the stored proc with more than one value.

CREATE PROCEDURE TestParam1
@Map varchar(4)
AS
BEGIN
SET NOCOUNT ON;

SELECT ID, Map_Area
FROM TestParamOne
WHERE Map_Area=@Map
END
GO


Anyone can help me?



SQLBoy

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 03/09/2014 :  19:28:08  Show Profile  Reply with Quote
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm


KH
Time is always against us

Go to Top of Page

SQLBoy14
Starting Member

USA
24 Posts

Posted - 03/09/2014 :  19:33:04  Show Profile  Reply with Quote
Hi Khtan, I try the method # 1 and it does not return the correct data. I put two values of Map Area and it return nothing. Any idea?

SQLBoy
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 03/09/2014 :  19:49:26  Show Profile  Reply with Quote
can you show us your query ?


KH
Time is always against us

Go to Top of Page

SQLBoy14
Starting Member

USA
24 Posts

Posted - 03/09/2014 :  20:05:04  Show Profile  Reply with Quote
This is the simple stored proc I did. Thank you Khtan.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE TestParam1
(
@Map varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @sql Varchar(600)

SET @sql=
'SELECT ID, Map_Area
FROM TestParamOne
WHERE Map_Area IN ('+@Map+')'

Exec(@SQL)


END
GO

SQLBoy
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 03/09/2014 :  20:23:11  Show Profile  Reply with Quote
do a PRINT @sql before exec(@sql) and you will see the issue.

in the example OrderID from Orders table in Northwind is an integer. Your map_area is string. If you pass in @Map = 'KK45,IIY7' the form statement will be like
WHERE Map_Area IN (KK45, IIY7)


so you will need to pass in the value as
@Map = '''KK45'',''IIY7'''



KH
Time is always against us

Go to Top of Page

SQLBoy14
Starting Member

USA
24 Posts

Posted - 03/09/2014 :  20:41:50  Show Profile  Reply with Quote
Hi Khtan,

That is correct. The data type is varchar and you are correct, I would need to insert in the where clause. Now, let's say if you have 100 or 500 different values, how would you handle that?

SQLBoy
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 03/09/2014 :  21:17:11  Show Profile  Reply with Quote
your calling application would need to format that value accordingly.

there are also other method describe in the link, you can also explore it


KH
Time is always against us


Edited by - khtan on 03/09/2014 21:18:16
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.09 seconds. Powered By: Snitz Forums 2000