Call Stored Procedure With A User Defined Table Type

Question

Hello Everybody
I am not sure how to do this, but I need to call a stored procedure that has some int, and string parameters, as well as a user defined table type.

--Type  
CREATE TYPE SomeType AS TABLE (  
     Value1 decimal,  
     Value2 decimal,  
     Message varchar(50)  
}  
  
--Stored Procedure  
EXEC    @SomeMessage = 'Hello',  
            @SomeMessage2 = 'World',  
            @SomeType  = ?????  
  
GO  

And then this would put the data into the database. I am not entirely sure how to populate the user defined type parameter, and I have looked around for answers, but I have had no luck in figuring a way to do this.

Answer 1

You don't populate the table type. You populate a table variable of that type:

   DECLARE @tablevar SomeType  
   INSERT @tablevar(Value1, Value2, Message)  
      VALUES (1, 90000, 'This is the old number'),  
             (2, 112, 'This is the new number')  
     
    --Stored Procedure  
    EXEC    @SomeMessage = 'Hello',  
                @SomeMessage2 = 'World',  
                @SomeType  = @tablevar  

By the way, beware that decimal is the same thing as decimal(18, 0), that is 18 digits with no decimal.