OK I think I understand what was happening. Lesson learned -- local variable declarations collide in some way with column names in any DDL or DML within the procedure.
Altering my procedure and changing variable fileID (which happens to be a column in a table I reference) to a different value, I get the result I'm expecting.
Here is how to do it without having to use a scalar UDF. This avoids the pitfalls that Jason Long mentions in his answer (which was very well written, btw). The tradeoff here is that the SQL is not as readable.
Here are the pros and cons of my method.
no new UDFs created
simplifies index creation
simpler index declaration SQL
able to create index ...
You might like to look at partition switching - the staging table could be switched in to the production table, and the production table switched out to a history table. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15#c-switching-partitions-between-tables gives an example of a switch out. There are quite a ...
It's hard to debug without seeing the output of your 2nd @SQL or @cols.
I'd start from there to debug, SELECT/PRINT the variable and copy-and-paste-run-it
or try different way of cols?
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks
FYI this is my ...
Unfortunately, you can't use a variable containing the table name in an INSERT statement. The error message is actually occurring because it is expecting a variable that is a table type to insert rows into, but you're passing a variable with a string value.
You can use Dynamic SQL to execute the command using a variable table name value like this:
There is something niot right, but you can take it from here.
I replaced your FROM clause, because you need a sorted list to do that coalculation and mariadb takes the definition, that tables are unsorted literary and eliminates all sorting in subqueries. so you have to circumvent it
Check the numbers, i think you must work on that.
A method that does not require any self-joins and will only read the table once.
This is adapted from this answer which contains an explanation of the code. All that has changed is to add PARTITION BY order_id to calculate the date ranges for each order_id and then to return the ranges (rather than total the values, as per the linked answer):
When you right click on stored procedures, you hit create stroed procedures.
this should show spelling errors like INsRT and systax erors
But logic errors it will not find.
So start using error handling and user defined variables to debug it properly.
But you can test all queries in a normal Query tab this would also check for syntax errors
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
create table data(order_id number, start_date_time date, end_date_time date);
insert into data values (3933 ,'04/02/2020 08:00:00', '04/02/2020 12:00:00');
insert into data values (3933 ,'04/02/2020 13:30:00', '04/02/2020 17:00:00');
insert into data values (3933 ,'04/02/2020 14:00:00', '04/02/...