Stored Procedure CRUD MySQL

Question

I have a problem with a procedure that I'm developing in MySQL, this is my first time. A few months ago I made a small CRUD (select, insert, update) in MSSQL, and only sends the required data.

create procedure sp_Bodegas (@Opcion varchar(10), @CodBodega int = null, @NomBodega varchar(75) = null, @DirBodega varchar(150) = null, @EstBodega bit = null)
as
begin
set nocount on
if (@Opcion='SELECT')
begin
select cod_Bodega as CodBodega, nom_Bodega as NomBodega, dir_Bodega as DirBodega, est_Bodega as EstBodega from inv_Bodegas
end
if (@Opcion='INSERT')
begin
insert into inv_Bodegas (cod_Bodega, nom_Bodega, dir_Bodega, est_Bodega) values (@CodBodega, @NomBodega, @DirBodega, @EstBodega)
end
if (@Opcion='UPDATE')
begin
update inv_Bodegas set nom_Bodega = @NomBodega, dir_Bodega = @DirBodega where cod_Bodega = @CodBodega
end
set nocount off
end;

But a few days ago I started to work with MySQL and try to do the same procedure but this can not use variables such as '@NomBodega = Null' for not sending any data required option. then create input variables to receive the data, but now there are many parameters that have to send every time you run the procedure.

create procedure sp_Bodegas (in Opcion varchar(10), in CodBodega int, in NomBodega varchar(75), in DirBodega varchar(150), in EstBodega bit)
begin
if Opcion = 'SELECT' then 
    select cod_Bodega as CodBodega, nom_Bodega as NomBodega, dir_Bodega as DirBodega, est_Bodega as EstBodega from inv_Bodegas;
end if;
if Opcion = 'INSERT' then
    insert into inv_Bodegas (cod_Bodega, nom_Bodega, dir_Bodega, est_Bodega) values (@CodBodega, @NomBodega, @DirBodega, @EstBodega);
end if;
if Opcion = 'UPDATE' then
    update inv_Bodegas set nom_Bodega = @NomBodega, dir_Bodega = @DirBodega where cod_Bodega = @CodBodega;
end if;
end;

Wanted to know how I can make it work so very similar in my application, I am using aspx and EF for my database.

Thanks.

On a side note, I would have a separate stored proc for each action. — Jon P, Mar 09 '15 at 02:56

score 0 · Answer 1 · answered Feb 18 '15 at 03:12

0

You have declared the variable as NomBodega but when you are using it in your procedure you use @NomBodega.

The @NomBodega would only be required if you passed in an out parameter to select the value after the procedure has run.

answered Feb 18 '15 at 03:12

BK435

3,076
3
19
27

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

Thanks for your help, but i was very busy.

After read a lot of SP MYSQL i find is not possible so. i gona send every value.

Is it possible to have a default parameter for a mysql stored procedure?

edited May 23 '17 at 11:50

Community

1
1

answered Mar 09 '15 at 01:59

Wixo

3
7

Stored Procedure CRUD MySQL

2 Answers2