Prev: sql 2000 error handling
Next: Plain Text In Image Field
From: Bob on 9 Jun 2010 22:56
Hello folks.

I have an application that uses about 110 "small" SPs. Most are only 4
lines long.

I would like to be able to group my SPs together.

I suppose I could add a parameter to each group of SPs and then use
the case functionality such as

create proc GetFruit @id,@fruit varchar(20),@newfruit varchar(20)
as

if @id=1
select * from FruitTable where fruit = @fruit
if @id=2
delete from FruitTable where fruit = @fruit
if @id=2
update FruitTable set fruit=(a)newfruit where fruit = @fruit


ect ect ect

But is there a better way to do it?

Thanks in advance,

Bob Sweeney
From: Erland Sommarskog on 10 Jun 2010 03:13
Bob (Go1369(a)Yahoo.Com) writes:
> I have an application that uses about 110 "small" SPs. Most are only 4
> lines long.
>
> I would like to be able to group my SPs together.
>
> I suppose I could add a parameter to each group of SPs and then use
> the case functionality such as
>
> create proc GetFruit @id,@fruit varchar(20),@newfruit varchar(20)
> as
>
> if @id=1
> select * from FruitTable where fruit = @fruit
> if @id=2
> delete from FruitTable where fruit = @fruit
> if @id=2
> update FruitTable set fruit=(a)newfruit where fruit = @fruit
>
>
> ect ect ect
>
> But is there a better way to do it?

Yes. Don't do anything. I don't know exactly what these procedure do,
but I think one procedure should perform a well-defined task. It is
usually OK with a procedure that performs an INSERT or an UPDATE
depending on the row exists or not. Particularly this is good, because
it relieves the caller from keeping track of whether there is an
existing row. And independent of the action, the parameters are the same.

But a DELETE or a SELECT procedure only needs the key, and with regards
to each other they perform very different tasks.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: sql 2000 error handling
Next: Plain Text In Image Field