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
/ 21 
