Q:

T-SQL - stored procedure that would handle an unknown number of parameters of unknown types

I have a large number of stored procedures that basically do the same thing: get a list of values as parameters, then go to some table and SELECT all the rows WHERE these values match.
I would like to be able to have a single stored procedure that would be able to handle all these cases.
How would you tackle this?

A:

Don't do it. You are going to throw all database performance down the drain. T-SQL and code reuse don't mix.
What you can do though is to have all these stored procedures generated automatically. There are many tools that do this, and you can easily roll your own: use SQL itself to extract the table definition into XML, then use XSLT to transform this into T-SQL and generate the proc. It can be automated into your project build and contiguous integration process. This way you get the best of both worlds: strongly types, efficient T-SQL code, yet an agile, DRY and maintainable process that makes it easy to re-write all those 100s of procs with one single change.

A:

You can try to use XML based approach for your problem. Send an XML as an input parameter. Convert the xml into a table. From the table form dynamic query and execute it in order to achieve your desired functionality.  See the below example. In this example Departments is a table which contains 3 columns pkDepartmentId (int), DepartmentName (varchar) and BuildingNumber (int). Using this approach you can send n Number of parameters and their values as input parameters.
pkDepartmentId  DepartmentName  BuildingNumber
1   Electronics and Communication   1
2   Computer Science    2
3   Instrumentation and Technology  4
--EXEC TestProc '<Parameters>
--                  <Param>
--                      <ColumnName>pkDepartmentId</ColumnName>
--                      <ColumnValue>1</ColumnValue>        
--                  </Param>
--                  <Param>
--                      <ColumnName>DepartmentName</ColumnName>
--                      <ColumnValue>Electronics and Communication</ColumnValue>        
--                  </Param>
--                  <Param>
--                      <ColumnName>BuildingNumber</ColumnName>
--                      <ColumnValue>1</ColumnValue>        
--                  </Param>    
--              </Parameters>'

CREATE PROCEDURE TestProc
@parameters XML
AS
BEGIN
    DECLARE @temp1 TABLE
    (
          ColName VARCHAR(100)
        , ColVal VARCHAR(4000)      
    )

    INSERT INTO @temp1
    SELECT    Params.Col.value('ColumnName[1]', 'VARCHAR(50)') ColName
            , Params.Col.value('ColumnValue[1]', 'VARCHAR(50)') ColVal              FROM   @parameters.nodes('//Parameters/Param') Params(Col)

    DECLARE @sql VARCHAR(4000)

    SET @sql = 'SELECT * FROM Departments WHERE '

    SELECT @sql = @sql  + ColName + ' = ''' + ColVal  + ''' AND '
    FROM @temp1

    -- Trim last AND
    SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 3)

    PRINT @sql                  

    EXEC (@sql)             

END

On executing this proc following result is obtained
pkDepartmentId  DepartmentName  BuildingNumber
1   Electronics and Communication   1

