SQL query syntax error

Question

I am facing syntx error for min(creation time) max (creation time) line code. Please help to fix syntx error for below SP.

alter procedure MyProcedure
     @table_name varchar(100)
 as
 begin
     declare @s nvarchar(max) = 
         concat('select top(0) * into ', quotename(@table_name + '_Old_' + convert(varchar(30), cast(getdate() as date), 102)), 
             ' from ', @table_name)


    select  min (CAST(creation_time AS DATE)), max (CAST(creation_time AS DATE))  from   @table_name)

       exec (@s)

 end

Answer 1

10. select min (CAST(creation_time AS DATE)), max (CAST(creation_time AS DATE)) from @table_name)

That line is wrong, you can not use variable as object name and the closing bracket is also wrong

Answer 2

Hi @Olaf Helper , thanks. Could you please suggest how to fetch min and max creation date from @table_name.

Answer 3

Maybe try this approach:

. . .
declare @s varchar(max) = 
          concat('select top(0) * into ', quotename(@table_name + '_Old_' + convert(varchar(30), cast(getdate() as date), 102)), 
              ' from ', @table_name, 
              ' select min(CAST(creation_time AS DATE)), max(CAST(creation_time AS DATE)) from ', quotename(@table_name) )

exec (@s)
. . .

Note that if you want to copy the data too, then remove 'top(0)'.

Answer 4

You also can directly build the dynamic sql string and then execute it:

ALTER PROCEDURE MyProcedure
    @table_name varchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @s nvarchar(max) = N'
        SELECT TOP(0) * INTO ' + QUOTENAME(@table_name + '_Old_' + CONVERT(varchar(10), GETDATE(), 102)) + ' FROM ' + @table_name + ';
        SELECT MIN(CAST([ModifiedDate] AS date)), MAX(CAST([ModifiedDate] AS date)) FROM ' + QUOTENAME(@table_name) + ';
    ';

    EXEC(@s);
END

Answer 5

Hi @PraveenKumar ,

Please also check：

    alter  procedure MyProcedure  
          @table_name varchar(100)  
      as  
      begin  
          declare @s nvarchar(max) =   
              concat('select top(0) * into ', quotename(@table_name + '_Old_' + convert(varchar(30), cast(getdate() as date), 102)),   
                  ' from ', @table_name)  
            
              
         select  min (CAST(creation_time AS DATE)), max (CAST(creation_time AS DATE))  from   quotename(@table_name)   
          
    exec (@s)  
    end    

For more details, please refer to：QUOTENAME (Transact-SQL)
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html