Code stored in Binary/Text files instead in Stored Procedures-SQL SERVER

The base idea of this article is to extend the boundary of SQL Server by introducing Code/part of code stored in ASCII/Binary/other file format in same/different server.  The code of the Stored Procedures or User Defined Functions need not to reside only in SP/UDF, you can have the code/part of code in ASCII/Binary/Other file format and stored under 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 small change.  To some extent this concept also introduces code security, non-sharing of business logics.

Advantages

Ø       For security purpose.

o        Store the “Query/part of query/Stored Procedure” in a Text/Binary/other file format. Give a restricted access to the server/folder. Read the file through “Bulk Insert” command or through any other feature of SQL SERVER for execution.

o        Include an encryption/decryption algorithm to store/retrieve the code for execution from a Text/other file format.

Ø       Reduce the creation/usage of GUIs if the input data is very less. You can even come out with some innovative approaches by making use of “Replaceable Parameter in Batch files” DOS concept.

Ø       Access/Leverage can be given to Users who have SQL Query knowledge. 

Ø       Reduce the Developer/DBA work/involvement in some specific tasks where business ideas cannot be shared with Development team.

Case 1: .INI File

A SQL Server job is scheduled to run on daily basis at 11:30 PM.  The Job will execute one Stored Procedure to produce an Excel file by querying table(s).  The report is an analysis report based on Department(s).  User wants data of different department(s) during different weeks/months/quarters in the year.  The “Department” is the only input required for the Report, there is no GUI involved in it.  User has access to one .INI file where he could add/change the department names.  The SP reads the .INI file stored in a shared folder, and generates the report based on the departments stored in .INI file. 

Dept.ini contains two departments viz., BPO, DEVELOPMENT.  So the report will be on these departments.

CREATE PROCEDURE DBO.CASE1

AS

BEGIN

      --Procedure to generate Profit & Loss report

      --DECLARATION SECTION

      --OTHER SQL STATEMENTS

      --Temporary table for getting Department Name

      CREATE TABLE #tmpDept (Dept_Name VARCHAR(20))  

      --Gets the data from .ini file to Temp table

      BULK INSERT #tmpDept

         FROM '\\computer1\Thomson_Financial\dept.ini'

         WITH (ROWTERMINATOR = '\n')

      --Get some summary data from a view

      SELECT dept_ID, dept_TurnOver, dept_Profit, dept_Loss

FROM vw_DeptSummary

      WHERE dept_ID IN (SELECT DEPT_ID FROM DBO.DEPARTMENT

                        WHERE DEPT_NAME IN (SELECT C1 FROM #TMP))

      --OTHER SQL STATEMENTS

END

Case 2: SQL Query

There is a SQL Query which changes constantly due to the requirement changes.  And every time there is a change you need to edit the SP, recompile it. The setup exists in many other locations, so you need to contact respective DB authority for changes every time.  In this case, you can store that query in a Text file.  Let the SP read the Text file, get the query, and execute it.

Query.sql file contains the constantly changing query.

CREATE PROCEDURE dbo.CASE2

AS

BEGIN

      --DECLARATION SECTION

      DECLARE @SQLQuery as NVARCHAR(4000)

      --OTHER SQL STATEMENTS

      --Temporary table for getting the query

      CREATE TABLE #tmpQuery (Query NVARCHAR(4000))

      --Gets the query from .sql to Temp table

      BULK INSERT #tmpQuery

         FROM '\\computer1\Thomson_Financial\QUERY.sql'

      --Get Query to a Variable for execution

      SELECT @SQLQuery = Query FROM #TMPQUERY

      --Execute the query

      EXECUTE sp_executesql @SQLQUERY

      --OTHER SQL STATEMENTS

END

There are some business related functions need to be created/maintained.  It is related to core business stuff and Management does not want to explain it to the Development team.  One of the Management guys has SQL Server skill and he will take care of creating/editing the content of Stored Procedure.  It is converted to Binary file and stored in server with some specific rights.  So the task of Developer is to just read the complete Binary file and execute it.

You can even introduce encrypt/decrypt feature for security purpose.  Just add converting/decrypting back to original code in the calling SP.

The SP is in a Binary file.

CREATE PROCEDURE dbo.CASE3

AS

BEGIN

      DECLARE @SQLsp as NVARCHAR(4000)

      --Temporary table for getting the SP

      CREATE TABLE #tmpSP (spText NVARCHAR(4000))

      --Gets the SP from .sql to Temp table

      BULK INSERT #tmpSP

         FROM '\\computer1\Thomson_Financial\SP.sql'

      --Convert/Decrypt Code on the content of SP.sql

      --Get the complete SP to a Variable to execute it

      SELECT @SQLsp = spText FROM #tmpSP

      --Execute the SP

      EXECUTE sp_executesql @SQLsp

END

Conclusion

Some of the readers may feel that this kind of requirements would be rare ones, but if you look at the MSDN News Group queries, people are in search of many innovative approaches.  Now they are not just sticking to the Basics of DB objects.  This effort of Binary/Text File incorporation in Stored Procedures is also a small contribution towards extending the boundaries of MS SQL Server 2000.

Raghavendra Narayana