Trying to insert values into Table Variable in Dynamic Sql

indian143

Hi all, I am trying to insert values into Table variable within Dynamic Sql, I am able to select values from Table variable but not able to insert values in to the Table variable. Here is how I am selecting values from Table variable

CREATE TYPE IntegerTableType AS TABLE (ID INT);
go

DECLARE @TempVehicles IntegerTableType;

INSERT @TempVehicles
values (1);

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT *
FROM @TempVehicles;';

EXECUTE SP_EXECUTESQL @SQL,N'@TempVehicles IntegerTableType READONLY',
@TempVehicles;

IF EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='IntegerTableType')
DROP TYPE [dbo].IntegerTableType;
GO

But when I am trying to insert values as below, I am not able to insert.

IF NOT EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='Results')
CREATE TYPE Results AS TABLE (TableName nvarchar(500), ColumnName nvarchar(500))
GO

Declare @Tables table (Id int identity(1,1), TableName nvarchar(500))
declare @Columns table (Id int identity(1,1), ColumnName nvarchar(500))

declare @Results AS Results

declare @IdMin1 int, @IdMax1 int, @IdMin2 int, @IdMax2 int, @col varchar(500), @cmd varchar(max), @TableName nvarchar(500)

insert into @Tables(TableName)
SELECT t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo'
order by t.name

select @IdMin1=min(Id), @IdMax1=max(Id) from @Tables
while(@IdMin1<=@IdMax1)
begin
set @TableName = (select top 1 TableName from @Tables where Id = @IdMin1)

delete from @Columns
insert into @Columns(ColumnName)
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object\_ID = c.Object\_ID
WHERE t.Name = @TableName
order by c.name

--insert into @Results (TableName) values(@TableName)

select @IdMin2=min(Id), @IdMax2=max(Id) from @Columns
while(@IdMin2<=@IdMax2)
begin
	set @col=''
	select @col=ColumnName  from @Columns where Id=@IdMin2

	
	SELECT @cmd = 'IF EXISTS (SELECT top 1 \* FROM ' + @TableName + ' WHERE \[' + @col + 
	'\] IS NULL) BEGIN insert into @Results (TableName, ColumnName) values( ''' + @TableName +''',''' + @col + ','') end';

	begin try
		--select @cmd
		--EXEC(@cmd)
		EXECUTE SP\_EXECUTESQL @cmd, N'@Results Results READONLY', @Results;
	end try
	begin catch
		SELECT @col=@col + ', Error Message: ' + ERROR\_MESSAGE()			
	end catch
	
	--insert into #Results (ColumnName) values(@col)	

	set @IdMin2+=1
end

set @

Mycroft Holmes

Is there a reason why you are creating a user defined TYPE for each table? Why not create a table variable Declare @Tbl TABLE(ID int)

Never underestimate the power of human stupidity RAH

indian143

Because I am trying to insert into the Table variable using dynamic sql, its not doing, can you please help me how can I insert into the Table variable within dynamic sql without using the User Defined Type? Thanks in advance buddy. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Richard Deeming

indian143 wrote:

@Results Results READONLY

There's a slight clue in that line to suggest that you can't write to the table valued parameter. :)

Use Table-Valued Parameters (Database Engine) | Microsoft Docs[^]:

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

If you need to write to the table, use a temp table instead. It looks like you at least started trying to do that - one of the commented-out lines refers to #results instead of @results.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

indian143

hi Rick, yes that's true I did achieve by using the Temp Table but I am always puzzled if I have any way to insert using table variable rather than temp table, because I don't like to drop, hence like to use Table variable which will be lost after the scope. Any other way I can insert into table variable within dynamic sql? Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Richard Deeming

If you pass a table valued parameter around, you can't modify the contents. You can only modify it in the procedure where it's declared. A temp table is definitely the way to go, and there's no danger in dropping it. Each active connection gets its own set of temp tables, so there's no chance of the code executing on one connection affecting a temp table from another connection.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer