Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to enable or disable a condition in a query

7 posts in General Discussion Last posting was on 2011-03-08 22:30:23.0Z
Jose Manuel Espinoza Posted on 2011-03-07 17:43:59.0Z
From: Jose Manuel Espinoza <jmespinoza@seicom.com.mx>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; es-ES; rv:1.9.2.7) Gecko/20100713 Thunderbird/3.1.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: How to enable or disable a condition in a query
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 110307-0, 07/03/2011), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d75195f$1@forums-1-dub>
Date: 7 Mar 2011 09:43:59 -0800
X-Trace: forums-1-dub 1299519839 10.22.241.152 (7 Mar 2011 09:43:59 -0800)
X-Original-Trace: 7 Mar 2011 09:43:59 -0800, vip152.sybase.com
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30025
Article PK: 79254

Hi,

I need to create a stored procedure that will affect few columns in a
table given some conditions. However, the rows that are going to be
affected are determined by a column or variable(let's call it C). The
matter is I can't figure out how to do this update in one single query.
If the column C has value then I will update all the rows which D
column equals C. Otherwise I need to affect all the rows, no matter what
value D has. For example:

update my_table
Set A = @value
Where my_table.D = @C

--the other case would be
update my_table
Set A = @value

--Or
update my_table
Set A = @value
Where my_table.D <> @C


In other words, C can only have 3 values: 'A', 'B' or 'C'. if C is
empty, then I'd update every row, otherwise only those rows that match C.

Any suggestion?


Bret Halford Posted on 2011-03-07 18:41:25.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.14) Gecko/20110221 Thunderbird/3.1.8
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to enable or disable a condition in a query
References: <4d75195f$1@forums-1-dub>
In-Reply-To: <4d75195f$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d7526d5$1@forums-1-dub>
Date: 7 Mar 2011 10:41:25 -0800
X-Trace: forums-1-dub 1299523285 10.22.241.152 (7 Mar 2011 10:41:25 -0800)
X-Original-Trace: 7 Mar 2011 10:41:25 -0800, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30026
Article PK: 79255


On 3/7/2011 10:43 AM, Jose Manuel Espinoza wrote:
> Hi,
>
> I need to create a stored procedure that will affect few columns in a
> table given some conditions. However, the rows that are going to be
> affected are determined by a column or variable(let's call it C). The
> matter is I can't figure out how to do this update in one single query.
> If the column C has value then I will update all the rows which D column
> equals C. Otherwise I need to affect all the rows, no matter what value
> D has. For example:
>
> update my_table
> Set A = @value
> Where my_table.D = @C
>
> --the other case would be
> update my_table
> Set A = @value
>
> --Or
> update my_table
> Set A = @value
> Where my_table.D <> @C
>
>
> In other words, C can only have 3 values: 'A', 'B' or 'C'. if C is
> empty, then I'd update every row, otherwise only those rows that match C.
>
> Any suggestion?

Why is it important to do this with a single statement?

Your description isn't terribly clear.
Do you mean "update every row where c is null"
or "update every row with the input value if there exists any row where
c is null"

I think (this might be what you are looking for
(I've done a little renaming to make a distinction between
column names, parameter/variable names, and literal values):

create procedure myproc (@value_input char)
as
update mytable
set col_A = @value_input
where
col_C is null
or (
col_C is not null
and col_D = "value_C"
)


Jose Manuel Espinoza Posted on 2011-03-07 22:16:24.0Z
From: Jose Manuel Espinoza <jmespinoza@seicom.com.mx>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; es-ES; rv:1.9.2.7) Gecko/20100713 Thunderbird/3.1.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to enable or disable a condition in a query
References: <4d75195f$1@forums-1-dub> <4d7526d5$1@forums-1-dub>
In-Reply-To: <4d7526d5$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Antivirus: avast! (VPS 110307-0, 07/03/2011), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d755938$1@forums-1-dub>
Date: 7 Mar 2011 14:16:24 -0800
X-Trace: forums-1-dub 1299536184 10.22.241.152 (7 Mar 2011 14:16:24 -0800)
X-Original-Trace: 7 Mar 2011 14:16:24 -0800, vip152.sybase.com
Lines: 122
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30029
Article PK: 79257

Bret,

I'm really sorry I couldn't make it clearer. Thanks for your effort :).

Here I go again, now with an example:


my_table
ID qty reference other
1 101 'A' 12
2 35 'B' 10
3 28 'A' 12
4 54 'C' 11
5 89 'B' 12


