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.

General approach to determine what parameters were actually passed to a procedure

3 posts in General Discussion Last posting was on 2009-06-16 10:07:45.0Z
Leonid Gvirtz Posted on 2009-06-15 18:40:23.0Z
From: Leonid Gvirtz <lgvirtz@yahoo.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: General approach to determine what parameters were actually passed to a procedure
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: <4a369597$1@forums-1-dub>
Date: 15 Jun 2009 11:40:23 -0700
X-Trace: forums-1-dub 1245091223 10.22.241.152 (15 Jun 2009 11:40:23 -0700)
X-Original-Trace: 15 Jun 2009 11:40:23 -0700, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27865
Article PK: 77115

I'm trying to rewrite a stored procedure that performs update of a
table. All values for fields in the SET clause are passed as procedure
parameters. I want to update only those columns that really should be
updated, i.e. only columns that their values are passed via procedure
parameters and not just blindly update all possible columns (the number
of columns to update can reach 70-80 and even more).

The problem is that I need to know what parameters have actually been
passed to the procedure. The traditional solution for this problem is to
define default values of NULL for non-mandatory parameters. Then, if the
parameter value is NULL then we can assume that the parameter wasn't
passed and, therefore, we don't need to update the corresponding column.
But, what happens if a user wants to update some column(s) with NULL
value? From inside the stored procedure, it will look just like the
corresponding parameter wasn't passed.

Although we can find a value that will never be used for a field and use
it as a default value for an input parameter of the procedure instead of
NULL, there are some cases when it is difficult to find such a value.

There are also additional ways to pass parameters to a stored procedure,
for example via temporary table or application context. In addition, it
is possible to pass an additional integer value with bit mask for all
input parameters (passed/not passed). It is also possible to define a
mandatory bit parameter for each non-mandatory parameter and use such
bit parameters to indicate if the parameter is passed or not. The
downside of all these approaches is that all of them are too much
complicated for such a simple task. Maybe I miss something here. Any
ideas are welcome.


Thanks in advance
Leonid Gvirtz


Carl Kayser Posted on 2009-06-16 10:07:45.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4a369597$1@forums-1-dub>
Subject: Re: General approach to determine what parameters were actually passed to a procedure
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a376ef1@forums-1-dub>
Date: 16 Jun 2009 03:07:45 -0700
X-Trace: forums-1-dub 1245146865 10.22.241.152 (16 Jun 2009 03:07:45 -0700)
X-Original-Trace: 16 Jun 2009 03:07:45 -0700, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27870
Article PK: 77119


"Leonid Gvirtz" <lgvirtz@yahoo.com> wrote in message
news:4a369597$1@forums-1-dub...
> I'm trying to rewrite a stored procedure that performs update of a table.
> All values for fields in the SET clause are passed as procedure
> parameters. I want to update only those columns that really should be
> updated, i.e. only columns that their values are passed via procedure
> parameters and not just blindly update all possible columns (the number of
> columns to update can reach 70-80 and even more).
>
> The problem is that I need to know what parameters have actually been
> passed to the procedure. The traditional solution for this problem is to
> define default values of NULL for non-mandatory parameters. Then, if the
> parameter value is NULL then we can assume that the parameter wasn't
> passed and, therefore, we don't need to update the corresponding column.
> But, what happens if a user wants to update some column(s) with NULL
> value? From inside the stored procedure, it will look just like the
> corresponding parameter wasn't passed.
>
> Although we can find a value that will never be used for a field and use
> it as a default value for an input parameter of the procedure instead of
> NULL, there are some cases when it is difficult to find such a value.
>
> There are also additional ways to pass parameters to a stored procedure,
> for example via temporary table or application context. In addition, it is
> possible to pass an additional integer value with bit mask for all input
> parameters (passed/not passed). It is also possible to define a mandatory
> bit parameter for each non-mandatory parameter and use such bit parameters
> to indicate if the parameter is passed or not. The downside of all these
> approaches is that all of them are too much complicated for such a simple
> task. Maybe I miss something here. Any ideas are welcome.
>
>
> Thanks in advance
> Leonid Gvirtz

"The downside of all these approaches is that all of them are too much
complicated for such a simple task. Maybe I miss something here. Any
ideas are welcome."

What is your goal? To minimize CPU resources? Apparently not. To minimize
logging volume? Possibly. Some other goal?

I do remember a killer situation with

update ABC
set col1 = new_val
where <simple condition>

The problem was that most rows qualified ... but the old/new values were
mostly identical. Lost and lots and losts of transaction log space used.
The fix was trivial and obvious (but configuration management delayed the
solution far, far too long in my opinion).

where <simple condition> and col1 <> new_value.

Thinking about your issue a "solution" would be to use execute immediate
with the set clauses generated only when the values differ. But first you
would have to select the current values and compare them with the SP
parameters. If it doesn't have to be "real time" and you want to use
(compratively speaking) a lot more CPU and IO resources to reduce logging
then that might be a possibility.


Sherlock, Kevin [TeamSybase] Posted on 2009-06-15 19:08:47.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a369597$1@forums-1-dub>
Subject: Re: General approach to determine what parameters were actually passed to a procedure
Lines: 42
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a369c3f$1@forums-1-dub>
Date: 15 Jun 2009 12:08:47 -0700
X-Trace: forums-1-dub 1245092927 10.22.241.152 (15 Jun 2009 12:08:47 -0700)
X-Original-Trace: 15 Jun 2009 12:08:47 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27866
Article PK: 77121

Given the conditions you put on the question:
How about making all of the parameters (var)char datatypes, and have the app
pass in a literal 'NULL' string for columns that need to be updated to null.
Then apply your logic to find which parameters were passed literal values,
and do the datatype conversion in your update statement.

"Leonid Gvirtz" <lgvirtz@yahoo.com> wrote in message
news:4a369597$1@forums-1-dub...
> I'm trying to rewrite a stored procedure that performs update of a table.
> All values for fields in the SET clause are passed as procedure
> parameters. I want to update only those columns that really should be
> updated, i.e. only columns that their values are passed via procedure
> parameters and not just blindly update all possible columns (the number of
> columns to update can reach 70-80 and even more).
>
> The problem is that I need to know what parameters have actually been
> passed to the procedure. The traditional solution for this problem is to
> define default values of NULL for non-mandatory parameters. Then, if the
> parameter value is NULL then we can assume that the parameter wasn't
> passed and, therefore, we don't need to update the corresponding column.
> But, what happens if a user wants to update some column(s) with NULL
> value? From inside the stored procedure, it will look just like the
> corresponding parameter wasn't passed.
>
> Although we can find a value that will never be used for a field and use
> it as a default value for an input parameter of the procedure instead of
> NULL, there are some cases when it is difficult to find such a value.
>
> There are also additional ways to pass parameters to a stored procedure,
> for example via temporary table or application context. In addition, it is
> possible to pass an additional integer value with bit mask for all input
> parameters (passed/not passed). It is also possible to define a mandatory
> bit parameter for each non-mandatory parameter and use such bit parameters
> to indicate if the parameter is passed or not. The downside of all these
> approaches is that all of them are too much complicated for such a simple
> task. Maybe I miss something here. Any ideas are welcome.
>
>
> Thanks in advance
> Leonid Gvirtz