CODEWITHSUNDEEP
sql-serverstored-procedurestable-valued-parameters
Ali Shahzad
Ali Shahzad

Reputation: 5332

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
        )

Upvotes: 2

Views: 839

Answers (1)

Szymon
Szymon

Reputation: 43023

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)

Upvotes: 3

Related Questions