Conditional Column Name On Insert

[cavassinif]

I need to dynamic select a column in which insert a vale based on a
parameter value, I have this code, but it throws an incorrect syntax
error.

How do I dinamically select a column to insert based on a parameter?

Create PROCEDURE dbo.UpdateDetalleOT (
@eotId int,
)

insert into OT (
select Case
when @eotId = 1 THEN OTFechaBorrador
when @eotId = 2 THEN OTFechaAAsignar
end
) values ....
Best Regards
Fabio Cavassini
http://www.pldsa.com

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Try something like this instead:

If @eotID = 1
BEGIN
INSERT INTO OT (OTFechaBorrador)
VALUES ...
END

IF @eotId = 2
BEGIN
INSERT INTO OT (OTFechaAAsignar)
VALUES ...
END
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F9/oechKqOuFEgEQL0DwCfVQyA7xrkbFiBhXHJwcZwh6jlv1sAnj8 x
Ig0V5L9rm9Cpt13pG+Talbie
=0ECz
-----END PGP SIGNATURE-----

ca********@gmail.com wrote:

I need to dynamic select a column in which insert a vale based on a
parameter value, I have this code, but it throws an incorrect syntax
error.

How do I dinamically select a column to insert based on a parameter?

Create PROCEDURE dbo.UpdateDetalleOT (
@eotId int,
)

insert into OT (
select Case
when @eotId = 1 THEN OTFechaBorrador
when @eotId = 2 THEN OTFechaAAsignar
end
) values ....

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

[Hugo Kornelis]

On 20 Jan 2006 11:49:20 -0800, ca********@gmail.com wrote:

I need to dynamic select a column in which insert a vale based on a
parameter value, I have this code, but it throws an incorrect syntax
error.

How do I dinamically select a column to insert based on a parameter?

Create PROCEDURE dbo.UpdateDetalleOT (
@eotId int,
)

insert into OT (
select Case
when @eotId = 1 THEN OTFechaBorrador
when @eotId = 2 THEN OTFechaAAsignar
end
) values ....
Best Regards
Fabio Cavassini
http://www.pldsa.com

Hi Fabio,

You can't insert into just one column - you insert a complete row, and
you'll have to give values (either real values or NULL) for all columns.
Sure, the language permits you to leave out some columns, but that's
just a shorthand way for specifying that you want to insert the defined
DEFAULT value (if any) or NULL in all the other columns.

To do what you appear to want (and I *really* hope that this is an
extremely simplified illustration of the real problem, because if this
is your real procedure, you have much, much bigger problems), you can
either use the code posted by MGFoster, or use

INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar)
SELECT CASE WHEN @eotId = 1 THEN .... ELSE NULL END),
CASE WHEN @eotId = 2 THEN .... ELSE NULL END)

--
Hugo Kornelis, SQL Server MVP

[cavassinif]

Thanks for all your advices:

MGFoster:
Yes, this would be a solution....but the table in which I'm inserting
has more than 30 columns....the insert code is huge...and I wouldn't
like to copy the insert for just one column of difference.

--CELKO--
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, because depending the
state the Job Order will be saved....will depend which column
(DraftDate, ConfirmedDate, etc) it will have to insert the current
date.

Hugo:
I don't want to select a dynamic value... the value will be always the
current date, I need to dinamically specify in which column I will
insert the current date

Best Regards
Fabio Cavassini

[Hugo Kornelis]

On 22 Jan 2006 15:12:16 -0800, ca********@gmail.com wrote:

(snip)

Yes, this would be a solution....but the table in which I'm inserting
has more than 30 columns....the insert code is huge...and I wouldn't
like to copy the insert for just one column of difference.
Hi Fabio,

Hmmm. Maybe you could explain in some more detail what is the actual
business problem you're trying to solve. A parameter that governs in
which of 30 columns the current date has to be inserted sounds as if the
best solution would be a redesign of your table - but I can only say for
sure if I know more about your actual problem and your current table
structure.

(snip)Hugo:
I don't want to select a dynamic value... the value will be always the
current date, I need to dinamically specify in which column I will
insert the current date

I had used ellipsis as a placeholder for the value to delete. Now that I
know it's the current date, I can complete my proposed code. I've also
added a third column and ellipsis to show how you can extend this to as
many columns as you need.

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),
...

If called with @eotId equal to 1, this will create a row with
CURRENT_TIMESTAMP in the first column (OTFechaBorrador) and NULL in the
two (or more) other columns. If @eotId is 2, OTFechaAAsignar will be the
current datetime and the other columns are NULL. Etc, etc.

--
Hugo Kornelis, SQL Server MVP

[pb648174]

Do it in two steps: Insert the common column data first, then do an
update to the appropriate row/column based on the new entry and the
type.

Hugo Kornelis wrote:

On 22 Jan 2006 15:12:16 -0800, ca********@gmail.com wrote:

(snip)

Yes, this would be a solution....but the table in which I'm inserting
has more than 30 columns....the insert code is huge...and I wouldn't
like to copy the insert for just one column of difference.

Hi Fabio,

Hmmm. Maybe you could explain in some more detail what is the actual
business problem you're trying to solve. A parameter that governs in
which of 30 columns the current date has to be inserted sounds as if the
best solution would be a redesign of your table - but I can only say for
sure if I know more about your actual problem and your current table
structure.

(snip)

Hugo:
I don't want to select a dynamic value... the value will be always the
current date, I need to dinamically specify in which column I will
insert the current date

I had used ellipsis as a placeholder for the value to delete. Now that I
know it's the current date, I can complete my proposed code. I've also
added a third column and ellipsis to show how you can extend this to as
many columns as you need.

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),
...

If called with @eotId equal to 1, this will create a row with
CURRENT_TIMESTAMP in the first column (OTFechaBorrador) and NULL in the
two (or more) other columns. If @eotId is 2, OTFechaAAsignar will be the
current datetime and the other columns are NULL. Etc, etc.

--
Hugo Kornelis, SQL Server MVP

[Ellen K]

The problem is your database design. Instead of 30 columns for dates
for all the events applicable to the job, you should have two tables:
The first table has the job identification, a date column, and a column
for the event or event identifier.
The second table is the lookup table of events. Most people would set
this up with a numeric identifier as the primary key, and a description
column for the event description.

HTH

[Ellen K]

The problem is your database design. Instead of 30 columns for dates
for all the events applicable to the job, you should have two tables:
The first table has the job identification, a date column, and a column
for the event or event identifier.
The second table is the lookup table of events. Most people would set
this up with a numeric identifier as the primary key, and a description
column for the event description.

HTH

[cavassinif]

Thanks for all the replies,

I implemented it as Hugo sayed, with condition in the value:

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 haven't realized that in an insert...in fact all values are
modified....consequently I need to put the condition in the value.
The problem is your database design. Instead of 30 columns for dates
A parameter that governs in
which of 30 columns the current date has to be inserted sounds as if the
best solution would be a redesign of your table

I know..that there are many columns....but...the business model
requires it. In addition I don't like to have many tables in my
databases, it's too much simple to maintain a reduced (respecting
normal forms, of course) set of tables.

Best Regards
Fabio Cavassini