Way too many Stored Procedure Parameters

[Sam Shrefler]

I have a database table that contains approx 150 columns per row. I
need to update all 150 columns at once. To create a stored procedure
will cause me to have to create 150 sqlParameters....Rows will only be
updated one at a time. Does anyone else have any thoughts on the best
way to handle this. The data is available to .NET in the form of a
typed transfer object with 150 data elements in it.

Thanks

 

[Cowboy \(Gregory A. Beamer\)]

Typed DataSet
O/R Mapping product (LLBLGen Pro)
Other code gen

If you are dealing with this many columns, the cost of buying and perhaps
building a code gen is less than hand typing.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************

 

[Nicholas Paldino [.NET/C# MVP]]

Sam,

Assuming you don't want to write a code-gen product, I would do the
following (this assumes you are using SQL Server).

Start by creating a typed data set, which will create the
insert/update/delete/select statements for the table.

Create a test program to insert one record into the table with the typed
data set.

Run SQL Server profiler on the database in SQL Server (filter by your
machine as the client) and make sure you get the batch statement begin, and
the statement begin events. You also want the statement text in the trace.

Run your program. You will see something like "sp_execute" along with a
parameterized statement (your insert, update, or delete statement) along
with a list of parameters and types.

From this statement, you should be able to generate your stored
procedure easily on the database. It will require a bunch of copy and
pasting, but it's better than having to do the field and type discovery by
hand.

Once you have your stored procedure, you can just drag it into VS.NET
and it will create a wrapper for you to call it by, or you can use the Data
Access Application Block to make the call for you, where it generates all
the SqlParameter instances for you, and sets them up correctly.

Hope this helps.

 

[sloan]

Or you can have 1 input parameter.
@xml text

If you're willing to accept the small performance penalty of using
OPENXML

See
http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp

While I'm passing in "query filters", you can use the same idea to populate
idea.
Once data is in a @variableTable or #tempTable, you can do whatever you want
with it.

The idea kinda comes from here:
http://support.microsoft.com/kb/315968


The beauty of xml is that ,, if you ever add a new column ... you never
change the signature.

 

[mirko]

I have a database table that contains approx 150 columns per row. I
need to update all 150 columns at once. To create a stored procedure
will cause me to have to create 150 sqlParameters....Rows will only be
updated one at a time. Does anyone else have any thoughts on the best
way to handle this. The data is available to .NET in the form of a
typed transfer object with 150 data elements in it.

Thanks

try CodeSmith, it will create stored procedures for you

 

[Otavio Decio]

I feel your pain. Are you actually required to use stored procedures? Is
there any speed concerns or triggers involved with your update? Otherwise I
would use plain old parameterized statements (better yet, automatically
generate them).

Otavio

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

I have a database table that contains approx 150 columns per row. I
need to update all 150 columns at once. To create a stored procedure
will cause me to have to create 150 sqlParameters....Rows will only be
updated one at a time. Does anyone else have any thoughts on the best
way to handle this. The data is available to .NET in the form of a
typed transfer object with 150 data elements in it.

Thanks

I would first like to ask you: why on earth do you have 150 fields in a
database table? It looks that the real problem is the database design.