locked
table valued parameters versus table variables RRS feed

  • Question

  • User1904516115 posted

    what is the difference between table valued parameters and table variables?

    Saturday, August 27, 2016 5:21 PM

Answers

  • User283571144 posted

    Hi vinodkpasi,

    what is the difference between table valued parameters and table variables?

    As far as I know,there is no connection.

    Table variables:

    Table variables store a set of records; therefore it will be declared as variables rather than created with SQL DDL statements.

    More details, you could refer to follow codes:

    Here, we are creating a table variable ‘@Student’, as shown below:

    DECLARE @Student TABLE(
    S_ID INT PRIMARY KEY,
    StudName VARCHAR(50), 
    StudBranch VARCHAR(50))
    

    Now, if we want to insert some values, then:

    INSERT INTO @Student VALUES ( 1, 'Piyush','Computer-Science');

    Table-valued parameters:

    Table-valued parameters are declared by using user-defined table types. 

    You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

    More details, you could refer to follow codes:

    USE AdventureWorks2012;  
    GO  
      
    /* Create a table type. */  
    CREATE TYPE LocationTableType AS TABLE   
    ( LocationName VARCHAR(50)  
    , CostRate INT );  
    GO  
      
    /* Create a procedure to receive data for the table-valued parameter. */  
    CREATE PROCEDURE dbo. usp_InsertProductionLocation  
        @TVP LocationTableType READONLY  
        AS   
        SET NOCOUNT ON  
        INSERT INTO AdventureWorks2012.Production.Location  
               (Name  
               ,CostRate  
               ,Availability  
               ,ModifiedDate)  
            SELECT *, 0, GETDATE()  
            FROM  @TVP;  
            GO  
      
    /* Declare a variable that references the type. */  
    DECLARE @LocationTVP AS LocationTableType;  
      
    /* Add data to the table variable. */  
    INSERT INTO @LocationTVP (LocationName, CostRate)  
        SELECT Name, 0.00  
        FROM AdventureWorks2012.Person.StateProvince;  
      
    /* Pass the table variable data to a stored procedure. */  
    EXEC usp_InsertProductionLocation @LocationTVP;  
    GO

    Link:https://msdn.microsoft.com/zh-cn/library/bb510489.aspx  

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 28, 2016 2:29 AM