I have to do an update on "qty" based on some calculations and user
input(no nulls allowed in any field, BTW):

Create procedure My_sp
( @param char(1), @other_parm int)
begin
update my_table
Set qty = <calculation here>
Where reference= @param
and other = @other_parm
End

This is the shorter way. However, this same SP must be capable of
updating either any row matching the column "reference" with @param or
any row if @param = ''. Then, I'd do something like this:

Create procedure My_sp_if
( @param char(1), @other_parm int)
begin
if @param <> ''
update my_table
Set qty = <calculation here>
Where reference= @param
and other = @other_parm
else --@param is not used:
update my_table
Set qty = <calculation here>
Where other = @other_parm
End


Okay, I know this solution is quite simple. But I have to do more than
15 updates(different columns/calculations). That would mean that I have
to repeat the same code if @param = '' or if it isn't.(then, if I need
to make a change in future, I'd have to repeat that change 30 times :s )

I've thinking of Dynamic SQL, however I have no idea if it really would
affect performance.

Oh! And I'm asking about enabling/disabling becasue I once saw a
condition like this:

( table.a_field = '1' OR @param = 1 )

and it worked :s. If @param = 1 then it return all the rows with
table.a_field = '1', else would bring all those rows with table.a_field
with any other value(maybe I'm confused?)


Thanks again.


El 07/03/2011 12:41 p.m., Bret Halford escribió:

> On 3/7/2011 10:43 AM, Jose Manuel Espinoza wrote:
>> Hi,
>>
>> I need to create a stored procedure that will affect few columns in a
>> table given some conditions. However, the rows that are going to be
>> affected are determined by a column or variable(let's call it C). The
>> matter is I can't figure out how to do this update in one single query.
>> If the column C has value then I will update all the rows which D column
>> equals C. Otherwise I need to affect all the rows, no matter what value
>> D has. For example:
>>
>> update my_table
>> Set A = @value
>> Where my_table.D = @C
>>
>> --the other case would be
>> update my_table
>> Set A = @value
>>
>> --Or
>> update my_table
>> Set A = @value
>> Where my_table.D <> @C
>>
>>
>> In other words, C can only have 3 values: 'A', 'B' or 'C'. if C is
>> empty, then I'd update every row, otherwise only those rows that match C.
>>
>> Any suggestion?
>
>
> Why is it important to do this with a single statement?
>
> Your description isn't terribly clear.
> Do you mean "update every row where c is null"
> or "update every row with the input value if there exists any row where
> c is null"
>
> I think (this might be what you are looking for
> (I've done a little renaming to make a distinction between
> column names, parameter/variable names, and literal values):
>
> create procedure myproc (@value_input char)
> as
> update mytable
> set col_A = @value_input
> where
> col_C is null
> or (
> col_C is not null
> and col_D = "value_C"
> )


Bret Halford Posted on 2011-03-07 23:45:20.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to enable or disable a condition in a query
References: <4d75195f$1@forums-1-dub> <4d7526d5$1@forums-1-dub> <4d755938$1@forums-1-dub>
In-Reply-To: <4d755938$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d756e10$1@forums-1-dub>
Date: 7 Mar 2011 15:45:20 -0800
X-Trace: forums-1-dub 1299541520 10.22.241.152 (7 Mar 2011 15:45:20 -0800)
X-Original-Trace: 7 Mar 2011 15:45:20 -0800, vip152.sybase.com
Lines: 146
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30030
Article PK: 79258

Thank you, this example is much clearer.

Perhaps by using isnull()? (note that this can be simplified a bit
if you pass @param NULL rather than a space to mean "all rows"


Create procedure
My_sp (
@param char(1),
@other_parm int
)
begin
update my_table
Set qty = <calculation here>
where
reference= isnull(nullif(@param,""), reference)
and other = @other_parm
End

On 3/7/2011 3:16 PM, Jose Manuel Espinoza wrote:
> Bret,
>
> I'm really sorry I couldn't make it clearer. Thanks for your effort :).
>
> Here I go again, now with an example:
>
>
> my_table
> ID qty reference other
> 1 101 'A' 12
> 2 35 'B' 10
> 3 28 'A' 12
> 4 54 'C' 11
> 5 89 'B' 12
>
>
> I have to do an update on "qty" based on some calculations and user
> input(no nulls allowed in any field, BTW):
>
> Create procedure My_sp
> ( @param char(1), @other_parm int)
> begin
> update my_table
> Set qty = <calculation here>
> Where reference= @param
> and other = @other_parm
> End
>
> This is the shorter way. However, this same SP must be capable of
> updating either any row matching the column "reference" with @param or
> any row if @param = ''. Then, I'd do something like this:
>
> Create procedure My_sp_if
> ( @param char(1), @other_parm int)
> begin
> if @param <> ''
> update my_table
> Set qty = <calculation here>
> Where reference= @param
> and other = @other_parm
> else --@param is not used:
> update my_table
> Set qty = <calculation here>
> Where other = @other_parm
> End
>
>
> Okay, I know this solution is quite simple. But I have to do more than
> 15 updates(different columns/calculations). That would mean that I have
> to repeat the same code if @param = '' or if it isn't.(then, if I need
> to make a change in future, I'd have to repeat that change 30 times :s )
>
> I've thinking of Dynamic SQL, however I have no idea if it really would
> affect performance.
>
> Oh! And I'm asking about enabling/disabling becasue I once saw a
> condition like this:
>
> ( table.a_field = '1' OR @param = 1 )
>
> and it worked :s. If @param = 1 then it return all the rows with
> table.a_field = '1', else would bring all those rows with table.a_field
> with any other value(maybe I'm confused?)
>
>
> Thanks again.
>
>
> El 07/03/2011 12:41 p.m., Bret Halford escribió:
>> On 3/7/2011 10:43 AM, Jose Manuel Espinoza wrote:
>>> Hi,
>>>
>>> I need to create a stored procedure that will affect few columns in a
>>> table given some conditions. However, the rows that are going to be
>>> affected are determined by a column or variable(let's call it C). The
>>> matter is I can't figure out how to do this update in one single query.
>>> If the column C has value then I will update all the rows which D column
>>> equals C. Otherwise I need to affect all the rows, no matter what value
>>> D has. For example:
>>>
>>> update my_table
>>> Set A = @value
>>> Where my_table.D = @C
>>>
>>> --the other case would be
>>> update my_table
>>> Set A = @value
>>>
>>> --Or
>>> update my_table
>>> Set A = @value
>>> Where my_table.D <> @C
>>>
>>>
>>> In other words, C can only have 3 values: 'A', 'B' or 'C'. if C is
>>> empty, then I'd update every row, otherwise only those rows that
>>> match C.
>>>
>>> Any suggestion?
>>
>>
>> Why is it important to do this with a single statement?
>>
>> Your description isn't terribly clear.
>> Do you mean "update every row where c is null"
>> or "update every row with the input value if there exists any row where
>> c is null"
>>
>> I think (this might be what you are looking for
>> (I've done a little renaming to make a distinction between
>> column names, parameter/variable names, and literal values):
>>
>> create procedure myproc (@value_input char)
>> as
>> update mytable
>> set col_A = @value_input
>> where
>> col_C is null
>> or (
>> col_C is not null
>> and col_D = "value_C"
>> )
>


Jose Manuel Espinoza Posted on 2011-03-08 22:30:23.0Z
From: Jose Manuel Espinoza <jmespinoza@seicom.com.mx>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; es-ES; rv:1.9.2.7) Gecko/20100713 Thunderbird/3.1.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to enable or disable a condition in a query
References: <4d75195f$1@forums-1-dub> <4d7526d5$1@forums-1-dub> <4d755938$1@forums-1-dub> <4d756e10$1@forums-1-dub>
In-Reply-To: <4d756e10$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Antivirus: avast! (VPS 110307-0, 07/03/2011), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d76adff$1@forums-1-dub>
Date: 8 Mar 2011 14:30:23 -0800
X-Trace: forums-1-dub 1299623423 10.22.241.152 (8 Mar 2011 14:30:23 -0800)
X-Original-Trace: 8 Mar 2011 14:30:23 -0800, vip152.sybase.com
Lines: 156
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30033
Article PK: 79261

Just what I needed!

Thanks!

Thanks to Harry and J, too.


El 07/03/2011 05:45 p.m., Bret Halford escribió:

> Thank you, this example is much clearer.
>
> Perhaps by using isnull()? (note that this can be simplified a bit
> if you pass @param NULL rather than a space to mean "all rows"
>
>
> Create procedure
> My_sp (
> @param char(1),
> @other_parm int
> )
> begin
> update my_table
> Set qty = <calculation here>
> where
> reference= isnull(nullif(@param,""), reference)
> and other = @other_parm
> End
>
>
>
> On 3/7/2011 3:16 PM, Jose Manuel Espinoza wrote:
>> Bret,
>>
>> I'm really sorry I couldn't make it clearer. Thanks for your effort :).
>>
>> Here I go again, now with an example:
>>
>>
>> my_table
>> ID qty reference other
>> 1 101 'A' 12
>> 2 35 'B' 10
>> 3 28 'A' 12
>> 4 54 'C' 11
>> 5 89 'B' 12
>>
>>
>> I have to do an update on "qty" based on some calculations and user
>> input(no nulls allowed in any field, BTW):
>>
>> Create procedure My_sp
>> ( @param char(1), @other_parm int)
>> begin
>> update my_table
>> Set qty = <calculation here>
>> Where reference= @param
>> and other = @other_parm
>> End
>>
>> This is the shorter way. However, this same SP must be capable of
>> updating either any row matching the column "reference" with @param or
>> any row if @param = ''. Then, I'd do something like this:
>>
>> Create procedure My_sp_if
>> ( @param char(1), @other_parm int)
>> begin
>> if @param <> ''
>> update my_table
>> Set qty = <calculation here>
>> Where reference= @param
>> and other = @other_parm
>> else --@param is not used:
>> update my_table
>> Set qty = <calculation here>
>> Where other = @other_parm
>> End
>>
>>
>> Okay, I know this solution is quite simple. But I have to do more than
>> 15 updates(different columns/calculations). That would mean that I have
>> to repeat the same code if @param = '' or if it isn't.(then, if I need
>> to make a change in future, I'd have to repeat that change 30 times :s )
>>
>> I've thinking of Dynamic SQL, however I have no idea if it really would
>> affect performance.
>>
>> Oh! And I'm asking about enabling/disabling becasue I once saw a
>> condition like this:
>>
>> ( table.a_field = '1' OR @param = 1 )
>>
>> and it worked :s. If @param = 1 then it return all the rows with
>> table.a_field = '1', else would bring all those rows with table.a_field
>> with any other value(maybe I'm confused?)
>>
>>
>> Thanks again.
>>
>>
>> El 07/03/2011 12:41 p.m., Bret Halford escribió:
>>> On 3/7/2011 10:43 AM, Jose Manuel Espinoza wrote:
>>>> Hi,
>>>>
>>>> I need to create a stored procedure that will affect few columns in a
>>>> table given some conditions. However, the rows that are going to be
>>>> affected are determined by a column or variable(let's call it C). The
>>>> matter is I can't figure out how to do this update in one single query.
>>>> If the column C has value then I will update all the rows which D
>>>> column
>>>> equals C. Otherwise I need to affect all the rows, no matter what value
>>>> D has. For example:
>>>>
>>>> update my_table
>>>> Set A = @value
>>>> Where my_table.D = @C
>>>>
>>>> --the other case would be
>>>> update my_table
>>>> Set A = @value
>>>>
>>>> --Or
>>>> update my_table
>>>> Set A = @value
>>>> Where my_table.D <> @C
>>>>
>>>>
>>>> In other words, C can only have 3 values: 'A', 'B' or 'C'. if C is
>>>> empty, then I'd update every row, otherwise only those rows that
>>>> match C.
>>>>
>>>> Any suggestion?
>>>
>>>
>>> Why is it important to do this with a single statement?
>>>
>>> Your description isn't terribly clear.
>>> Do you mean "update every row where c is null"
>>> or "update every row with the input value if there exists any row where
>>> c is null"
>>>
>>> I think (this might be what you are looking for
>>> (I've done a little renaming to make a distinction between
>>> column names, parameter/variable names, and literal values):
>>>
>>> create procedure myproc (@value_input char)
>>> as
>>> update mytable
>>> set col_A = @value_input
>>> where
>>> col_C is null
>>> or (
>>> col_C is not null
>>> and col_D = "value_C"
>>> )
>>
>


hy Posted on 2011-03-08 01:53:19.0Z
From: "hy" <nospam_harrylhy@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4d75195f$1@forums-1-dub> <4d7526d5$1@forums-1-dub> <4d755938$1@forums-1-dub>
In-Reply-To: <4d755938$1@forums-1-dub>
Subject: Re: How to enable or disable a condition in a query
Lines: 3
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="utf-8"; reply-type=response
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d758c0f@forums-1-dub>
Date: 7 Mar 2011 17:53:19 -0800
X-Trace: forums-1-dub 1299549199 10.22.241.152 (7 Mar 2011 17:53:19 -0800)
X-Original-Trace: 7 Mar 2011 17:53:19 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30031
Article PK: 79259

Jose, I met this problem before and finally I use CASE to solved this problem if I need to input parameter dynamically ;


update my_table
set qty = <calculation here>
where other = @other_parm
and case when @param <> '' (*1) then
case when reference = @param then
1
else
0
end
else
1
end > 0

*1: @param <> '', can change to 'isnull(len(rtrim(@param)),0) > 0' instead if need to trim the space

The above syntax is, if the @param does not exists, it always returns the TRUE condition (1 > 0), that is, it BYPASS this line for
checking, otherwise, check the condition again and return the value to compare that is > 0 or not.


HTH :)

Harry


"Jose Manuel Espinoza" <jmespinoza@seicom.com.mx> 在郵件張貼內容主旨 4d755938$1@forums-1-dub 中撰寫...

> Bret,
>
> I'm really sorry I couldn't make it clearer. Thanks for your effort :).
>
> Here I go again, now with an example:
>
>
> my_table
> ID qty reference other
> 1 101 'A' 12
> 2 35 'B' 10
> 3 28 'A' 12
> 4 54 'C' 11
> 5 89 'B' 12
>
>
> I have to do an update on "qty" based on some calculations and user input(no nulls allowed in any field, BTW):
>
> Create procedure My_sp
> ( @param char(1), @other_parm int)
> begin
> update my_table
> Set qty = <calculation here>
> Where reference= @param
> and other = @other_parm
> End
>
> This is the shorter way. However, this same SP must be capable of updating either any row matching the column "reference" with
> @param or any row if @param = ''. Then, I'd do something like this:
>
> Create procedure My_sp_if
> ( @param char(1), @other_parm int)
> begin
> if @param <> ''
> update my_table
> Set qty = <calculation here>
> Where reference= @param
> and other = @other_parm
> else --@param is not used:
> update my_table
> Set qty = <calculation here>
> Where other = @other_parm
> End
>
>
> Okay, I know this solution is quite simple. But I have to do more than 15 updates(different columns/calculations). That would mean
> that I have to repeat the same code if @param = '' or if it isn't.(then, if I need to make a change in future, I'd have to repeat
> that change 30 times :s )
>
> I've thinking of Dynamic SQL, however I have no idea if it really would affect performance.
>
> Oh! And I'm asking about enabling/disabling becasue I once saw a condition like this:
>
> ( table.a_field = '1' OR @param = 1 )
>
> and it worked :s. If @param = 1 then it return all the rows with table.a_field = '1', else would bring all those rows with
> table.a_field with any other value(maybe I'm confused?)
>
>
> Thanks again.
>
>
> El 07/03/2011 12:41 p.m., Bret Halford escribió:
>> On 3/7/2011 10:43 AM, Jose Manuel Espinoza wrote:
>>> Hi,
>>>
>>> I need to create a stored procedure that will affect few columns in a
>>> table given some conditions. However, the rows that are going to be
>>> affected are determined by a column or variable(let's call it C). The
>>> matter is I can't figure out how to do this update in one single query.
>>> If the column C has value then I will update all the rows which D column
>>> equals C. Otherwise I need to affect all the rows, no matter what value
>>> D has. For example:
>>>
>>> update my_table
>>> Set A = @value
>>> Where my_table.D = @C
>>>
>>> --the other case would be
>>> update my_table
>>> Set A = @value
>>>
>>> --Or
>>> update my_table
>>> Set A = @value
>>> Where my_table.D <> @C
>>>
>>>
>>> In other words, C can only have 3 values: 'A', 'B' or 'C'. if C is
>>> empty, then I'd update every row, otherwise only those rows that match C.
>>>
>>> Any suggestion?
>>
>>
>> Why is it important to do this with a single statement?
>>
>> Your description isn't terribly clear.
>> Do you mean "update every row where c is null"
>> or "update every row with the input value if there exists any row where
>> c is null"
>>
>> I think (this might be what you are looking for
>> (I've done a little renaming to make a distinction between
>> column names, parameter/variable names, and literal values):
>>
>> create procedure myproc (@value_input char)
>> as
>> update mytable
>> set col_A = @value_input
>> where
>> col_C is null
>> or (
>> col_C is not null
>> and col_D = "value_C"
>> )
>


J Posted on 2011-03-08 17:23:37.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: How to enable or disable a condition in a query
Reply-To: J@bogusemailAddress.com
Message-ID: <4d7665a6.2855843921@forums.sybase.com>
References: <4d75195f$1@forums-1-dub> <4d7526d5$1@forums-1-dub> <4d755938$1@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 8 Mar 2011 09:23:37 -0800
X-Trace: forums-1-dub 1299605017 10.22.241.152 (8 Mar 2011 09:23:37 -0800)
X-Original-Trace: 8 Mar 2011 09:23:37 -0800, vip152.sybase.com
Lines: 133
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30032
Article PK: 79260

On 7 Mar 2011 14:16:24 -0800, Jose Manuel Espinoza
<jmespinoza@seicom.com.mx> wrote:

I don't think you should be too concerned about using dynamic sql to
make the process more streamlined. Unless you are executing the
"update"s many times the cost of building a good plan and
simplification seems warranted.

Jay

>Bret,
>
>I'm really sorry I couldn't make it clearer. Thanks for your effort :).
>
>Here I go again, now with an example:
>
>
>my_table
>ID qty reference other
>1 101 'A' 12
>2 35 'B' 10
>3 28 'A' 12
>4 54 'C' 11
>5 89 'B' 12
>
>
>I have to do an update on "qty" based on some calculations and user
>input(no nulls allowed in any field, BTW):
>
>Create procedure My_sp
>( @param char(1), @other_parm int)
>begin
> update my_table
> Set qty = <calculation here>
> Where reference= @param
> and other = @other_parm
>End
>
>This is the shorter way. However, this same SP must be capable of
>updating either any row matching the column "reference" with @param or
>any row if @param = ''. Then, I'd do something like this:
>
>Create procedure My_sp_if
>( @param char(1), @other_parm int)
>begin
> if @param <> ''
> update my_table
> Set qty = <calculation here>
> Where reference= @param
> and other = @other_parm
> else --@param is not used:
> update my_table
> Set qty = <calculation here>
> Where other = @other_parm
>End
>
>
>Okay, I know this solution is quite simple. But I have to do more than
>15 updates(different columns/calculations). That would mean that I have
>to repeat the same code if @param = '' or if it isn't.(then, if I need
>to make a change in future, I'd have to repeat that change 30 times :s )
>
>I've thinking of Dynamic SQL, however I have no idea if it really would
>affect performance.
>
>Oh! And I'm asking about enabling/disabling becasue I once saw a
>condition like this:
>
>( table.a_field = '1' OR @param = 1 )
>
>and it worked :s. If @param = 1 then it return all the rows with
>table.a_field = '1', else would bring all those rows with table.a_field
>with any other value(maybe I'm confused?)
>
>
>Thanks again.
>
>
>El 07/03/2011 12:41 p.m., Bret Halford escribió:
>> On 3/7/2011 10:43 AM, Jose Manuel Espinoza wrote:
>>> Hi,
>>>
>>> I need to create a stored procedure that will affect few columns in a
>>> table given some conditions. However, the rows that are going to be
>>> affected are determined by a column or variable(let's call it C). The
>>> matter is I can't figure out how to do this update in one single query.
>>> If the column C has value then I will update all the rows which D column
>>> equals C. Otherwise I need to affect all the rows, no matter what value
>>> D has. For example:
>>>
>>> update my_table
>>> Set A = @value
>>> Where my_table.D = @C
>>>
>>> --the other case would be
>>> update my_table
>>> Set A = @value
>>>
>>> --Or
>>> update my_table
>>> Set A = @value
>>> Where my_table.D <> @C
>>>
>>>
>>> In other words, C can only have 3 values: 'A', 'B' or 'C'. if C is
>>> empty, then I'd update every row, otherwise only those rows that match C.
>>>
>>> Any suggestion?
>>
>>
>> Why is it important to do this with a single statement?
>>
>> Your description isn't terribly clear.
>> Do you mean "update every row where c is null"
>> or "update every row with the input value if there exists any row where
>> c is null"
>>
>> I think (this might be what you are looking for
>> (I've done a little renaming to make a distinction between
>> column names, parameter/variable names, and literal values):
>>
>> create procedure myproc (@value_input char)
>> as
>> update mytable
>> set col_A = @value_input
>> where
>> col_C is null
>> or (
>> col_C is not null
>> and col_D = "value_C"
>> )
>