CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
rapt
rapt

Reputation: 12220

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?

Upvotes: 1

Views: 3483

Answers (4)

Daniel
Daniel

Reputation: 1044

How about using table valued parameters TVP (provided your SQL Server version is >= 2008) ? This doesn't solve the "unknown types" part though. I guess you would have to use varchar(max) or even nvarchar(max) and then cast it as necessary...

See: http://www.sommarskog.se/arrays-in-sql-2008.html

For older versions of SQL Server, see also:

Upvotes: 1

Manjunatha Gouli
Manjunatha Gouli

Reputation: 36

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

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294247

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.

Upvotes: 4

Diego
Diego

Reputation: 36146

default parameters:

create procedure MyPRoc1
(
@param1 int,
@param2 int =1
)
as
begin
    --code
end

you can call it using 

exec MyPRoc1 10

or

exec MyPRoc1 1,1

Upvotes: 0

Related Questions