0

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

Community
  • 1
  • 1
Steam
  • 9,368
  • 27
  • 83
  • 122
  • 2
    2) `NVARCHAR(MAX)`. What you try to accomplish ? – Bogdan Sahlean Jan 06 '14 at 21:26
  • This is not acceptable practice. I'd urge you not to do this. – sam yi Jan 06 '14 at 21:26
  • Are you using mysql or sql-server? – sam yi Jan 06 '14 at 21:26
  • @BogdanSahlean - execute that SQL inside SSIS and then save the result set to a file. – Steam Jan 06 '14 at 21:27
  • 4
    @samyi: there are source control systems that use SQL Server to store the source code. – Bogdan Sahlean Jan 06 '14 at 21:27
  • 1
    No, you wouldn't store any extraneous SQL data in another table be it query or creation script. If you need code to be accessed by another language, why not setup a stored routine, function, view, etc? – the_pete Jan 06 '14 at 21:28
  • Yes but users do not access the database directly. Also, it's using the database as a "code/text" storage as it was designed to do... in other words, it's not intended for execution. This is generally dangerous practice. IMO – sam yi Jan 06 '14 at 21:29
  • I thought it would be a bit easier if someone else wants to change the SQL code later. – Steam Jan 06 '14 at 21:31
  • Please don't make assumptions about my opinions. – Bogdan Sahlean Jan 06 '14 at 21:32
  • @BogdanSahlean - Wow ! I did not know that source control systems use SQL Server to store the source code. Can you give me an example ? Also, then it seems that its okay to store my code in sql server. The length of the code might be only problem, because its limited by max variable size. Thanks. – Steam Jan 06 '14 at 21:33
  • You can design SSIS to be parameter and event driven so that you wouldn't have to do this except in most extreme cases. I think there are things you can do to avoid this. – sam yi Jan 06 '14 at 21:34
  • 1
    @blasto At my firm, we use Git and Red Gate's SQL Source Control. Storing SQL in a DBMS is silly. – Kermit Jan 06 '14 at 21:34
  • 2
    @blasto: Examples: [Team Foundation Server](http://en.wikipedia.org/wiki/Team_Foundation_Server), [Plastic SCM](http://en.wikipedia.org/wiki/Plastic_SCM) – Bogdan Sahlean Jan 06 '14 at 21:36
  • @BogdanSahlean, I don't think this sort of architecture should be encouraged. Even a parameter driven proc with dynamic sql would be a better option than what OP is proposing. – sam yi Jan 06 '14 at 21:41
  • @FreshPrinceOfSO - Actually, I am not using it for source control. – Steam Jan 06 '14 at 21:42
  • @blasto: Please describe your *source problem*. Maybe some kind of parameterization could help you with SSIS. – Bogdan Sahlean Jan 06 '14 at 21:45
  • 1
    @BogdanSahlean - I have to do joins, aggregate some data, add some "fake" columns (ie 'FixMe' as FAKE_COLUMN), convert an existing date - all in my select statement. and...filter by some criteria. All this is to generate a log file of sorts. Hope this makes sense. – Steam Jan 06 '14 at 21:59

1 Answers1

4

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

Mitch
  • 21,223
  • 6
  • 63
  • 86
  • Now I have a new challenge - My proc takes parameters, which VIEW cannot. so, I cannot use a view instead. I need to pivot the stored procedure as mentioned here - http://stackoverflow.com/questions/20960784/pivot-the-results-of-a-stored-procedure – Steam Jan 06 '14 at 22:50
  • 1
    @blasto - So use a table valued function instead. – Martin Smith Jan 06 '14 at 22:58
  • @blasto If the stored procedure is being written for this purpose.. why not just return the pivoted result instead? In other words, do the pivot inside the sp – sam yi Jan 07 '14 at 04:47