Stored Proc with multiple parameters

Question

I need a SP which can take some 24 input parameters to insert a record into a table. One way of sending multiple parameters is by using XML datatype. Any other best practice for sending multiple input parameter in SQL SP?

Any advise is appreciated!

Vasea · Accepted Answer · 2011-07-22 15:31:12Z

7

If you're inserting only a fixed number of records, than you can define 24 parameters in your SP. This way you can get some compile-time checking, also you can define not-null, null or a default value for each parameter for greater flexibility.

I woudn't use XML datatype unless I have variable number of arguments or I must simulate parameter arrays (like in insert multiple orderlines at the same time).

If you're using SQL Server 2008 or higher there is support for Table-Valued Parameters. You can check this link for using table-valued params with .NET SqlCient, too

edited Jul 22, 2011 at 15:31

answered Jul 22, 2011 at 10:18

Vasea

5,3512 gold badges29 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TimothyAWiseman · Accepted Answer · 2011-07-22 15:58:28Z

1

Vasile Bujac's answer is excellent and I agree with everything. But it may be worth adding that Sommarskog, a luminary MVP, has some very good articles on mimicing an array in SQL Server that may be very applicable to your situation. You can find them here: http://www.sommarskog.se/arrays-in-sql.html

answered Jul 22, 2011 at 15:58

TimothyAWiseman

15k12 gold badges42 silver badges49 bronze badges

Collectives™ on Stack Overflow

Stored Proc with multiple parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related