Create one stored procedure for INSERT/UPDATE/SELECT

Hi!
Instead of creating one stored procedure for insert and another one for
update, i did one with both.
Should I create one stored procedure for each? Should i create one stored
procedure for Insert/Update/Select ?
ALTER PROCEDURE dbo.[Inserir Atualizar Conta Bancaria]
(
@.ContaBancariaID As Int,
@.AgenciaID As VarChar(50),
@.BancoID As Int,
@.Numero As Int
)
AS
DECLARE @.Error as int
DECLARE @.Rowcount as int
IF @.ContaBancariaID IS NULL BEGIN
INSERT INTO ContaBancaria (AgenciaID, BancoID, Numero) Values (@.AgenciaID,
@.BancoID, @.Numero)
SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
If @.ERROR <> 0 OR @.ROWCOUNT = 0
GOTO ERROR
END
ELSE BEGIN
UPDATE ContaBancaria SET AgenciaID = @.AgenciaID, BancoID = @.BancoID,
Numero = @.Numero WHERE ContaBancariaID = @.ContaBancariaID
SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
If @.ERROR <> 0 OR @.ROWCOUNT = 0
GOTO ERROR
END
RETURN 0
ERROR:
RAISERROR ('Erro ao realizar a operao', 16,1)
RETURN @.Error
Thanks you all
Bruno N> Should I create one stored procedure for each?
Personally, I prefer two separate stored procedures. The answer depends on
what your criteria for "should" are...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||One for Insert / Update and another for Select?
Or
One for Insert and another for Update?
Thans,
Bruno N
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi!
> Instead of creating one stored procedure for insert and another one for
> update, i did one with both.
> Should I create one stored procedure for each? Should i create one stored
> procedure for Insert/Update/Select ?
> ALTER PROCEDURE dbo.[Inserir Atualizar Conta Bancaria]
> (
> @.ContaBancariaID As Int,
> @.AgenciaID As VarChar(50),
> @.BancoID As Int,
> @.Numero As Int
> )
> AS
> DECLARE @.Error as int
> DECLARE @.Rowcount as int
>
> IF @.ContaBancariaID IS NULL BEGIN
> INSERT INTO ContaBancaria (AgenciaID, BancoID, Numero) Values
> (@.AgenciaID, @.BancoID, @.Numero)
> SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
> If @.ERROR <> 0 OR @.ROWCOUNT = 0
> GOTO ERROR
> END
> ELSE BEGIN
> UPDATE ContaBancaria SET AgenciaID = @.AgenciaID, BancoID = @.BancoID,
> Numero = @.Numero WHERE ContaBancariaID = @.ContaBancariaID
> SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
> If @.ERROR <> 0 OR @.ROWCOUNT = 0
> GOTO ERROR
> END
> RETURN 0
> ERROR:
> RAISERROR ('Erro ao realizar a operao', 16,1)
> RETURN @.Error
> --
> Thanks you all
> Bruno N
>|||You should definitely be separating SELECT procedures from others, in my
opinion. As for whether to separate INSERT from UPDATE, the answer still
depends on your decision criteria.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Bruno N" <nylren@.hotmail.com> wrote in message
news:uuZFqe6OFHA.164@.TK2MSFTNGP12.phx.gbl...
> One for Insert / Update and another for Select?
> Or
> One for Insert and another for Update?
> Thans,
> Bruno N
>
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
stored
>|||To add to what the others have said:
I have separate stored procedures to Insert, Update or Delete contacts.
I have one stored procedure to Insert, Update or Delete attendance.
Why? Because you usually don't work on the same form to Insert a contact and
modify another at the same time.
But you will work on one form to Insert, Update and Delete attendance.
...at least in the company that hired me.
"Bruno N" <nylren@.hotmail.com> wrote in message
news:uuZFqe6OFHA.164@.TK2MSFTNGP12.phx.gbl...
> One for Insert / Update and another for Select?
> Or
> One for Insert and another for Update?
> Thans,
> Bruno N
>
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
>