Pass a variable number of parameters to a stored procedure

[Curious]

I have a stored procedure, spAccountDetails, that needs to take an
unknown number of parameters. For example, it should be able to run
either of the following:

EXEC spAccountDetails 'DLCC_GOOG', 'Google', 'DCMD_YHOO', 'Yahoo'

or

EXEC spAccountDetails 'DLCC_GOOG', 'Google', 'DCMD_YHOO', 'Yahoo',
'DMID_NGRD', 'National Grid'

I want to pass any number of pairs of strings. In each pair, the first
string is the AccountName in the database table, bl. The second string
in each pair is display name for the account. I want to get data
according to AccountName in table bl, but replace AccountName with
display name I supply.

What would be syntax for these parameters in creating the stored
procedure (in the spirit of the following)?

CREATE PROCEDURE [dbo].[spAccountDetails]
@AccountNameAndDisplayNamePairs AS Array[text]

Also how to extract each pair and get its AccountName and display
name?

Thanks!

[TheSQLGuru]

Here is pretty much the definitive article on this topic:
www.sommarskog.se/arrays-in-sql.html

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"Curious" <fir5t...@yahoo.com> wrote in message
news:a18730f4-813b-465d...@34g2000hsf.googlegroups.com...

[Pla...@sqlstudio.com]

Erland Sommarskog has a great article on the topic with different
methods to accomplish this:
http://www.sommarskog.se/arrays-in-sql-2005.html

Plamen Ratchev
http://www.SQLStudio.com

[Aaron Bertrand [SQL Server MVP]]

Typically you pass in a comma-separated list, since you cannot have an
unknown number of parameters to a stored procedure, and there is no such
thing as an array. SQL Server 2008 will provide table-valued parameters
which is close, but you still need to populate them somehow if the strings
are coming from a GUI. There are many ways to skin a cat of course; you
could use XML here too. But my personal preference is a string splitting
function, which I have in some form in just about every system I manage.

In this case you would probably pass the string like this:

EXEC dbo.proc_name 'DLCC_GOOG,Google|DMCD_YHOO,Yahoo|DMID_NGRD,National
Grid';

The key is choosing two delimiters that won't naturally occur in your data.
Pipes are usually safe; commas are not as safe, but they are still one of
the more popular choices.

Then, you can create a split function that loops through the string and
returns a table with the set of pairs. The following supports delimiters up
to 3 characters; it does not check if the delimiters are the same (which
would break the function obviously), there is also no check that each pair
has valid delimiters (which also breaks the function). It does allow you to
optionally prevent duplicates and also allows you to maintain the order in
which the original list was constructed.

CREATE FUNCTION [dbo].[SplitStringPairs]
(
@List NVARCHAR(MAX),
@MajorDelimiter VARCHAR(3) = '|',
@MinorDelimiter VARCHAR(3) = ',',
@AllowDuplicates BIT = 0
)
RETURNS @Items TABLE
(
Position INT IDENTITY(1,1) NOT NULL,
LeftItem NVARCHAR(MAX) NOT NULL,
RightItem NVARCHAR(MAX) NOT NULL
)
AS
BEGIN
DECLARE
@Item NVARCHAR(MAX),
@LeftItem NVARCHAR(MAX),
@RightItem NVARCHAR(MAX),
@Pos INT;

SELECT
@List = @List + ' ',
@MajorDelimiter = LTRIM(RTRIM(@MajorDelimiter)),
@MinorDelimiter = LTRIM(RTRIM(@MinorDelimiter));

WHILE DATALENGTH(@List)>0
BEGIN
SET @Pos = CHARINDEX(@MajorDelimiter,@List);

IF @Pos = 0
SET @Pos = DATALENGTH(@List) + LEN(@MajorDelimiter);

SELECT
@Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))),
@LeftItem = LTRIM(RTRIM(LEFT(@Item,
CHARINDEX(@MinorDelimiter, @Item) - 1))),
@RightItem = LTRIM(RTRIM(SUBSTRING(@Item,
CHARINDEX(@MinorDelimiter, @Item)
+ LEN(@MinorDelimiter), LEN(@Item))));

