SQL Server 2005 - Dynamic insert query issue

Question

I'm struggling to get this statement run

SET @SelectStatement = 'INSERT INTO [ArchiveProcessHistory] VALUES (' + @TableName + ',' + @TotalRowsSource +',' + @TotalRowsDestination + ',' + GetDate()') '

[I know something is wrong with the above statment, which I'm not able to fix)

SET @FullStatement = @SelectStatement

ArchiveProcessHistory Table Structure is:

TableName - nvarch(5)
TotalRowsSource - integer
TotalRowsDestination - integer
DateofInsert - Date

It's giving me this error when i run through sp_executesql

Msg 102, Level 15, State 1, Procedure usp_ArchiveTable, Line 39 Incorrect syntax near ') '.

How can I solve this?

aF. · Accepted Answer · 2012-05-14 10:34:32Z

1

SET @SelectStatement = 'INSERT INTO [ArchiveProcessHistory] VALUES (' + @TableName + ',' + @TotalRowsSource +',' + @TotalRowsDestination + ',' + GetDate() + ') '

Missing one + after getdate().

answered May 14, 2012 at 10:34

aF.

67k45 gold badges141 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikael Eriksson · Accepted Answer · 2012-05-14 10:42:44Z

1

To avoid a potential SQL Injection attack you could use sp_executesql instead.

declare @SelectStatement nvarchar(max)

set @SelectStatement = 
  'INSERT INTO [ArchiveProcessHistory] VALUES
    (@TableName, @TotalRowsSource, @TotalRowsDestination, GetDate())'

exec sp_executesql @SelectStatement,
                   N'@TableName nvarchar(5), @TotalRowsSource int, @TotalRowsDestination int',
                   @TableName = '123', 
                   @TotalRowsSource = 4, 
                   @TotalRowsDestination = 5

You should also have a look at The Curse and Blessings of Dynamic SQL

answered May 14, 2012 at 10:42

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

3 Comments

AdaTheDev Over a year ago

+1 - if dynamic SQL is truly required (not clear from the question if there is any need for it), use parameterised SQL like this

Conrad Jagger Over a year ago

But these are @TableName are input parameters which are passed to stored procredure.

Mikael Eriksson Over a year ago

@ConradJagger You don't use it as a table name in your query. If you do, you should concatenate the table name into @SelectStatement using quotename().

Emmie Lewis-Briggman · Accepted Answer · 2012-05-14 10:41:00Z

0

Instead of GetDate() in your insert statement which is a function, use current_timestamp.

SET @SelectStatement = 'INSERT INTO [ArchiveProcessHistory] VALUES (' + @TableName + ','   + @TotalRowsSource +',' + @TotalRowsDestination + ',' + current_timestamp + ') '

answered May 14, 2012 at 10:41

Emmie Lewis-Briggman

8554 silver badges12 bronze badges

1 Comment

Conrad Jagger Over a year ago

This is failing because TableName is VARCHAR ... and above statement fails when I run it... varchar error

Collectives™ on Stack Overflow

SQL Server 2005 - Dynamic insert query issue

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related