By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

Identifying specified parameters in stored procedures

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
[posted and mailed, please reply in news]

Jeff (js****@capmarktech.com) writes:
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?


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a
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='col1, col2', @col1=10). So I'm trying to close
that loophole.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
[posted and mailed, please reply in news]

Jeff (js****@capmarktech.com) writes:
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?


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.

Jul 20 '05 #3

P: n/a
Jeff (js****@capmarktech.com) writes:
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.
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.
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='col1, col2', @col1=10). So I'm trying to close
that loophole.


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.