SQL Sever: Storing Code in Binary or Text Files Rather than Stored Procedures
(Page 1 of 5 )
Learn how to extend SQL Server's capabilities by making use of binary and text files, to execute SQL queries, in place of 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.
Next: Case 1: .INI File >>
More SQL Server Articles
More By Raghavendra Narayana
/ 21 
