SQL Sever: Storing Code in Binary or Text Files Rather than Stored Procedures

The base idea of this article is to extend the boundary of SQL Server.  The code of a Stored Procedure or User Defined Function need not to reside only in SP/UDF; you can have the code or a part of the code in ASCII/Binary/Other file format and stored under a specific server/folder.  This concept is useful while introducing .INI file concept in SQL Server.  Plus, you can avoid re-compilation of Stored Procedures on every minor change.  To some extent this concept also introduces code security and non-sharing of business logics.

Advantages

• For security purposes:
  • Store the “Query/Part of query/Stored Procedure” in a Text/Binary/other file format.  Read it through “Bulk Insert” command or through any other feature of SQL SERVER for execution.
  • Include an encryption/decryption algorithm to store/retrieve the complete/part of code for execution from a Text or other file format.
• Reduce the creation/usage of GUIs if the input data is minimal. You can even come out with some innovative approaches by making use of “Replaceable Parameter in batch files” ( a DOS concept).
• Access/Leverage can be given to users who have SQL Query knowledge.
• Reduce the Developer/DBA work/involvement in certain specific tasks where business ideas cannot be shared with the Development team.

Let us explore the cases where we can make use of this concept.  Case 1 and 2 explained here are expectable project requirements.  However, case 3 can be thought about in terms of security.