Updating a table variable

20-Sep-2019 07:36

Let's see this behavior with an example: --Creating table variable DECLARE @Test Table TABLE ( ID INT, Name NVARCHAR(40) ) --Creating temporary table CREATE TABLE #test Table ( ID INT, Name NVARCHAR(40) ) DECLARE @id INT = 0 BEGIN TRANSACTION SET @id = 5 INSERT INTO @Test Table (ID, Name) VALUES (1, 'Name1'), (2, 'Name2') INSERT INTO #test Table (ID, Name) VALUES (1, 'Name1'), (2, 'Name2') ROLLBACK --Selecting data after rollback SELECT @id AS '@id' SELECT * FROM @Test Table SELECT * FROM #test Table Thanks to this feature table variables can be used for auditing purposes.For example, when we are updating a table and the transaction is rolled back and we want to audit these uncommitted changes, we can store it in a table variable and after the rollback then insert this data into a table for auditing.Before passing a list of parameters to the procedure we should define the appropriate table type. User Role ( User Role ID INT IDENTITY(1,1), User ID INT, Role ID INT CONSTRAINT IX_User Role_User ID UNIQUE NONCLUSTERED (User ID ASC) ) INSERT INTO dbo. Role ID WHEN NOT MATCHED THEN INSERT (User ID, Role ID) VALUES (source. Role ID); END Take into account that table variables passed to a stored procedure are READONLY.User Role (User ID, Role ID) VALUES (1,2),(2,5),(3,2),(4,6) CREATE PROCEDURE usp Update User Role Mapping @p User Role List User Role READONLY AS BEGIN MERGE dbo. Table variables must be passed as READONLY and we can't perform DML operations (INSERT, UPDATE, DELETE) on a table variable in the body of stored procedure or function.Sometimes it's necessary to pass a list of values to stored procedures or functions.

However, they have some restrictions: they can't be altered after creation, statistics are not maintained, etc.

We can't use SELECT INTO or INSERT EXEC statements to initialize a table variable.

Table variables can be passed to stored procedures and functions only as READONLY. User ID --Is referenced using an alias Table variables are a good solution for working with small temporary data, also they allow us to pass a list of values to stored procedures and functions which makes some development needs much simpler.

Table variables can be used for working with small temporary data, for passing a list of values to stored procedures or functions, for auditing, etc.

In this tip we will illustrate how table variables behave in transactions, how to pass as a parameter to a stored procedure, how to create indexes on table variables (they can be created only in the table definition) and table variable limitations.User Role AS target USING @p User Role List AS source ON target. In the following code we illustrate the call of our stored procedure with a table-valued parameter: --Table before update SELECT * FROM dbo.