SQL Sever: Storing Code in Binary or Text Files Rather than Stored Procedures (Page 1 of 5 )
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.
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.