Identifying specified parameters in stored procedures

[Jeff]

I am using SQL Server 2000. I have a table with, say, 20 columns. I
have one procedure which updates all 20 columns at once, accepting a
parameter for each column. However, I want to be able to pass any
combination of parameters and only update those columns if passed. So
I created the sp as something like

create update_t1
(
@col1 int = null,
@col2 int = null,
@col3 int = null,
....
@col20 int = null
)
as

update t1
set col1 = @col1,
col2 = @col2,
col3 = @col3,
.....
col20 = @col20

This way I can explicitly specify columns or not as I choose. For
example I could call "exec update_t1 @col1 = 23, @col4 = 49" to update
only the first and fourth column. Of course this will obviously
update the remaining columns to null. Is there any way to identify
within the procedure which parameters were actually specified? I
can't simply do a null check because the user could be updating the
value to be null. Is there any way for the procedure to know the
exact command that invoked it?

For example, if I called "exec update_t1 @col1 = 23, @col4 = 49" I
would want to know only col1 and col4 were specified. If I called
"exec update_t1 @col1 = 23, @col4 = 49, @col17 = null" I would want to
know that col1, col4 and col17 were specified, even though col17 was
set to the default of null.

[Erland Sommarskog]

Re: Identifying specified parameters in stored procedures

[posted and mailed, please reply in news]

Jeff (jsauri@capmark tech.com) writes:[color=blue]
> create update_t1
> (
> @col1 int = null,
> @col2 int = null,
> @col3 int = null,
> ...
> @col20 int = null
> )
> as
>
> update t1
> set col1 = @col1,
> col2 = @col2,
> col3 = @col3,
> ....
> col20 = @col20
>
> This way I can explicitly specify columns or not as I choose. For
> example I could call "exec update_t1 @col1 = 23, @col4 = 49" to update
> only the first and fourth column. Of course this will obviously
> update the remaining columns to null. Is there any way to identify
> within the procedure which parameters were actually specified? I
> can't simply do a null check because the user could be updating the
> value to be null. Is there any way for the procedure to know the
> exact command that invoked it?[/color]

As long as you don't want to set a column to NULL, you can easily
do this:

UPDATE t1
SET col1 = CASE WHEN @col1 IS NOT NULL THEN @col1 ELSE col1 END,
col2 = CASE WHEN @col2 IS NOT NULL THEN @col2 ELSE col2 END,
--

But if you also want to be able to set a column to NULL, there is
no way, since the procedure cannot tell whether the NULL was explicitly
specified or not. To do this, you need to add extra parameters that
specifies which columns to update.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Jeff]

Re: Identifying specified parameters in stored procedures

If there's no way to tell whether the parameter was explicitly set to
null or not, is there anyway to retrieve the actual command that
invoked the procedure, similar to if you were running SQL Profiler,
except from within the procedure itself? If so, I could glean the
information out of that.

I am currently requiring the front end to send a comma delimited list
of parameter names it asserts it is using (in a parameter called
@param_list) which works well. I validate the list of parameters by
checking SQL Server's data dictionary. But if the front end passes a
valid parameter name in that list without actually specifiying the
parameter in the call, then the column will be overwritten (i.e. exec
update_t1 @param_list='co l1, col2', @col1=10). So I'm trying to close
that loophole.

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns945594 ED4373Yazorman@ 127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> Jeff (jsauri@capmark tech.com) writes:[color=green]
> > create update_t1
> > (
> > @col1 int = null,
> > @col2 int = null,
> > @col3 int = null,
> > ...
> > @col20 int = null
> > )
> > as
> >
> > update t1
> > set col1 = @col1,
> > col2 = @col2,
> > col3 = @col3,
> > ....
> > col20 = @col20
> >
> > This way I can explicitly specify columns or not as I choose. For
> > example I could call "exec update_t1 @col1 = 23, @col4 = 49" to update
> > only the first and fourth column. Of course this will obviously
> > update the remaining columns to null. Is there any way to identify
> > within the procedure which parameters were actually specified? I
> > can't simply do a null check because the user could be updating the
> > value to be null. Is there any way for the procedure to know the
> > exact command that invoked it?[/color]
>
> As long as you don't want to set a column to NULL, you can easily
> do this:
>
> UPDATE t1
> SET col1 = CASE WHEN @col1 IS NOT NULL THEN @col1 ELSE col1 END,
> col2 = CASE WHEN @col2 IS NOT NULL THEN @col2 ELSE col2 END,
> --
>
> But if you also want to be able to set a column to NULL, there is
> no way, since the procedure cannot tell whether the NULL was explicitly
> specified or not. To do this, you need to add extra parameters that
> specifies which columns to update.[/color]

[Erland Sommarskog]

Re: Identifying specified parameters in stored procedures

Jeff (jsauri@capmark tech.com) writes:[color=blue]
> If there's no way to tell whether the parameter was explicitly set to
> null or not, is there anyway to retrieve the actual command that
> invoked the procedure, similar to if you were running SQL Profiler,
> except from within the procedure itself? If so, I could glean the
> information out of that.[/color]

In one word: don't.

You can use DBCC INPUTBUFFER. However, DBCC INPUTBUFFER is intended to
be a diagnostic tool, and not supposed to be used in application
development. DBCC INPUTBUFFER returns the SQL statement that was
passed to SQL Server. So this if this procedure was not called directly
from the application, you lose. You also lose, if the application
uses RPC to call the procedure - which it should - in this case, I
don't think DBCC INPUTBUFFER includes the parameter list. And you
also lose if the total command string is > 255 characters, because
that is as much DBCC INPUTBUFFER returns.

In short, this is at best an unreliable method.
[color=blue]
> I am currently requiring the front end to send a comma delimited list
> of parameter names it asserts it is using (in a parameter called
> @param_list) which works well. I validate the list of parameters by
> checking SQL Server's data dictionary. But if the front end passes a
> valid parameter name in that list without actually specifiying the
> parameter in the call, then the column will be overwritten (i.e. exec
> update_t1 @param_list='co l1, col2', @col1=10). So I'm trying to close
> that loophole.[/color]

You could generate dynamic SQL to compose the UPDATE statement, but
that would require the user to have direct UPDATE rights on the
table, which is in most cases not acceptable.

The approach that we use in the system I work with, is that the GUI
simply reads all about data from the table, and then writes back
all as well. If there are functions which only updates one or two
columns, there are usually special procedures for these.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp