Transact SQL UPDATE based on a declared variable

Question

I have two tables called ALUMNO and ALUMNO_GENERAL.

I would like to create a stored procedure which automatically chooses which table will update by looking at the variable @campo declared as a parameter to the stored procedure.

When I execute the procedure below, it says 1 row affected, but does not update the value of the table.

exec updateAl 'CALLE','0015','ROJO'

Stored procedure:

create procedure updateAl @campo varchar(30), @matr varchar(10), @newVal varchar(15) as begin if @campo IN ('AP_PATERNO' , 'AP_MATERNO', 'NOMBRE', 'GRUPO', 'TURNO' , 'SEMESTRE' , 'STATUS' , 'NUMEMPLEADO') begin select @campo declare @sql varchar (1000) select @sql= 'update alumno set '+ @campo+'='+@newVal +' where MATRICULA='+@matr exec(@sql) end else update ALUMNO_GENERAL set @campo =@newVal where MATRICULA=@matr end

You second statement in the else isn't going to work as-is. So if your @campo is causing it go to the else then you'll have to fix that up to be dynamic as well. — JNevill
– JNevill, Commented Nov 16, 2017 at 21:07
I hope you are not going to use this in a web application; Little Bobby Tables is close... — Ruud Helderman
– Ruud Helderman, Commented Nov 16, 2017 at 21:19
Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space. — HABO
– HABO, Commented Nov 16, 2017 at 21:19
My guess would be that you have a UI that auto-saves each field after you leave it (inline editing kind-of-thing), and you don't want to write a separate SQL statement for each field to do the updating. There are definitely alternatives and frameworks that would help you with this. You are setting yourself up for a maintenance nightmare with procedures like this. — Cᴏʀʏ
– Cᴏʀʏ, Commented Nov 16, 2017 at 21:21
this is a practice in my school, and I just thought about that to save code.. — Eduardo Chaparro
– Eduardo Chaparro, Commented Nov 16, 2017 at 21:42

LONG · Accepted Answer · 2017-11-16 21:10:26Z

1

Your dynamic sql is not correct, try this:

select @sql= 'update alumno set '+ @campo+'='''+@newVal +''' where MATRICULA='''+@matr + ''' exec(@sql) end

edited Nov 16, 2017 at 21:10

answered Nov 16, 2017 at 21:07

LONG

4,6302 gold badges21 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cᴏʀʏ Over a year ago

Probably need extra quotes around @matr as well, since it's also a varchar.

Eduardo Chaparro Over a year ago

it doesn't works :/ also shows the message "1 row(s) affected" but doesn't update the value on the table.. Any idea?

Collectives™ on Stack Overflow

Transact SQL UPDATE based on a declared variable

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related