Passing multiple value in the single parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Passing multiple value in the single parameter

Author

Topic

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-09 : 14:34:54

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)

17689 Posts

Posted - 2014-03-09 : 19:28:08

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

KH
[spoiler]Time is always against us[/spoiler]

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-09 : 19:33:04

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-09 : 19:49:26

can you show us your query ?

KH
[spoiler]Time is always against us[/spoiler]

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-09 : 20:05:04

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-09 : 20:23:11

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
[spoiler]Time is always against us[/spoiler]

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-09 : 20:41:50

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-09 : 21:17:11

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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources