Prev: Fill Factor on a clustered index
Next: Trying to setup Trigger to Update Each record
From: OriginalStealth on 19 Jan 2010 08:48
How do I write the syntax for a stored procedure that has one parameter but
accepts multiple values. I am running a report from reporting services that
can pass multiple values or pass all values to the stored procedure

stored procedure 1: exec getcolor @color = blue....this runs fine.
stored procedure 2: exec getcolors @colors in('red', 'white',
'blue').........bombs

code is

create procedure getcolors @colors nvarchar (50)
as
(select *
from hrs_by_activity
where color in(@colors)
)

need help making this work.
thanks in advance

From: Uri Dimant on 19 Jan 2010 09:02
OriginalStealth
Read Erland's articles about the subject
http://www.sommarskog.se/arrays-in-sql.html




"OriginalStealth" <OriginalStealth(a)discussions.microsoft.com> wrote in
message news:47CC6A22-888F-47EC-A755-27CC9E0BF3B2(a)microsoft.com...
> How do I write the syntax for a stored procedure that has one parameter
> but
> accepts multiple values. I am running a report from reporting services
> that
> can pass multiple values or pass all values to the stored procedure
>
> stored procedure 1: exec getcolor @color = blue....this runs fine.
> stored procedure 2: exec getcolors @colors in('red', 'white',
> 'blue').........bombs
>
> code is
>
> create procedure getcolors @colors nvarchar (50)
> as
> (select *
> from hrs_by_activity
> where color in(@colors)
> )
>
> need help making this work.
> thanks in advance
>


From: Plamen Ratchev on 19 Jan 2010 10:05
If you are using SQL Server 2008 then look at implementing table-valued parameters:
http://technet.microsoft.com/en-us/library/bb510489.aspx

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 19 Jan 2010 23:54
>> How do I write the syntax for a stored procedure that has one parameter but accepts multiple values. <<

Read a book on RDBMS. The Relational Model is based on scalar
values. Writing your own parser and error handling is a kludge (I
posted some of the first code for CSV lists in one statement; learn
from my folly).

Your procedure can take a parameter list that allows for 1 to (n)
colors and has a default value (best not to use a NULL) when one is
left out. Since the values are actual parameters, you will get all of
the T-SQL exception checking you would have to put into a homemade
parser.

Or decide that getting the right answers is not important, which is
what most Newbies do but don't know it.

Before you worry about performance with long parameter list, let
cut&paste an email from a friend, Richard Romley, who wrote a one-
select statement Sudoku solver. He passes the initial grid, cell by
cell.

Most of the time is in the PRINT statements. Many published Sudoku
puzzles have multiple answers (yes, I know they are not supposed to).
In Orlando at a SQL Saturday we did one puzzle where we kept removing
initial numbers to see how many solutions were created with relaxed
conditions; we stopped when we got back over 300 valid answers.

Assuming you are not working with a list of more than a few hundred
colors and a simple matching condition, you ought to consider the long
parameter list approach and stay in the Relational Model.

===== email =========
Since we last met in Orlando the only thing SQL related I have done is
install the 64 bit version of SQL Server 2008 on my new 64 bit HP i7
computer. It’s absolutely incredible how fast it runs, solving most
Sudoku puzzles in 10ms – 15ms. Pretty amazing (IMHO) when you realize
that the solution requires performing an 81 table join – not to
mention that horrible stored procedure interface with 81 input
parameters!







From: Michael Coles on 25 Jan 2010 09:04
Use Table-Valued Parameters or typed XML to get the same type safety and
exception checking without declaring hundreds/thousands of parameters on
your stored procedure.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:c0b93287-7414-4feb-932c-2555aebb8727(a)34g2000yqp.googlegroups.com...
>> How do I write the syntax for a stored procedure that has one parameter
>> but accepts multiple values. <<

Read a book on RDBMS. The Relational Model is based on scalar
values. Writing your own parser and error handling is a kludge (I
posted some of the first code for CSV lists in one statement; learn
from my folly).

Your procedure can take a parameter list that allows for 1 to (n)
colors and has a default value (best not to use a NULL) when one is
left out. Since the values are actual parameters, you will get all of
the T-SQL exception checking you would have to put into a homemade
parser.

Or decide that getting the right answers is not important, which is
what most Newbies do but don't know it.

Before you worry about performance with long parameter list, let
cut&paste an email from a friend, Richard Romley, who wrote a one-
select statement Sudoku solver. He passes the initial grid, cell by
cell.

Most of the time is in the PRINT statements. Many published Sudoku
puzzles have multiple answers (yes, I know they are not supposed to).
In Orlando at a SQL Saturday we did one puzzle where we kept removing
initial numbers to see how many solutions were created with relaxed
conditions; we stopped when we got back over 300 valid answers.

Assuming you are not working with a list of more than a few hundred
colors and a simple matching condition, you ought to consider the long
parameter list approach and stay in the Relational Model.

===== email =========
Since we last met in Orlando the only thing SQL related I have done is
install the 64 bit version of SQL Server 2008 on my new 64 bit HP i7
computer. It�s absolutely incredible how fast it runs, solving most
Sudoku puzzles in 10ms � 15ms. Pretty amazing (IMHO) when you realize
that the solution requires performing an 81 table join � not to
mention that horrible stored procedure interface with 81 input
parameters!







 | 
Pages: 1
Prev: Fill Factor on a clustered index
Next: Trying to setup Trigger to Update Each record