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

SQL Sever: Storing Code in Binary or Text Files Rather than Stored Procedures - Case 1: .INI File
(Page 2 of 5 )

An SQL Server job is scheduled to run on a 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).  A 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.  The 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 within the file.

Dept.ini contains two departments: BPO and 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

blog comments powered by Disqus