Better way to build a stored proc for an INSERT...

I've built a stored procedure where I'm inserting a row into two tables.
Both tables have a number of columns - and so I have to pass a rather larger
number of parameters to the stored proc. Like follows
INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm1, @.cParm2,
... @.cParm25)
INSERT INTO MyTable2 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm26,
@.cParm27, ... @.cParm50)
For any one row, however, at least a third of the columns are going to be
NULL, based on the value of one of the columns (a category column).
There's no opportunity to modify the table structure - it is what it is.
What I have "works", but I'm curious if there's a way that doesn't involve
as many parameters.
Thanks,
KevinKevin@.test.com wrote:
> I've built a stored procedure where I'm inserting a row into two
> tables.
> Both tables have a number of columns - and so I have to pass a rather
> larger number of parameters to the stored proc. Like follows
> INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm1,
> @.cParm2, ... @.cParm25)
> INSERT INTO MyTable2 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm26,
> @.cParm27, ... @.cParm50)
>
> For any one row, however, at least a third of the columns are going
> to be NULL, based on the value of one of the columns (a category
> column).
> There's no opportunity to modify the table structure - it is what it
> is. What I have "works", but I'm curious if there's a way that
> doesn't involve as many parameters.
>
> Thanks,
> Kevin
Write separate stored procedures for each "insert" type. So, let's say
your table has three logical implementations (design-issues aside), you
can write three separate insert SPs that only require the user pass
those that are asked.
The other option is to use defaults on the parameters, so if they are
not passed they default to an appropriate value:
Create Proc Test
@.Param1 INT = NULL
@.Param2 INT = NULL
as
Exec dbo.Test @.Param2 = 5
Exec dbo.Test @.Param1 = 3
Exec dbo.Test 1, 3
Exec dbo.Test
You may have to add some validation to the SP in the case where a user
leaves out a logically incorrect number of columns.
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks!...you've given me some good ideas to seriously consider, especially
having 3 stored procs.
Kevin