How to design SP with variable number of params?

I have a form that allows a user to update contact info. For example:
First name
Last name
PO Box
City
State
There are actually more fields (up to 50) but I have only used five for
simplicity. Sometimes a user will submit an update for all fields.
However, for a web service, some one may only send the First Name or any
other one field. In that case, is it better to design an SP for each case?
I see that having scaling issues.
Another approach is to design one SP with many conditionals (50). Both
approaches are inefficient. What is a better way?
Thanks,
BrettSpecify default values for your parameters. I.e.,
CREATE PROCEDURE sample
@.paramLast VARCHAR(30) = NULL, -- NULL default value
@.paramFirst VARCHAR(30) = NULL, -- NULL default value
@.paramPoBox VARCHAR(30) = NULL,
@.paramCity VARCHAR(30) = '', -- Empty string default value
@.paramState CHAR(2) = 'NY' -- 'NY' default value
It will be a little tedious for 50 fields, but will allow you to not specify
parameters on calling.
"Brett" <no@.spam.net> wrote in message
news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a form that allows a user to update contact info. For example:
> First name
> Last name
> PO Box
> City
> State
> There are actually more fields (up to 50) but I have only used five for
> simplicity. Sometimes a user will submit an update for all fields.
> However, for a web service, some one may only send the First Name or any
> other one field. In that case, is it better to design an SP for each
> case? I see that having scaling issues.
> Another approach is to design one SP with many conditionals (50). Both
> approaches are inefficient. What is a better way?
> Thanks,
> Brett
>|||Michael C# wrote:
> Specify default values for your parameters. I.e.,
> CREATE PROCEDURE sample
> @.paramLast VARCHAR(30) = NULL, -- NULL default value
> @.paramFirst VARCHAR(30) = NULL, -- NULL default value
> @.paramPoBox VARCHAR(30) = NULL,
> @.paramCity VARCHAR(30) = '', -- Empty string default
> value @.paramState CHAR(2) = 'NY' -- 'NY' default
> value
> It will be a little tedious for 50 fields, but will allow you to not
> specify parameters on calling.
>
I'm not sure that will work for the OP for updating.
Specify all updatable values in the parameter list. 50 is a lot, and I
might question the number of attributes on the underlying table. Unless
you're dealing with more than one table and could break up the updates
in a meaningful way.
David Gugick
Imceda Software
www.imceda.com|||Commonly we would just update all data on an update in the stored procedure
unless there is a great reason not to. You could do something like:
CREATE PROCEDURE TABLE_UPDATE
@.LastName VARCHAR(30) = NULL,
@.FirstName VARCHAR(30) = NULL,
as
update table
set lastName = coalesce(@.lastName, lastName),
firstName = coalesce(@.firstName, firstName)
go
Then if you call it with table_update @.firstName ='Bob'
The current value of lastName will be used, and the new value for
@.firstName.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Brett" <no@.spam.net> wrote in message
news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a form that allows a user to update contact info. For example:
> First name
> Last name
> PO Box
> City
> State
> There are actually more fields (up to 50) but I have only used five for
> simplicity. Sometimes a user will submit an update for all fields.
> However, for a web service, some one may only send the First Name or any
> other one field. In that case, is it better to design an SP for each
> case? I see that having scaling issues.
> Another approach is to design one SP with many conditionals (50). Both
> approaches are inefficient. What is a better way?
> Thanks,
> Brett
>|||there is one thing that bothers me with this approach (regardles of number
of fields). the thing is that on update, event if the value for the column
is unchanged, the constraints are being checked all the same. eg, if there
is a foreign key constraint, updating a fk column (with the same value, thus
in fact not updating at all) will cause a lookup in the referenced table,
which is absolutely unnecessary, imho. but the alternatives - dynamically
constructing the update statement, or creating a separate statement for
every combination of params - make even less sense.
any thoughts?
dean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23sEA5TWGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Commonly we would just update all data on an update in the stored
procedure
> unless there is a great reason not to. You could do something like:
> CREATE PROCEDURE TABLE_UPDATE
> @.LastName VARCHAR(30) = NULL,
> @.FirstName VARCHAR(30) = NULL,
> as
> update table
> set lastName = coalesce(@.lastName, lastName),
> firstName = coalesce(@.firstName, firstName)
> go
> Then if you call it with table_update @.firstName ='Bob'
> The current value of lastName will be used, and the new value for
> @.firstName.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Brett" <no@.spam.net> wrote in message
> news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||This seems to be the best approach of the posts here. I see there probably
isn't a way to get around conditionals for NULL checks. coalesce is a type
of conditional but probably better than using multiple IF statements
correct?
Thanks,
Brett
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23sEA5TWGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Commonly we would just update all data on an update in the stored
> procedure unless there is a great reason not to. You could do something
> like:
> CREATE PROCEDURE TABLE_UPDATE
> @.LastName VARCHAR(30) = NULL,
> @.FirstName VARCHAR(30) = NULL,
> as
> update table
> set lastName = coalesce(@.lastName, lastName),
> firstName = coalesce(@.firstName, firstName)
> go
> Then if you call it with table_update @.firstName ='Bob'
> The current value of lastName will be used, and the new value for
> @.firstName.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Brett" <no@.spam.net> wrote in message
> news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%2359VcIWGFHA.524@.TK2MSFTNGP14.phx.gbl...
> Michael C# wrote:
> I'm not sure that will work for the OP for updating.
>
Why not? Here's an example of a stored procedure, with a variable number of
params, that updates a table.
--Create Table and Primary Key
CREATE TABLE [dbo].[Table1] (
[IDNum] [int] NOT NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[IDNum]
) ON [PRIMARY]
GO
--Populate table
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (0, 'Jetson',
'George')
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (1, 'Flintstone',
'Fred')
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (2, 'Rubble',
'Barney')
GO
--Create stored procedure with variable number of parameters
CREATE PROCEDURE usp_UpdateRecord
@.paramID INT,
@.paramLast VARCHAR(50) = NULL,
@.paramFirst VARCHAR(50) = NULL
AS
UPDATE Table1 SET LastName = @.paramLast
WHERE IDNum = @.paramID
AND @.paramLast IS NOT NULL
UPDATE Table1 SET FirstName = @.paramFirst
WHERE IDNum = @.paramID
AND @.paramFirst IS NOT NULL
GO
--Now call the stored procedure with a variable
--number of parameters each time
EXEC usp_UpdateRecord @.paramID = 0, @.paramLast = 'Johnson'
EXEC usp_UpdateRecord @.paramID = 1, @.paramFirst = 'Wilma'
EXEC usp_UpdateRecord @.paramID = 2, @.paramLast = 'Public', @.paramFirst =
'John'
GO

> Specify all updatable values in the parameter list. 50 is a lot, and I
> might question the number of attributes on the underlying table. Unless
> you're dealing with more than one table and could break up the updates in
> a meaningful way.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I personally would not recommend you design a procedure to perform up to
50 distinct updates to update a single row in the table. Seems more work
and overhead than a single update to me.
David G.|||Are you talking about the overhead incurred when typing in the code once, or
the overhead incurred each time you UPDATE 50 fields in order to change one?
Michael C.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eunWozbGFHA.3112@.tk2msftngp13.phx.gbl...
>I personally would not recommend you design a procedure to perform up to 50
>distinct updates to update a single row in the table. Seems more work and
>overhead than a single update to me.
> --
> David G.
>|||Michael C# wrote:
> Are you talking about the overhead incurred when typing in the code
> once, or the overhead incurred each time you UPDATE 50 fields in
> order to change one?
> Michael C.
I just mean the possibly running up to 50 individual updates to satisfy
what a single update can do. Plus, the implementation does not allow you
to return a column value to NULL, if needed.
My only real point here is issuing a single update and supplying all
parameters is generally the easiest, most maintainable, and safest
implementation. If the OP has a component in ASP.net or his/her
fat-client app that automates the execution of the update, then he only
has to write it once.
David G.