IF @AllowDuplicates = 1 OR
(
@AllowDuplicates = 0 AND NOT EXISTS
(SELECT 1 FROM @Items
WHERE LeftItem = @LeftItem
AND RightItem = @RightItem)
)
INSERT @Items(LeftItem, RightItem)
SELECT @LeftItem, @RightItem;

SET @List = SUBSTRING(@List,
@Pos + LEN(@MajorDelimiter), DATALENGTH(@List));
END
RETURN
END
GO

So, given the above procedure call, you would have

CREATE PROCEDURE dbo.proc_name
@List NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;

SELECT [old value in table] = t.LeftItem,
[new value to replace old value] = t.RightItem,
bl.*
FROM
bl
INNER JOIN dbo.SplitStringPairs(@List, '|', ',', 0) AS t
ON b.AccountName = t.LeftItem;

-- when you've validated this works correctly,
-- delete the above and uncomment the below:
/*
UPDATE b
SET AccountName = t.RightItem
FROM bl AS b
INNER JOIN dbo.SplitStringPairs(@List, '|', ',', 0) AS t
ON b.AccountName = t.LeftItem;
*/
END
GO

Validating that @List is provided in the correct format is something I will
leave up to you. Also I would test this against a dummy copy of the table
to make sure the effect is what you want...

On 8/2/08 8:08 AM, in article
a18730f4-813b-465d...@34g2000hsf.googlegroups.com, "Curious"

[Aaron Bertrand [SQL Server MVP]]

BTW, the code I wrote assumes SQL Server 20065. It is always useful to
provide version information to us so we don't have to guess or assume, and
potentially do a bunch of free work for nothing...

On 8/2/08 8:08 AM, in article
a18730f4-813b-465d...@34g2000hsf.googlegroups.com, "Curious"
<fir5t...@yahoo.com> wrote:

[Aaron Bertrand [SQL Server MVP]]

> BTW, the code I wrote assumes SQL Server 20065.

Sorry, just came back from the future. You people in the stone ages of
course should interpret that as SQL Server 2005, and pretend you never saw
my earlier post. :-)

[Curious]

Hi Aaron,

> You people in the stone ages of course should interpret that as SQL Server 2005

Yes, I use SQL server 2005. Sorry for not including this info in my
original post.

[Curious]

Hi Aaron,

Thanks for the sample code! This is very useful.

FYI, both '|' and ',' are safe separators for our purpose, because we
don't use either in AccountName or display name.

[Curious]

> Erland Sommarskog has a great article on the topic with different
> methods to accomplish this:http://www.sommarskog.se/arrays-in-sql-2005.html

Thanks for providing the reference! I'll take a look at this article.

[Curious]

Hi Kevin,

I'll take my time to take a look at this article.

[Curious]

Hi Aaron,

Your function and procedure works very well! Thanks! I only have one
question for you:

In the following statement, why would you add a space, ' ', to @List?

[steve dassin]

"Aaron Bertrand [SQL Server MVP]" <ten...@dnartreb.noraa> wrote in message
news:C4B9E8F7.E45B%ten...@dnartreb.noraa...
>.

>But my personal preference is a string splitting function, which I have in
>some form in just about every system I manage.

>.

I bet a poll would show this as a best practice. A poll would probably also
show that there is a trend to move the server to 64 bits. So you have the
spectacle of using yesterdays method on tomorrows system. Does anybody find
this a bit crazy? And so much for the link between best practice and
progress. Application development with sql remains an elusive target.
Something about the stone age? :)

www.beyondsql.blogspot.com

[Aaron Bertrand [SQL Server MVP]]

That is just to protect you from a single element list. If you have this:

SET @List = 'foo,bar'

When you later get the position of the first delimiter (since it doesn't
exist):

SET @Pos = DATALENGTH(@List) + LEN(@MajorDelimiter);

This spot will be one past the length of the string, which can break
calculations further into the function (I think it would be invalid length
passed to SUBSTRING()). Come to think of it, it should probably be:

SELECT
@List = @List + SPACE(LEN(@MajorDelimiter)),

