Search code examples
sql-serverstored-procedurestable-valued-parameters

Get all records if table valued parameter is null


I want to get all values of column if the table valued parameter is null otherwise only the matched records. I have tried this but it gives an error when the subquery returns more than one record:

ALTER PROCEDURE [dbo].[usp_MY_SP]
(
    @TVP ABCTableType readonly,
)
AS

    SELECT *

    FROM TABLE t

    WHERE
        t.Id IN(
            CASE WHEN (SELECT COUNT([Id]) FROM @TVP) > 0 
                THEN (SELECT [Id] FROM @TVP)
                ELSE (t.Id)
            END
        )

Solution

  • You can do it this way

    ALTER PROCEDURE [dbo].[usp_MY_SP]
    (
        @TVP ABCTableType readonly
    )
    AS
    
          SELECT * FROM t
          WHERE
              ((SELECT COUNT([Id]) FROM @TVP) = 0)
              OR
              t.Id IN (SELECT [Id] FROM @TVP)