How to eliminate this SQL String_Split function error

Trying to use the SQL string_split function in a dynamic query but I continue to receive the following error. Invalid column name ‘Invoice Description’. Argument data type void type is invalid for argument 1 of string_split function. I can not figure out the issue. Any help is greatly appreciated.

DECLARE @AuthFile nvarchar(max); DECLARE @TableName AS SYSNAME;
DECLARE @sql nvarchar(max);

SET @TableName = '__tTransactions_' + REPLACE(CONVERT(CHAR(10), GETDATE(), 103), '/', '');
SET @AuthFile = '__Authorization';

create table #temp (TransactionID nvarchar(1000), CartID int, TotalAmount nvarchar(1000))

SET @sql = 'select [Transaction ID] as TransactionID, cs.value as CartID, [Total Amount] as TotalAmount 
            into #temp 
            from ' + @TableName + '
            cross apply string_split([Invoice Description], ''|'') cs
            where (isnull([Invoice Description], '''') <> '''')';

print(@sql);
EXEC(@sql);
SET @sql = '';

select * from #temp

drop table #temp

Answer

A sample table can be created to run your dynamic sql against.

drop table if exists #__tTransactions_05042021;
create table #__tTransactions_05042021 (
    [Transaction ID] int, 
    [Total Amount] float,
    [Invoice Description] varchar(255)
);
insert #__tTransactions_05042021 values 
    (1, 100, '1|2|3'),
    (2, 200, '4|5|6')

This sample is a temp table, so I changed your code to point to a temp table instead of a real one, but otherwise it’s the same:

DECLARE @AuthFile nvarchar(max); 
DECLARE @TableName AS SYSNAME;
DECLARE @sql nvarchar(max);

SET @TableName = '#__tTransactions_' + REPLACE(CONVERT(CHAR(10), GETDATE(), 103), '/', '');
SET @AuthFile = '__Authorization';

create table #temp (TransactionID nvarchar(1000), CartID int, TotalAmount nvarchar(1000))

SET @sql = 'select [Transaction ID] as TransactionID, cs.value as CartID, [Total Amount] as TotalAmount 
            into #temp 
            from ' + @TableName + '
            cross apply string_split([Invoice Description], ''|'') cs
            where (isnull([Invoice Description], '''') <> '''')';

print(@sql);
EXEC(@sql);
SET @sql = '';

select * from #temp

drop table #temp;

This code does NOT error out. It’s not the code. It’s the structure of the table you’re pointing to. See what table is targeted in your from clause that’s printed out, and look at the table structure. You should find that it doesn’t have an ‘invoice description’ column.

Now, there are more issues with your code. Namely, you’re trying to pump your data into ‘#temp’ inside dynamic sql. Your code won’t fail, but it won’t work either because the context is different between code inside dynamic sql and the code outside of it.

You’ll want to either use a global temp table (i.e. ##temp) or use insert-exec. Below is how you would change the last part of your code to do the latter:

create table #temp (TransactionID nvarchar(1000), CartID int, TotalAmount nvarchar(1000))

SET @sql = 'select [Transaction ID] as TransactionID, cs.value as CartID, [Total Amount] as TotalAmount 
            from ' + @TableName + '
            cross apply string_split([Invoice Description], ''|'') cs
            where (isnull([Invoice Description], '''') <> '''')';

print(@sql);
insert #temp
EXEC(@sql);
SET @sql = '';