I am thinking of storing non-sensitive SQL code in a column of a table. Is this considered an acceptable practice ? If yes, then what would be the best datatype for this ? This code is to be accessed by C# code.
EDIT - I am using SQL server. But, this seems like a generic RDBMS question.
Also, related question - What are the pros and cons to keeping SQL in Stored Procs versus Code
Use stored procedures for this. They are basically what you describe, but with built-in support for maintenance and security.
You can create a procedure:
CREATE PROCEDURE GetCustomerByID ( @CustomerID varchar(11) )
AS
BEGIN
SELECT CustomerName, CustomerAddress
FROM Customers
WHERE CustomerID = @CustomerID
END
Then call it from C# by using the EXEC command or by specifying CommandType.StoredProcedure.
SqlCommand sqc = new SqlCommand("GetCustomerByID", con);
sqc.CommandType = CommandType.StoredProcedure;
sqc.Parameters.AddWithValue("@CustomerID", "FOOBAR");
var reader = sqc.ExecuteReader();
If you are calling it from SQL, use EXEC
EXEC GetCustomerByID @CustomerID = 'FOOBAR';
To change it later, use ALTER PROCEDURE or DROP PROCEDURE instead of CREATE PROCEDURE
NVARCHAR(MAX). What you try to accomplish ?