Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a store
d
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

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

Conditional Column Name On Insert

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--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--

cavassinif@.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 ...|||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.|||On 20 Jan 2006 11:49:20 -0800, cavassinif@.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|||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|||On 22 Jan 2006 15:12:16 -0800, cavassinif@.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|||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, cavassinif@.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|||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|||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|||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