How to refactor T-SQL stored procedure encapsulating it's parameters to a class

On my SQL Server 2008 I have a stored procedure with a large number of parameters.

The first part of them is used in every call and parameters from the second part are used rarely. And I can't move开发者_如何学Python the logic to two different stored procedures.

Is there a way to encapsulate all this parameters to a class or struct and pass it as a stored procedure parameter?

Can I use SQL CLR. Are there other ways?

Have you tried Table Valued Parameters?

Actually, I wont use CLR function for anything which can be done effectively and easily in t-SQL. For example, last time I used CLR function is for updating a column based on some complex regex which I found pretty hard to do in t-SQL.

It sounds like your concern is with the need to specify values for each parameter. Would it work for you to just assign default values to the parameters that aren't used as often (so you don't need to pass every parameter each time the proc is called)?

A CLR type could be an option (as could XML) but I'm not sure it would be a good idea to go down that route.

If the volume of parameters is causing you problems in your application you could try one of the following two methods:

1) pass in a single parameter of XML data type that contains all of the parameters data. you could then parse out what you need. See xml (Transact-SQL).

2) create a table parameter, see Table-Valued Parameters (Database Engine), where the table is:

ParameterName sysname
DataValue     sql_variant

With either of these methods, you'd more than likely need to expand them out into local variables to use them again.