is it possible to call a stored procedure without a cursor?

is it possible to call a stored procedure without a cursor?Absolutely.|||hahaha, ok, well... If it is possible, would you please tel me how, or the name how to do it, or even a link to a site that shows me how. I've searched high and low and can't seem to find out how, help would be appreciated, thanks|||If the procedure is the first command in your code, you can simply call it by naming it:

MyCustomProcedure
select 'Procedure completed.'

Otherwise, you need to use the EXEC command:

select 'Getting ready to call the procedure...'
exec MyCustomProcedure
select 'Procedure completed.'

blindman

Is it possible pass to procedure open cursor from other procedure ??

Is it possible pass to procedure open cursor from other procedure ?
Any suggestions will be appreciated
Message posted via http://www.webservertalk.comYes, you can declare a cursor as an output a parameter of a stored
procedure. But it is most likely not the most efficient way to share data
between procedures. The alternatives are discussed by SQL Server MVP Erland
Sommarskog in the following article:
http://www.sommarskog.se/share_data.html
Jacco Schalkwijk
SQL Server MVP
"JB via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:bd838d17bc034f26a19b475812a8eeb7@.SQ
webservertalk.com...
> Is it possible pass to procedure open cursor from other procedure ?
> Any suggestions will be appreciated
> --
> Message posted via http://www.webservertalk.com|||Yes, but it's probably not a good idea. Could you explain your
requirement. There's sure to be a better way.
David Portas
SQL Server MVP
--|||In my db i call to some procedure suppese MyProc from application and pass
some XML,
inside the MyProc i parse the xml, insert the data to temporary table and
now according to some information that i got from xml call to other
procedure suppose proc1 or
proc2 or ... and in one of these procedures i perform some operation on
data that exist in temporary table. Actually i solve this problem with
temporary table (#myTable) however it's not good idea to use it and i can't
to use it, i don't know what to do.
The problem is that according to data in XML i call to appropriate
procedure, i can parse the xml in Myproc sp and call to relevant nested
procedure
proc1 or proc2, ... and pass this XML again however i don't want to open
and
parse the XML twice
Message posted via http://www.webservertalk.com|||This doesn't make a lot of sense to me as a design for a process in SQL
Server. You should parse your XML once only in order to load it into
appropriate tables. TSQL provides a proc to do this:
sp_xml_removedocument. Then execute procs from the data in tables. That
way you should avoid lots of messy cursors, temp tables and procedural
code. XML is for data-interchange only - it's a lousy way to persist
data and move it around inside the database.
David Portas
SQL Server MVP
--|||CORRECTION: sp_xml_preparedocument is the name of the proc you want.
David Portas
SQL Server MVP
--|||my problem is that after i parse the xml i fill temporary table with it's
data and now i need to call to other procedure to which i want pass
temporary table, but it isn't good idea also pass an open cursor is not
good idea
Message posted via http://www.webservertalk.com|||So why create two separate SPs and why load the data into a temporary
table or a cursor? You seem to be looking for a solution to a problem
that wouldn't exist if you made a better design.
David Portas
SQL Server MVP
--|||because according to the data that i got from XML i call to appropriate
procedure
Message posted via http://www.webservertalk.com|||OK. So load the XML data into tables where it belongs. Then execute the
logic for BOTH procedures but add or modify the WHERE clauses in your
DML code such that the logic only executes as appropriate. Example
pseudo-code:
Instead of this:
IF x=1
EXEC usp_proc1
IF x=2
EXEC usp_proc2
Do this:
..
WHERE x = 1
..
WHERE x = 2
In other words, adopt the declarative, set-based approach rather than a
procedural approach.
David Portas
SQL Server MVP
--

Is it possible pass to procedure open cursor from other procedure

JB, you need to take a break and get an education. Your postings
consistently show that you missed the whole idea of a declarative,
set-oriented language. I put in a chapter on "Thinking in Sets" in my
new book because of this problem. It gets a little Zen, but it is
important. See the forest and not the trees. Use the right words so
your mind will have the right concepts (rows are not records, etc.).
Example: I have a bag of pennies and I want to know the value of the
bag
Procedural solution: count the pennies one at a time (i.e. cursor).
This sucks when you have $1 million dollars
SQL solution: Weigh the bag as a whole unit in itself, divide by the
weight of a single penny. Scales to large amoutns of data quickly.
It takes about a year or more of programming in SQL to make the shift.
Do not XXXXX aobut that; it takes six years to beocme a Union
Journeyman Carpenter in New York State and their incompetence cannot
kill near or miam as many people as a bad database.
Right now, you are writing code that is 1,2 or 3 orders of magnitude
slower than it should be and you probably have little or no data
integrity in your schemas. In short, you are very dangerous right now.Thanks, where can i got your book ?
Message posted via http://www.webservertalk.com|||u r very helpfull
Message posted via http://www.webservertalk.com

Is it possible pass to procedure open cursor from other procedure

No. A procedure can not have a input parameter of type cursor. You can call
a
second procedure that has an output parameter of type cursor.
Example:
use northwind
go
create procedure proc1
@.c cursor varying output
as
set nocount on
set @.c = cursor local fast_forward for select orderid from dbo.orders
open @.c
go
create procedure proc2
as
set nocount on
declare @.c cursor
declare @.i int
exec proc1 @.c output
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c into @.i
if @.@.error != 0 or @.@.fetch_status != 0 break
print @.i
end
close @.c
deallocate @.c
end
go
exec proc2
go
drop procedure proc2, proc1
go
Try to find a set-based solution first, leave cursors as the last tool in
your pocket.
AMB
"JB via webservertalk.com" wrote:

> Is it possible pass to procedure open cursor from other procedure ?
> Any suggestions will be appreciated
> --
> Message posted via http://www.webservertalk.com
>thank you.
What divantages of using cursor rather then temp table?
Message posted via http://www.webservertalk.com

is it possibl to avoid cursor (returned rows)

Normal set up

cursor
Select ( a bunch of crap..)

Usually I have to use the cursor to go through each record and do more crap with each record, for example I may email each user returned in the select. Is it possible to somehow use a stored procedure instead of a cursor or any other way to make this more efficient?

It's a batch job (run at night) and it'll return about 7,000 rows roughly each time so it's not terrible to the point where i need it to be super efficient, i'd just like to keep as little load on the sql server as much as possible. I assume it's one of those situations where I just can't avoid using the cursor.If your select is simple, you can define a variable to hold the table key(s) and use a while loop to walk through the table.

If your select is complex you could dump the result set of the select into a table and then use a while loop/variable to walk the table.

declare @.au_id varchar(11)
select @.au_id = min(au_id) from pubs.dbo.authors
while @.au_id is not null begin
select * from authors where au_id = @.Au_id
select @.au_id = min(au_id) from pubs.dbo.authors where au_id > @.au_id
end

granted this is simple but it ilistrattes the point

Is Cursor Best Way To Go?

I need to get two values from a complex SQL statement which returns a single
record and use those two values to update a single record in a table. In
order to assign those two values to variables and then use those variables
in the UPDATE statement, I created a cursor and used Fetch Next... Into.
This way, I only have to call the complex SQL once instead of twice.
This seems like the best way to go. However, I've always used cursors for
scrolling through resultsets. In this case, though, there is just a single
record being returned, and the cursor doesn't scroll.
Is that the most efficient way to go, or is there a better way to be able to
use both values from the SQL statement without having to call it twice?
Thanks.Hi Neil
I'd need more details regarding the query/DDL to say anything too
meaningful, but certainly a set-based solution is always preferable to
an iterative/cursor-solution.|||here is a guess without seeing your code.
declare @.v1 int, @.v2 int
select @.v1=[col1], @.v2=[colx]
from (
-- your complex query
) as derived_table
-oj
"Neil" <nospam@.nospam.net> wrote in message
news:vhAne.4180$s64.2269@.newsread1.news.pas.earthlink.net...
>I need to get two values from a complex SQL statement which returns a
>single record and use those two values to update a single record in a
>table. In order to assign those two values to variables and then use those
>variables in the UPDATE statement, I created a cursor and used Fetch
>Next... Into. This way, I only have to call the complex SQL once instead
>of twice.
> This seems like the best way to go. However, I've always used cursors for
> scrolling through resultsets. In this case, though, there is just a single
> record being returned, and the cursor doesn't scroll.
> Is that the most efficient way to go, or is there a better way to be able
> to use both values from the SQL statement without having to call it twice?
> Thanks.
>|||SQL Server doesn't support the standard SQL syntax for this but it does
have a proprietary syntax to do the same job:
UPDATE T1
SET x = foo,
y = bar
FROM
(SELECT foo, bar /* your query here */
FROM ... ) AS T2
WHERE T2.key_col = T1.key_col
/* join condition should yield a single row from T2 for each row in
T1 */

> I've always used cursors for
> scrolling through resultsets
Really? For what purpose? Cursors should be the rare exception rather
than the rule. Usually there are better set-based solutions.
David Portas
SQL Server MVP
--|||> SQL Server doesn't support the standard SQL syntax for this but it does
> have a proprietary syntax to do the same job:
> UPDATE T1
> SET x = foo,
> y = bar
> FROM
> (SELECT foo, bar /* your query here */
> FROM ... ) AS T2
> WHERE T2.key_col = T1.key_col
> /* join condition should yield a single row from T2 for each row in
> T1 */
Yes, that was what I was looking for (though I needed to use UPDATE T1
SET... From T1, (Select foo...) As T2...)
Also, since I'm only updating a single row in T1, and since T2 only returns
a single row with values, I eliminated the WHERE T2.keycol=T1.keycol. My SQL
looks like:
UPDATE T1
SET X = T2.FOO, Y=T2.BAR
FROM T1, (SELECT FOO, BAR FROM MYQUERY WHERE ID=@.VALUE) AS T2
WHERE T1.ID=@.VALUE
Do you see any problem with that?

> Really? For what purpose? Cursors should be the rare exception rather
> than the rule. Usually there are better set-based solutions.
I guess one of the main areas where I've used them is in order-rearranging
functions -- such as where there are a set of items in a table, each with a
value in a field that specifies the order. The user clicks, say, an up arrow
in the interface, and the current item needs to move up one in order --
decrement it's field value by one, and increment the preceding item's by
one.
Another time I used a cursor was in a procedure in which the length of two
fields combined needed to be compared to a value and then, based on the
length of the combined fields, different values would be placed in a certain
field. I suppose that could have just been done with a set-based solution;
but the cursor seemed more straightforward. It was also only dealing with
one record at a time.
Thanks for your help!
Neil

> --
> David Portas
> SQL Server MVP
> --
>|||Re-arranging order based on a column (pos):
UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos
Update different columns based on the length of a string value:
UPDATE YourTable
SET col1 =
CASE
WHEN LEN(x+y)<=10
THEN a ELSE b END,
col2 =
CASE
WHEN LEN(x+y)>10
THEN a ELSE b END
WHERE ...
David Portas
SQL Server MVP
--
UPDATE|||David Portas Jun 2, 7:07 am show options
Newsgroups: comp.databases.ms-sqlserver,
microsoft.public.sqlserver.programming
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.o=ADrg> - Find
messages by this author
Date: 2 Jun 2005 04:07:17 -0700
Local: Thurs,Jun 2 2005 7:07 am
Subject: Re: Is Cursor Best Way To Go?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
Re-arranging order based on a column (pos):
UPDATE foo
SET pos =3D CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos;
Very neat! I always did a monster CASE expression with extra WHEN
clauses based on (old_pos ' newpos).|||Hi, David.
Here's another one for you. I have an sp that takes various input parameters
for a customer, and processes the data using various case statements. I now
want to run this sp for all customers on a nightly basis. My immediate
reaction, as previously, would be to use a cursor to loop through all the
customers, get the input parameters for the sp from the Customer table, and
call the sp once for each customer. Is there a way to do this without a
cursor?
Thanks,
Neil
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1117710437.665131.255080@.g47g2000cwa.googlegroups.com...
> Re-arranging order based on a column (pos):
> UPDATE foo
> SET pos = CASE pos
> WHEN @.old_pos
> THEN @.new_pos
> ELSE pos + SIGN(@.old_pos - @.new_pos)
> END
> WHERE pos BETWEEN @.old_pos AND @.new_pos
> OR pos BETWEEN @.new_pos AND @.old_pos
> Update different columns based on the length of a string value:
> UPDATE YourTable
> SET col1 =
> CASE
> WHEN LEN(x+y)<=10
> THEN a ELSE b END,
> col2 =
> CASE
> WHEN LEN(x+y)>10
> THEN a ELSE b END
> WHERE ...
> --
> David Portas
> SQL Server MVP
> --
>
>
>
> UPDATE
>|||Neil (nospam@.nospam.net) writes:
> Here's another one for you. I have an sp that takes various input
> parameters for a customer, and processes the data using various case
> statements. I now want to run this sp for all customers on a nightly
> basis. My immediate reaction, as previously, would be to use a cursor to
> loop through all the customers, get the input parameters for the sp from
> the Customer table, and call the sp once for each customer. Is there a
> way to do this without a cursor?
Yes, but you will of course have to rewrite the procedure, so that it
works with many customers. To do this, you need to pass the input
parameters in a table rather than as parameter. This table can be a temp
table, or a permanent table which is keyed by @.@.spid or similar. I discuss
this on http://www.sommarskog.se/share_data.html#temptables.
Well, rather you would write a new procedure that works with many, and
then rewrite the old procedure to be a wrapper on the new procedure.
Now, whether you actually should go this route depends. Let's say that
it takes 10 minutes to run a cursor over all customers and call the
existing procedure, and that you have plenty of time to spare in the
night. In this case, it's not likely to be worth the development effort.
Also, if you opt to use a temp table to pass the input parameters, the
procedure will be recompiled each time. This will have the net effect
that calls for single customers will now be more expensive, and could
even be performance problems, if the procedure is huge.
We actually did this exercise with a core procedure in our system, and
in our case it was really necessary. But it was a major developement task.
Our estimate was 200 hours for development, but I think the true outcome
was more than 300 hours. But that was a long procedure, on 700-800 lines
and which called several sub-procedures. The final multi-version is a
3000-line monster with no less than 43 table variables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Everything Erland has said. This is where it pays to have a good design
pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
UI you may typically want to pass parameters for a single row. For
procs that implement other business logic however, you should generally
design with a set-based approach in mind. Unfortunately, programmers
used to other languages too often try to encapsulate all logic in procs
that act like scalar functions - a sure route to cursor hell!
David Portas
SQL Server MVP
--

Is Cursor Best Way To Go?

I need to get two values from a complex SQL statement which returns a single
record and use those two values to update a single record in a table. In
order to assign those two values to variables and then use those variables
in the UPDATE statement, I created a cursor and used Fetch Next... Into.
This way, I only have to call the complex SQL once instead of twice.

This seems like the best way to go. However, I've always used cursors for
scrolling through resultsets. In this case, though, there is just a single
record being returned, and the cursor doesn't scroll.

Is that the most efficient way to go, or is there a better way to be able to
use both values from the SQL statement without having to call it twice?

Thanks.Hi Neil

I'd need more details regarding the query/DDL to say anything too
meaningful, but certainly a set-based solution is always preferable to
an iterative/cursor-solution.|||here is a guess without seeing your code.

declare @.v1 int, @.v2 int
select @.v1=[col1], @.v2=[colx]
from (
-- your complex query
) as derived_table

--
-oj

"Neil" <nospam@.nospam.net> wrote in message
news:vhAne.4180$s64.2269@.newsread1.news.pas.earthl ink.net...
>I need to get two values from a complex SQL statement which returns a
>single record and use those two values to update a single record in a
>table. In order to assign those two values to variables and then use those
>variables in the UPDATE statement, I created a cursor and used Fetch
>Next... Into. This way, I only have to call the complex SQL once instead
>of twice.
> This seems like the best way to go. However, I've always used cursors for
> scrolling through resultsets. In this case, though, there is just a single
> record being returned, and the cursor doesn't scroll.
> Is that the most efficient way to go, or is there a better way to be able
> to use both values from the SQL statement without having to call it twice?
> Thanks.|||SQL Server doesn't support the standard SQL syntax for this but it does
have a proprietary syntax to do the same job:

UPDATE T1
SET x = foo,
y = bar
FROM
(SELECT foo, bar /* your query here */
FROM ... ) AS T2
WHERE T2.key_col = T1.key_col
/* join condition should yield a single row from T2 for each row in
T1 */

> I've always used cursors for
> scrolling through resultsets

Really? For what purpose? Cursors should be the rare exception rather
than the rule. Usually there are better set-based solutions.

--
David Portas
SQL Server MVP
--|||> SQL Server doesn't support the standard SQL syntax for this but it does
> have a proprietary syntax to do the same job:
> UPDATE T1
> SET x = foo,
> y = bar
> FROM
> (SELECT foo, bar /* your query here */
> FROM ... ) AS T2
> WHERE T2.key_col = T1.key_col
> /* join condition should yield a single row from T2 for each row in
> T1 */

Yes, that was what I was looking for (though I needed to use UPDATE T1
SET... From T1, (Select foo...) As T2...)

Also, since I'm only updating a single row in T1, and since T2 only returns
a single row with values, I eliminated the WHERE T2.keycol=T1.keycol. My SQL
looks like:

UPDATE T1
SET X = T2.FOO, Y=T2.BAR
FROM T1, (SELECT FOO, BAR FROM MYQUERY WHERE ID=@.VALUE) AS T2
WHERE T1.ID=@.VALUE

Do you see any problem with that?

>> I've always used cursors for
>> scrolling through resultsets
> Really? For what purpose? Cursors should be the rare exception rather
> than the rule. Usually there are better set-based solutions.

I guess one of the main areas where I've used them is in order-rearranging
functions -- such as where there are a set of items in a table, each with a
value in a field that specifies the order. The user clicks, say, an up arrow
in the interface, and the current item needs to move up one in order --
decrement it's field value by one, and increment the preceding item's by
one.

Another time I used a cursor was in a procedure in which the length of two
fields combined needed to be compared to a value and then, based on the
length of the combined fields, different values would be placed in a certain
field. I suppose that could have just been done with a set-based solution;
but the cursor seemed more straightforward. It was also only dealing with
one record at a time.

Thanks for your help!

Neil

> --
> David Portas
> SQL Server MVP
> --|||Re-arranging order based on a column (pos):

UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos

Update different columns based on the length of a string value:

UPDATE YourTable
SET col1 =
CASE
WHEN LEN(x+y)<=10
THEN a ELSE b END,
col2 =
CASE
WHEN LEN(x+y)>10
THEN a ELSE b END
WHERE ...

--
David Portas
SQL Server MVP
--

UPDATE|||David Portas Jun 2, 7:07 am show options

Newsgroups: comp.databases.ms-sqlserver,
microsoft.public.sqlserver.programming
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.o*rg> - Find
messages by this author
Date: 2 Jun 2005 04:07:17 -0700
Local: Thurs,Jun 2 2005 7:07 am
Subject: Re: Is Cursor Best Way To Go?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Re-arranging order based on a column (pos):

UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos;

Very neat! I always did a monster CASE expression with extra WHEN
clauses based on (old_pos ?? newpos).|||Hi, David.

Here's another one for you. I have an sp that takes various input parameters
for a customer, and processes the data using various case statements. I now
want to run this sp for all customers on a nightly basis. My immediate
reaction, as previously, would be to use a cursor to loop through all the
customers, get the input parameters for the sp from the Customer table, and
call the sp once for each customer. Is there a way to do this without a
cursor?

Thanks,

Neil

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1117710437.665131.255080@.g47g2000cwa.googlegr oups.com...
> Re-arranging order based on a column (pos):
> UPDATE foo
> SET pos = CASE pos
> WHEN @.old_pos
> THEN @.new_pos
> ELSE pos + SIGN(@.old_pos - @.new_pos)
> END
> WHERE pos BETWEEN @.old_pos AND @.new_pos
> OR pos BETWEEN @.new_pos AND @.old_pos
> Update different columns based on the length of a string value:
> UPDATE YourTable
> SET col1 =
> CASE
> WHEN LEN(x+y)<=10
> THEN a ELSE b END,
> col2 =
> CASE
> WHEN LEN(x+y)>10
> THEN a ELSE b END
> WHERE ...
> --
> David Portas
> SQL Server MVP
> --
>
>
>
> UPDATE|||Neil (nospam@.nospam.net) writes:
> Here's another one for you. I have an sp that takes various input
> parameters for a customer, and processes the data using various case
> statements. I now want to run this sp for all customers on a nightly
> basis. My immediate reaction, as previously, would be to use a cursor to
> loop through all the customers, get the input parameters for the sp from
> the Customer table, and call the sp once for each customer. Is there a
> way to do this without a cursor?

Yes, but you will of course have to rewrite the procedure, so that it
works with many customers. To do this, you need to pass the input
parameters in a table rather than as parameter. This table can be a temp
table, or a permanent table which is keyed by @.@.spid or similar. I discuss
this on http://www.sommarskog.se/share_data.html#temptables.

Well, rather you would write a new procedure that works with many, and
then rewrite the old procedure to be a wrapper on the new procedure.

Now, whether you actually should go this route depends. Let's say that
it takes 10 minutes to run a cursor over all customers and call the
existing procedure, and that you have plenty of time to spare in the
night. In this case, it's not likely to be worth the development effort.
Also, if you opt to use a temp table to pass the input parameters, the
procedure will be recompiled each time. This will have the net effect
that calls for single customers will now be more expensive, and could
even be performance problems, if the procedure is huge.

We actually did this exercise with a core procedure in our system, and
in our case it was really necessary. But it was a major developement task.
Our estimate was 200 hours for development, but I think the true outcome
was more than 300 hours. But that was a long procedure, on 700-800 lines
and which called several sub-procedures. The final multi-version is a
3000-line monster with no less than 43 table variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Everything Erland has said. This is where it pays to have a good design
pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
UI you may typically want to pass parameters for a single row. For
procs that implement other business logic however, you should generally
design with a set-based approach in mind. Unfortunately, programmers
used to other languages too often try to encapsulate all logic in procs
that act like scalar functions - a sure route to cursor hell!

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Everything Erland has said. This is where it pays to have a good design
> pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
> UI you may typically want to pass parameters for a single row. For
> procs that implement other business logic however, you should generally
> design with a set-based approach in mind. Unfortunately, programmers
> used to other languages too often try to encapsulate all logic in procs
> that act like scalar functions - a sure route to cursor hell!

Permit me to expand a bit on what I touched in my previous post.

In many cases it is reasonable to write a procedure that operates
on a scalar set of values. It cannot be denied that writing such a
procedure is simpler, and thus cuts development costs at that stage.

Passing data in tables is actually quite messy. Let's look at the options:
1) Use a temp table. The caller must create the temp table, and the callee
trust the caller. If the procedure is called from many places, many
callers must create the table. This can be address with an include-
file, if you have the luxury of a preprocessor. We have that, but it's
not a standard feature.
And if even you get by all this, the callee is recompiled for each
new instance of the caller. This can be expensive.
2) A permanent table, typically spid-keyed. We use this technique for the
really heavy-duty stuff. If you make this routine, you get lots of
these tables. Note also that the tables are typically stored disjunct
in the version-control system, which means that procedure and
"parameter list" are in two places.
3) Clients can't use any of 1 or 2, but they can pass comma-separated
lists or XML-documents. But if A_SP calls B_SP, it would be a bad
idea if A_SP built an XML document from its data, only to be able
to call B_SP. What you can to is to have a wrapper that accepts
the XML document, and unpacks that into the temp table or spid-
keyed table. If the client is mainly interested in single-row
operations, it probably needs a scalar wrapper as well. Else, it
will be a lot extra development overhead to build XML documents.