I probably only tested that edge case with pipes and commas, but not
multi-character delimiters (which are sometimes used to reduce the chance of
collisions with real data).

On 8/2/08 11:43 PM, in article
44293343-1cdf-4b62...@56g2000hsm.googlegroups.com, "Curious"

[Aaron Bertrand [SQL Server MVP]]

Well we all know what you would do if you were in charge of the SQL engine.
In the meantime, do you have a better solution for those of us that are
using the product and need to deal with the lack of an array data type? We
can't all just switch to Dataphor (!) or to a different RDBMS to overcome
this limitation. Some of us have to work in the real world where everything
we do is fun and experimental. I think your criticisms are aimed in the
wrong direction (or perhaps you have a hard time preventing your comments
from being perceived as criticism).

On 8/3/08 3:35 PM, in article u6OpMAa9...@TK2MSFTNGP03.phx.gbl, "steve

[steve dassin]

"Aaron Bertrand [SQL Server MVP]" <ten...@dnartreb.noraa> wrote in message

news:C4BBBF83.E557%ten...@dnartreb.noraa...

> Well we all know what you would do if you were in charge of the SQL
> engine.
> In the meantime, do you have a better solution for those of us that are
> using the product and need to deal with the lack of an array data type?
> We
> can't all just switch to Dataphor (!) or to a different RDBMS to overcome
> this limitation. Some of us have to work in the real world where
> everything
> we do is fun and experimental.

Just how long a time is 'in the meantime' because everything done there is
really a waste of time. If the best and brighest don't show they
are that who or what fills the void? A better solution lies not only in
engineering but in attitude. Requests beget in the meantimes. It's
just as much your industry as it's MSs. You can demand. If you demand others
will. You shouldn't have to look elsewhere. Those that don't know can be
forgiven the error of omission. But for those that do know it's much harder
to swallow an error of commission. Dataphor isn't necessarily an answer for
you. But it's a damn good 'what if'. The MS sql community is burying its
head in the sand while a greek play is going on there. But it needn't be a
tragedy for those who believe in a rdbms. It's time. You know the things
that suck. You and your colleagues fish and end all the meantime junk or
continue cutting bait. For a few such people this may be a risk,
but when many it becomes a voice. Clear your throat already, you are the
best solution.

>..perhaps you have a hard time preventing your comments

>from being perceived as criticism

There are some great programmers here that come up with brillant
workarounds. Sometimes the accolades obscure the question of why such brain
power was necessary in the first place. There is usually a downside to every
upside. And every industry needs its downers. :)

www.beyondsql.blogspot.com

[Aaron Bertrand [SQL Server MVP]]

> Clear your throat already, you are the best solution.

