Could not able to figure out the error near convert sql

Question

It is giving me an error saying 'Incorrect syntax near the keyword 'convert'.' Could not able to figure out what could be done to remove it.

ALTER PROCEDURE [dbo].[sp_convert_unix_timestamp]
    @table_name nvarchar(250),     ---Staging Table
    @DateTimeString nvarchar(max)
AS
BEGIN
        if isnull(@DateTimeString,'') <> '' begin 
        declare @SQLString Nvarchar(max)    
        declare @UpdateString Nvarchar(max)
        declare @initialepoch Nvarchar(10)
        set @initialepoch = '19700101'
        SELECT @SQLString = coalesce(@SQLString + ',', '') + 
                            'convert(varchar, dateadd(ss, convert(Int, ' + value + '),' + @initialepoch + '), 120)'   
        FROM STRING_SPLIT(@DateTimeString, ',')  
        WHERE RTRIM(value) <> ''

        select @UpdateString = 'Update [' + @table_name  + '] set ' + @SQLString

        exec (@SQLString)
    end 
END

Possible duplicate of [T-SQL: Opposite to string concatenation - how to split string into multiple records](https://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco) — Panagiotis Kanavos, Jul 20 '18 at 09:44
The statement you are trying to execute is a CONVERT... which in fact isn't a statement but a function. It can be contained in a SELECT statement, but it can't be executed all alone. — Wolfgang Kais, Jul 20 '18 at 09:52
Put a PRINT @SQLSTRING before the EXEC to get an idea of what it is attempting to execute. — cloudsafe, Jul 20 '18 at 09:58
Actually this script is being executed in an ETL tool which does not print the output of the script — Gagan, Jul 20 '18 at 10:00
I'd recommend `QUOTENAME` over `'[' + @table_name + ']'`. What you have is still open to injection. For example `@table_name = 'MyTable]; USE master; ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE YourDatabase;--'` would work fine (Assuming `MyTable` and `YourDatabase` were real objects, and the Login had high enough priviledges.) — Thom A, Jul 20 '18 at 10:14
Using `QUOTENAME(@table_name)` would turn the string into `'[MyTable]]; USE master; ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE YourDatabase;--]'`, making it completely harmless. — Thom A, Jul 20 '18 at 10:15

Thom A · Accepted Answer · 2018-08-28T08:33:51.647

Firstly, let's fix the SP so it's no open to Injection, and the datatype choice for @table_name:

ALTER PROCEDURE [dbo].[sp_convert_unix_timestamp]
    @table_name sysname,     ---Staging Table
    @DateTimeString nvarchar(max)
AS

    IF ISNULL(@DateTimeString, '') <> ''
    BEGIN
        DECLARE @SQLString nvarchar(MAX);
        DECLARE @UpdateString nvarchar(MAX);
        DECLARE @initialepoch nvarchar(10);
        SET @initialepoch = N'19700101';

        SELECT @SQLString = COALESCE(@SQLString + ',', '') + N'convert(varchar(10), dateadd(ss, convert(Int, ' + QUOTENAME(value,N'''') + N'), + @dinitialepoch), 120)'
        FROM STRING_SPLIT(@DateTimeString, ',')
        WHERE RTRIM(value) <> '';

        SELECT @UpdateString = N'Update ' + QUOTENAME(@table_name) + N' set ' + @SQLString;

        PRINT @UpdateString;
        EXEC sp_executesql @SQLString, N'@dinitialepoch nvarchar(10)', @dinitialepoch = @initialepoch;

    END;

If we were to run that SP, EXEC [dbo].[sp_convert_unix_timestamp] N'MyTable', N'20170720,20170721';, you'd get this output (and magcially, you know the reason why it's not working):

Update [MyTable] set convert(varchar, dateadd(ss, convert(Int, '20170720'), + @dinitialepoch), 120),convert(varchar, dateadd(ss, convert(Int, '20170721'), + @dinitialepoch), 120)

There is no column in your SET clause. You simply have UPDATE [Table] SET [Value]; The format of an UPDATE statement is UPDATE [Table] SET [Column] = [Value];. What column(s) that needs to be updated, however, I have no idea...

Could not able to figure out the error near convert sql

1 Answers1