CODEWITHSUNDEEP
sqlsql-server
Steam
Steam

Reputation: 9856

Storing SQL code in SQL server

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

Upvotes: 0

Views: 263

Answers (1)

Mitch
Mitch

Reputation: 22271

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

Upvotes: 4

Related Questions