How do I accurately insert a sql script into a SqlServer table.

Question

I'd like to insert a sql script into a table. I'm pretty sure this is more complicated than just wrapping the script in quotes and throwing it in an insert statement (scripts with quotes and more complicated escaping seem problematic for example)

So, how can I safely store arbitrary tsql in a SqlServer table?

I can use either sql or c# to encode the script if needed.

Answer 1

Use a parametrized query:

C#

var cmd = new SqlCommand(...);
cmd.CommandText = "INSERT INTO [Bla] ([SQL Column]) VALUES (@MyValue)";
cmd.Parameters.AddWithValue("MyValue", yourValueHere);

This will take care of all of the quoting and etc.

If you want to use a stored procedure, you would end up executing it in a similar way:

var cmd = new SqlCommand(...);
cmd.CommandText = "StoreSQL";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("MyValue", yourValueHere);

Answer 2

Use stored procedures and pass sql script as parameter - you will not have problems with quotes and special symbols.

Answer 3

Storing the SQL text in the database should not really be a problem so long as you using a stored procedure that takes a nvarchar(max) parameter, there should be no need to escape anything.

e.g.

CREATE PROCEDURE uspStoreSQL
(
  @sql nvarchar(max)
)
AS
INSERT INTO SomeTable (SQLText)
Values (@sql)