Dynamically built sql server insert statement fails to insert

Question

We dynamically build a sql statement after validation

After running some code the generated values for the insert looks like this:

(1, 1, SYSDATETIME(), null),
(4, 1, SYSDATETIME(), null),
(6, 1, SYSDATETIME(), null),
(8, 1, SYSDATETIME(), null)

Valid SQL Syntax

We then user this as follows

IF(@Activities != null)
Begin
 INSERT INTO [User].[User_Activities]
       (UserId, ActivitieId, DateCreated, DateDeleted)
 VALUES
       @Activities
END

@Activities being the above dynamically built SQL statement, its throws an error saying incorrect syntax by @Activities.

Even though in SQL query window it looks like this:

Insert into [User].User_Activities
(UserId, ActivitieId, DateCreated, DateDeleted)
Values
(1, 1, SYSDATETIME(), null),
(4, 1, SYSDATETIME(), null),
(6, 1, SYSDATETIME(), null),
(8, 1, SYSDATETIME(), null)

and executes successfully, how can I make this work successfully via the application?

Parameters are not simply string-replaced into the statement, they're like variables in C#. This fails for the same reason you can't say string s = "Console.WriteLine"; s("hello"); — Blorgbeard
– Blorgbeard, Commented Apr 5, 2015 at 6:18

Jon Tirjan · Accepted Answer · 2015-04-05 06:34:45Z

1

Take a look at either SqlBulkCopy or table-valued-parameters. These offer a much cleaner way to insert multiple records into a SQL database from a C# app.

answered Apr 5, 2015 at 6:34

Jon Tirjan

3,7242 gold badges18 silver badges28 bronze badges

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

Comments

Giorgos Betsos · Accepted Answer · 2015-04-05 06:35:33Z

0

Using @Activities like this does not build a dynamic SQL statement. It combines a static part of a statement with a dynamic one, which is not possible in SQL Server.

You also have to correctly formulate the IF statement: != operator cannot be used for inequality comparison against NULL value (it will never evaluate to TRUE). IS NOT NULL is used in this case.

You have to use EXEC to achieve what you want:

IF(@Activities IS NOT NULL)
BEGIN
  EXEC('INSERT INTO [User].[User_Activities]
          (UserId, ActivitieId, DateCreated, DateDeleted)
        VALUES' + @Activities)
END

edited Apr 5, 2015 at 6:35

answered Apr 5, 2015 at 6:25

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

1 Comment

Code Ratchet Over a year ago

this is interesting, I have just tried your code I get no error, yet when viewing the database after the insert the records are not there

Collectives™ on Stack Overflow

Dynamically built sql server insert statement fails to insert

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related