Hi All

How can i  create a stored procedure which can pass a parameter as a list.
Below is sample sp:

create procedure spxx (@@param1 varchar(100))
as
select * from tb1 where col1 = @@param1

When executing i like would like to be able to do this:
1. exec spxx 'John'
0r
2. exec spxx 'John', 'Mary', 'Susan'

Thank you in advance.

http://www.aspfaq.com/2248



Show quote Hide quote

You pass it as a string and split it inside the Stored Proc.

Check this out...

http://www.sommarskog.se/arrays-in-sql.html

And there is this one:

http://www.realsqlguy.com/?p=9

Show quoteHide quote

Thank you all. Is there a way of doing this without using a function?


Show quoteHide quote

MittyKom wrote:
Show quoteHide quoteOne method:
http://www.realsqlguy.com/?p=9

This is a common newbie error. Which you did not bother to Google
before posting, did you?  It immediately tells us that:

1) You do not know what a compiled program is, but expect SQL to be an
intrpreter.
2) You do not know what a parameter or understand the concept of a
scalar value.
3) You do not know that a table is the only data structure in SQL --
there are no lists!

The kludge you get in a newsgroup is dynamic SQL, with all the problems
that come with it.  Hey, but a kludge is so much easier and faster than
an education, or even a Google search beforee posting!

We can also give you some pure SQL that split up a string.  But that is
not the point; this is a bad design and poor programming.  If you
really give a damn about doing it right, Please post DDL, so that
people do not have to guess what the keys, constraints, Declarative
Referential Integrity, data types, etc. in your schema are. Sample data
is also a good idea, along with clear specifications.

This answer tells us

1)    You have no real industrial programming experience
2)    You do not understand how SQL is being used out in the field
3)    You are ignorant, condescending and arrogant

Show quoteHide quote

I have tried to ignore your reply, but it's still bothering me. I have gone
through a few of your replies in this newsgroup and they are not helpful.You
are not being forced to help us here. So please if you are willing to help,
do so without all these stupid comments. Also if you are a programmer, it
does not mean that everyone is a programmer in this newsgroup. Cheers.  

Show quoteHide quote

Gee, most people I run into learned Nested sets and relational division
options from my postings.  And I seem to sell a lot of books and
articles on SQL programming

And you are not forced to ask other people to do your job (or homework
in many casse) when you cannot or will not do it yourself.

I am trying to help you. what bothers you about my comments is that
they are not stupid, but expert.  I tell you in detail, with quotes and
references to Standards and Best Practices.  Even worse, I diagnose why
you are making errors.  A bad programmer is like a drunk who does not
want to hear that he has a problem and needs to change his habits.
"Hey, doctor, just give a fix to get over the hump!"

I can certainly see that a lot of bad code from non-programmers is
screwing up a lot of enterprises :)  Here is my standard "cut & paste"
for a CSV string.  It is pure SQL, one statement

This is a common Newbie question.  This shows that you don't know SQL
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first week of any SQL language class.  SQL is not your original
procedural programming language.

1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code.  Again, this is not just an SQL problem; this is a basic
misunderstanding  of programming principles.

2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator.  I like to use the
traditional comma.  Let's assume that you have a whole table full of
such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.

This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.

It makes life easier if the lists in the input strings start and end
with a comma.  You will need a table of sequential numbers -- a
standard SQL programming trick,  Now, the query, in SQL-92 syntax
(translate into your local dialect).  As far as I can tell, I was the
first person to post this solution to avoid WHILE loops.  You are given
a table with a key column and an input string of CSV integers:

CREATE VIEW ParmList (keycol, place, parm)
SELECT keycol,
       COUNT(S2.seq), -- reverse order
       CAST (SUBSTRING (I1.input_string
                        FROM S1.seq
                         FOR MIN(S2.seq) - S1.seq -1)
         AS INTEGER)
  FROM InputStrings AS I1,  Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
   AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
   AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step.  The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.  The relative postion of each element
in the list is given by the value of "place", but it does a count down
so you can plan horizonal placement in columns.

Hey, I can write kludges with the best of them, but I don't.  You need
to at the very least write a routine to clean out blanks, handle double
commas and non-numerics in the strings, take care of floating point and
decimal notation, etc.  Basically, you must write part of a compiler in
SQL.  Yeeeech!  Or decide that you do not want to have data integrity,
which is what most Newbies do in practice altho they do not know it.

something like this?

if exists(
Select 1 from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472')
)
SET @COUNT_CALLS_REC_1 = 1
else
SET @COUNT_CALLS_REC_1 = 0

SQL question
Function results
Incorrect information from a "View"
selecting on a null col.
Search