How to correctly insert sql script into SqlServer table

Question

I would like to insert a sql script into a table. I'm sure this is harder than just wrapping the script in quotation marks and throwing it in the insert statement (scripts with quotes and more complex escaping seem problematic, for example)

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

I can use sql or C # to encode the script if necessary.

Answer 1

Use parameterized query:

WITH#

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

This will take care of all quotes, etc.

If you want to use a stored procedure, you must execute it in the same way:

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

Answer 2

SQL- , , nvarchar (max), .

.

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

Answer 3

Use stored procedures and pass the sql script as a parameter - you will not have problems with quotation marks and special characters.