Yeah, thanks for the advice. I've filed over 500 bugs and suggestions
against SQL Server (see the bottom right lists at
http://connect.microsoft.com/sqlserver), and that is just in the current
feedback system; other systems pre-dated it and the feedback there did not
carry over. So I think I am doing my part.

Personally, the lack of an array data type is not a high priority to me.
And given all the other strengths of the system, this is not a blocking
issue for me, or one for me to be up in arms about. If you think this is
such a critical shortcoming in the product, why don't YOU clear YOUR throat
in a venue where they will actually see your comments, instead of soapboxing
here, where they won't?

[--CELKO--]

>> I want to pass any number of pairs of strings. In each pair, the first string is the account_name in the database table, BL. The second string in each pair is display name for the account. I want to get data according to account_name in table BL, but replace account_name with display name I supply. <<

When you write your own parser, you lose the error messages that an SP
gives you for a parameter error. You will need to provide that code
and the appropriate RAISERROR() calls -- or decided that data
integrity is not required.

You also need to handle duplicate values for both the account_name and
display_account_name: ('DCDD-IBM" , 'IBM'), ('DCDD-IBM" ,
'International Business Machines'), etc. Then there is the problem
of commas, unbalanced quotes and missing data in the string. And what
about allowing NULLs or defaults?

My advice is to either load a working table that has some constraints
and scrub the data inside it before doing the update, or to use a long
list of parameters. I wrote one of the first "parser in query
statements" and I regret that kludge.

[steve dassin]

"Aaron Bertrand [SQL Server MVP]" <ten...@dnartreb.noraa> wrote in message

news:e7jsS3i9...@TK2MSFTNGP02.phx.gbl...

>> Clear your throat already, you are the best solution.
>
> Yeah, thanks for the advice. I've filed over 500 bugs and suggestions
> against SQL Server (see the bottom right lists at
> http://connect.microsoft.com/sqlserver), and that is just in the current
> feedback system; other systems pre-dated it and the feedback there did not
> carry over. So I think I am doing my part.

'Achieve Flexible Data Modeling With The Entity Framework'
by Elisa Flasko
Elisa Flasko is a Program Manager in the Data Programmability team at
Microsoft,
focusing on ADO.NET, XML, and SQL Server Connectivity technologies.
http://msdn.microsoft.com/en-us/magazine/cc700331.aspx

"So why was another model needed? Well, as the amount of data companies were
working with increased, it became very difficult to understand and develop
applications on top of that data. Database schemas are designed with
storage concerns, such as data integrity,
performance, and management, in mind and are not always easy to understand.
Often these schemas also do not align with the structure of your
application, making development and maintenance even more complex. Custom
solutions that separated the structure of data from the application being
built were common. Unfortunately, with the number of custom solutions,
various approaches, and steps required to model data all being different for
each application, the problem continued to grow. There was a consistent need
across the industry for a way to define and develop against an
application-level domain model that can be clearly separated from the
logical model of the store. Enter the Entity Framework. The EDM (see the
sample illustrated in Figure 1) allows the definition of a domain model
that is consistent with the way an organization thinks about and uses its
data, rather than the way that data is stored."

"The EDM was also developed with the primary goal of becoming the core data
model across a suite of developer and server technologies within Microsoft."

"Thus was born Entity SQL (ESQL), a new SQL dialect that adds the ability to
query based on the concepts that are not supported in previous SQL dialects.
ESQL extends the existing SQL language in much the same way as the EDM
extends the Relational model used in databases."

Can you understand when someone is raining on your parade because this is
what MS is doing. The is a point of view and a statement of 'direction'.
Feedback superstars and top validators deserve all the credit in the world.
But do all their efforts add up to a direction? Do all the brilliant puzzles
from Ben-Gan draw a picture of the path sql and a relational orientation
should travel to help bring it within the grasp of contemporary application
developers? Does the sql community offer a picture of an alternative to that
being followed by MS? Or are we simply talking about the inertia of the past
with tweaks, some major some minor. But the same inertia nonetheless. Me, I
like to draw, hold the picture up and put it where....well it's hard to
miss:)

>
> Personally, the lack of an array data type is not a high priority to me.
> And given all the other strengths of the system, this is not a blocking
> issue for me, or one for me to be up in arms about. If you think this is
> such a critical shortcoming in the product, why don't YOU clear YOUR
> throat in a venue where they will actually see your comments, instead of
> soapboxing here, where they won't?

The lack of a list type type may be nothing to you but it's a necessity to
application developers. But I'm going way beyond it. I hope you would too.
As for appropriate venue, I'm not on the A-list of invitees:) Of course if
you want to put in a good word for me at sqlblog that's kewl:) I'm always
open to good suggestions. I got plenty of lozenges. And what makes you think
that it's most important that 'they' see my comments? What are you, chopped
liver? :)

www.beyondsql.blogspot.com

[Aaron Bertrand [SQL Server MVP]]

> The lack of a list type type may be nothing to you but it's a necessity to
> application developers.

If there are workarounds, how is it a necessity? A car that runs on oxygen
might be considered a necessity too, by your definition. In the meantime, I
will continue to buy gas because I don't have much choice... I still need to
get between A and B, and I have options for doing so, even if they're not
the most ideal options.

> As for appropriate venue, I'm not on the A-list of invitees:)

You don't need an invitation to file feedback at connect.microsoft.com/sql
... and the interaction with the product team that comes out of it is often
better than you would expect.

A