CODEWITHSUNDEEP
sqlsql-server
cronos2546
cronos2546

Reputation: 1106

Can a table valued parameter to stored procedure have default values

Suppose that I have a two column User Defined Table type 

CREATE TYPE [Schema].[Type] AS TABLE (
    [Name]         NVARCHAR (100) NULL
    [Value]         int NULL
);

Further suppose that I have stored procedure that I pass the table type to

CREATE PROCEDURE [Schema].[sp_SomeProcedure]
@TVP [Type] READONLY
AS
SELECT 
[Name]
,1 + [Value]
FROM
@TVP

Can I default the value of @TVP to be 

(SELECT 'John', 1)

Upvotes: 4

Views: 6829

Answers (2)

Kimber
Kimber

Reputation: 71

You can use the 'DEFAULT' keyword for the TVP when calling the procedure. That will pass an empty table of the type.

Example - if the TVP is the second parameter passed to a procedure:

    Exec myProcedure (intParam, DEFAULT)

Upvotes: 7

M.Ali
M.Ali

Reputation: 69564

SQL Server does not allow to make a TVP an optional parameter but setting some sort of default value to it, if you have a TVP in your procedure you will need to pass a TVP at runtime.

But there is a way around to it, add another parameter to your procedure which decides the behavior of TVP in your procedure.

Mind you in any case you would need to pass a TVP to your procedure at runtime. 

CREATE PROCEDURE [Schema].[sp_SomeProcedure]
   @TVP_Default BIT = 1 
  ,@TVP [dbo].[Test_Type] READONLY
AS
BEGIN
    DECLARE @tempTable TABLE (
                            [Name]  NVARCHAR(100) NULL
                           ,[Value] int NULL
                           );

-- If @TVP_Default = 1 default values will be populated 
-- else values passed to TVP will be used    
IF (@TVP_Default = 1)
  BEGIN 
    INSERT INTO @tempTable([Name] , [Value])
    SELECT 'John' , 1
  END
ELSE
  BEGIN 
    INSERT INTO @tempTable([Name] , [Value])
    SELECT   [Name]
          ,1 + [Value]
    FROM @TVP
  END

  /*   
        rest of the code

       use @tempTable instead of the TVP in rest of the code
  */

END

Upvotes: 3

Related Questions