Search code examples
sql-server-2012user-defined-types

How to pass Value of user defined datatype in SQL Server 2012


I have created a user defined datatype in sql server as shown below.

CREATE TYPE MonthFees AS TABLE 
(
    MonthID INT, MonthName VARCHAR(20)
)

And i created a parameter for that datatype as shown,

@MinthFeeDetails MonthFees READONLY

now i don't know how to pass the value from sql management studio for that parameter.


Solution

  • From Documentation Something like this

    /* Declare a variable that references the type. */  
    DECLARE @mf AS MonthFees;  
    
    /* Add data to the table variable. */  
    INSERT INTO @mf (MonthID, MonthName)  
        SELECT MonthID, MonthName 
        FROM table_name;  
    
    /* Pass the table variable data to a stored procedure. */  
    EXEC usp_myprocedure @mf;