The concept of this article is to introduce code stored in text/binary/other file format instead of storing it

as an stored procedure in syscomments. By using this feature, the user/developer

is capable of changing some frequently changing values/queries/SP editable in text/other file. Also to some extent this introduces security/hiding the information in

stored procedure database object.

Advantages

• For security purposes, stored the Stored Procedure in a Text/Binary/other file format, include encryption/decryption, read it through “Bulk Insert” or other SQL Server 2000 functions for execution.
• Got get rid off from GUIs if the input data is very minimal. Even come out with some innovative Parameter Replaceable Batch files to supply dynamic parameter values.
• Access/Leverage could be given to Users who have SQL Query knowledge.
• Reduce the Developer work/involvement in some specific tasks.

Let us explore the cases where we can make use of this concept. Case 1 and 2 explained here are expected project requirements. But Case 3 can be given a thought

with regards to security.

Case 1 - .INI File

A job is scheduled to run on SQL Server on daily basis at 11:30 PM. Job will execute one Stored Procedure to output 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 the different quarters/weeks in the year. As the Department is the only input required for the Report, there is no GUI involved in it. User is given with 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 on the departments in it.

Dept.ini contains two departments, BPO and DEVELOPMENT.

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 change. And every time there is a change you need to edit the SP, recompile it. The setup exist in many other locations, so you need to contact respective DB authority for changes every time. In this case, have that query in a Text file. Let the SP read the Text file when it required, 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 Variable to execute it SELECT @SQLQuery = Query FROM #TMPQUERY
--Execute the query
 EXECUTE sp_executesql @SQLQUERY
--OTHER SQL STATEMENTS
END

Case 3 - Complete Stored Procedure in a Binary File

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

You can even introduce encrypt/decrypt feature for security purpose. Just add converting/decrypting back to original stuff 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 would be a very rare requirement, but if you look at the MSDN News Group queries, people are in search 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.