Conditional column NAME on insert/update

I need to dinamically select a column in which to insert based on a
parameter, I have this code, but it throws an incorrect syntax
error.
The value that I'm inserting is always static (the current date) what I
need to be dynamic is the column in which it'll be inserted.
How do I dinamically select a column to insert based on a parameter?
Create PROCEDURE dbo.UpdateDetalleOT (
@.eotId int, --Parameter
)
insert into OT (
select Case
when @.eotId = 1 THEN OTFechaBorrador
when @.eotId = 2 THEN OTFechaAAsignar
end
) values ......
Here's the explanation of the case:
Suppose that you have a Job Order that goes over diferrent states
(Draft, Confirmed, Assigned, Finished...)
Well, I need to save the Date when the Job Order changed it's state, so
I have the following columns in the JobOrder Table:
DraftDate : Date when the Job Order get's the Draft state
ConfirmedDate : Date when the Job Order get's the Confirmed state
AssignedDate : Date when the Job Order get's the Assignedstate
etc...
That's why I need to create a dynamic Insert/Update, because depending
the
state the Job Order will be saved...will depend which column
(DraftDate, ConfirmedDate, etc) to insert the current
date.
Best Regards
Fabio CavassiniYou should go out of your way to avoid dynamic SQL. If that means you have
to write several nearly identical insert statements, then so be it. A
little redundant code is a whole lot easier to understand and to maintain
and a whole lot more secure than dynamic SQL. You could also specify all
columns in the column list and then use CASE in a SELECT clause to insert
NULLs into the nonrelevant columns (That's what will be inserted anyway if
column values aren't supplied.).
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137972528.339815.240160@.g14g2000cwa.googlegroups.com...
>I need to dinamically select a column in which to insert based on a
> parameter, I have this code, but it throws an incorrect syntax
> error.
> The value that I'm inserting is always static (the current date) what I
> need to be dynamic is the column in which it'll be inserted.
> How do I dinamically select a column to insert based on a parameter?
> Create PROCEDURE dbo.UpdateDetalleOT (
> @.eotId int, --Parameter
> )
> insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
> ) values ......
> Here's the explanation of the case:
> Suppose that you have a Job Order that goes over diferrent states
> (Draft, Confirmed, Assigned, Finished...)
> Well, I need to save the Date when the Job Order changed it's state, so
> I have the following columns in the JobOrder Table:
> DraftDate : Date when the Job Order get's the Draft state
> ConfirmedDate : Date when the Job Order get's the Confirmed state
> AssignedDate : Date when the Job Order get's the Assignedstate
> etc...
> That's why I need to create a dynamic Insert/Update, because depending
> the
> state the Job Order will be saved...will depend which column
> (DraftDate, ConfirmedDate, etc) to insert the current
> date.
> Best Regards
> Fabio Cavassini
>|||"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137972528.339815.240160@.g14g2000cwa.googlegroups.com...
>I need to dinamically select a column in which to insert based on a
> parameter, I have this code, but it throws an incorrect syntax
> error.
> The value that I'm inserting is always static (the current date) what I
> need to be dynamic is the column in which it'll be inserted.
> How do I dinamically select a column to insert based on a parameter?
> Create PROCEDURE dbo.UpdateDetalleOT (
> @.eotId int, --Parameter
> )
> insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
> ) values ......
> Here's the explanation of the case:
> Suppose that you have a Job Order that goes over diferrent states
> (Draft, Confirmed, Assigned, Finished...)
> Well, I need to save the Date when the Job Order changed it's state, so
> I have the following columns in the JobOrder Table:
> DraftDate : Date when the Job Order get's the Draft state
> ConfirmedDate : Date when the Job Order get's the Confirmed state
> AssignedDate : Date when the Job Order get's the Assignedstate
> etc...
> That's why I need to create a dynamic Insert/Update, because depending
> the
> state the Job Order will be saved...will depend which column
> (DraftDate, ConfirmedDate, etc) to insert the current
> date.
> Best Regards
> Fabio Cavassini
>
In an INSERT there is no need to do such a thing. Obviously ALL columns are
affected by an INSERT statement, so just specify values for the ones you
want to populate and defaults or nulls for the ones you don't.
In the case of UPDATE you can use the general form:
UPDATE tbl
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3),
..
WHERE ...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I didn't have realized that every column is affected with the insert,
I'll use condition for the values...and use COALESCE for the update.
Thanks very much for your help
Best Regards
Fabio Cavassini|||This is how I implemented:

>INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
>SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),
I include all columns and the insert, and then I conditionally select
the value (null or current date) according to the parameter value.
Best Regards
Fabio Cavassini