How to Pass Multiple Strings as one parameter in Store Procedure

Question

Post reply

How to Pass Multiple Strings as one parameter in Store Procedure

Sangeeth Raj

Hall of Fame

Points: 3541
More actions
September 22, 2016 at 3:24 am

#329961

Hi,
I am trying to create store procedure, which has requirement to pass multiple Strings as input for Store Proc, Could someone help me how to do this.
Example:
Create Store Procedure FindingCityNames @CountryNames varchar(200)
as
select CityName,Capital, Countryname
from TableCountries
where
CountryName in
(
@CountryNames)
-- If I pass One country Name, I am able to see the Output, but If I want to pass like 'UK', 'USA', 'NZ', then its not accepting and returning error as "Procedure or function dd has too many arguments specified."
Please help me how to handle this.
Many Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

What you need here is a table valued parameter:

CREATE TYPE ParamCountry AS TABLE (name varchar(200));

GO

CREATE PROCEDURE FindingCityNames @CountryNames ParamCountry READONLY

AS

SELECT CityName,Capital, Countryname

FROM TableCountries

WHERE CountryName IN (

SELECT name

FROM @CountryNames

)

GO

DECLARE @names AS ParamCountry;

INSERT INTO @names VALUES ('UK'), ('USA'), ('NZ');

EXEC FindingCityNames @names;

GO

-- Gianluca Sartori

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 2

It's easy enough to break up an input string with a string splitter...

DECLARE @States VARCHAR(255) = 'NY,NJ,FL,OK,OH,MD';

SELECT

s.StateAbbr

FROM

dbo.LK_States s

JOIN dbo.DelimitedSplit8k(@States, ',') ds

ON s.StateAbbr = ds.Item;

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You've got two great options here. If by any way you get an option of using dynamic sql, please avoid it unless you plan to go unemployed.

The splitter that Jason used can be found here along with its explanation: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Just for what it's worth, we used to use the string splitter approach until the developers finally bit the bullet and learned to use table valued parameters. They are really a lot more efficient, especially when you're splitting input values just to populate a work table.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

The Dixie Flatline (9/23/2016)
Just for what it's worth, we used to use the string splitter approach until the developers finally bit the bullet and learned to use table valued parameters. They are really a lot more efficient, especially when you're splitting input values just to populate a work table.

Agreed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)