So, clearly, if you at point A in your devleopment cycle only have a need
for a procedure that operates on scalar parameters, you write a procedure
that works with scalar procedure only, because that is what you are paid
for.

If you later at point B need to do the same operation on many rows,
you have to make a judicious choice between:

1) Write a cursor loop.
2) Just forget about the old procedure, and write a new set-based.
3) Replace the old procedure.

If the logic of the procedure is trivial, like "IF NOT EXISTS INSERT ELSE
UPDATE" you should pick #2. But say that the logic is non-trivial, for
instance includes updates to dependent tables in some unnormalised
scenario, then at some point #2 becomes completely impermissible. At
this point #1 can very well be the best pick. Say that you know that
it will be rare that the cursor will comprise as much as 100 rows. If
the procedure takes 100 ms to run, it may be very difficult to motivate
to rewrite the old procedure, if this would take 100 hours.

There is also another issue here that is worth mentioning. Say that your
procedure performs some sort of INSERT operation (in a couple of tables),
and the data comes from some less trustable source, which thus may
supply non-conformant data. If you have a scalar procedure, error
handling is fairly simple. You can do explicit checks on anticipated
errors, but you can be fairly relaxed, because if some data violates a
constraint or trigger check, the operation will fail.

This because a lot more complex if you accept input data in a table.
Because if you apply the same strategy, 1000 rows could fail to insert
when there is an error in a single one. This could very likely be
entirely unacceptable. Thus in case, you will need to duplicate all
constraint and trigger checks in your code, so you can mark which rows
that are illegal.

So while it is easy to say "replace cursor loops with set-based
statments", one should realise that in complex cases, this is far from
trivial.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp