CODEWITHSUNDEEP
sql-servert-sqlsql-server-2019
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

Handle large amount of user default table types in stored procedure

We have some dynamic stored procedures that have very similar logic and they use bunch of user defined table type as input parameters. The common approach how we handle these types is:

CREATE PROCEDURE dbo.SomeProcedure
    @importantParam1 dbo.SomeType READONLY,
    ....
BEGIN
  
    DECLARE @nrOfImportantParam1 INT = 0;
    DECLARE @singleImportantParam1 NVARCHAR(35);
    SELECT @nrOfImportantParam1 = COUNT(*) FROM @importantParam1;
    IF (@nrOfImportantParam1 = 1)
        SELECT @singleImportantParam1 = [value] FROM @importantParam1;

    ....

    SET @joinQuery = 
    ...
    + CASE WHEN @nrOfImportantParam1 > 1 THEN 'INNER JOIN @importantParam1 t ON t.[value] = tbl.SomeKey' ELSE '' END
    ...

    SET @filterQuery = 
    ...
    + CASE WHEN @nrOfImportantParam1 = 2 THEN 'AND tbl.SomeKey = @singleImportantParam1' ELSE '' END
    
END

Every time we add new user defined table type we need to add new parameter and new logic to handle it. I would like to have some generic approach to handle this. Do you have any suggestions how to reduce such code duplication?

Most probably it should be some kind of stored procedure that will return 2 output params: 1 for join query snippet and another one for filter query snippet. However adding new user defined table type would require to add new case in this stored procedure + there could be problems with aliases and join keys as they might differ little bit in every SP.

UPDATE: The normal use case of procedure like that is to return filtered data from ~5 big tables. There are a lot of filters that could be passed to this SP and the most of them are optional. Some filters are using table value params to pass more than 1 value for single filter, for example: list of statuses, list of users and so on. Normally they have 1-5 values and the dynamic SQL is used mostly to eliminate kitchen sink problem which appears in constructions like: WHERE (some_col = @some_param OR @some_param IS NULL). With set of ~50 params this problem can be just huge.

Upvotes: 0

Views: 86

Answers (0)

Related Questions