How to make this stored procedure more flexible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make this stored procedure more flexible?

Author

Topic

Webskater
Starting Member

19 Posts

Posted - 2009-04-11 : 07:39:52

3 tables:

tblDepartments
DepartmentID
DepartmentDescription
(there is a department in here called 'global')

tblHolidays
HolidayID
HolidayDate
HolidayDescription

tblDepartmentHolidays
DepartmentHolidayID
DepartmentID (a DepartmentID of zero means it is 'global' - i.e. applies to all departments)
HolidayID

Idea is that different departments have different 'holidays' - they all share public holidays like Christmas .. but different departments are not available on specific days - attending seminars, training etc.

I want to return data that will look like this:
Holiday Description Holiday Date Global Dept1 Dept2 Dept3 Dept4
Christmas Day 25/12/2009 Yes Yes Yes Yes Yes
Seminar 2 16/06/2010 No No Yes No Yes

(Sorry, can't get that to layout in columns.) But you get the idea, each row contains data about a specific day and whether each department has that day off or not

The stored procedure I have to return the data looks like this:


DECLARE @GlobalDeptID int
SET @GlobalDeptID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'GlobalDepartment')

DECLARE @Dept1ID int
SET @Dept1ID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'Department 1')

DECLARE @Dept2ID int
SET @Dept2ID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'Department 2')

DECLARE @Dept3ID int
SET @Dept3ID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'Department 3')

DECLARE @Dept4ID int
SET @Dept4ID = (SELECT DepartmentID FROM tblDepartments WHERE DepartmentDescription = 'Department 4')

SELECT  DISTINCT tbhHolidays.HolidayDescription,
		CONVERT(VARCHAR(10), tbhHolidays.HolidayDate, 103) AS [Holiday Date],
		CASE WHEN @GlobalDeptID IN (SELECT DepartmentID FROM tblBankHolidaysWPID WHERE tblBankHolidaysWPID.BHID = tblBankHolidays.BHID) THEN 'true' ELSE 'false' END AS 'Global',
		CASE WHEN @Dept1ID IN (SELECT DepartmentID FROM tblDepartmentHolidays WHERE tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept1',
		CASE WHEN @Dept2ID IN (SELECT DepartmentID FROM tblDepartmentHolidays WHERE tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept2',
		CASE WHEN @Dept3ID IN (SELECT DepartmentID FROM tblDepartmentHolidays WHERE tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept3',
		CASE WHEN @Dept4ID IN (SELECT DepartmentID FROM tblDepartmentHolidays WHERE tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID) THEN 'true' ELSE 'false' END AS 'Dept4'

	FROM tblDepartmentHolidays
	INNER JOIN tblHolidays ON tblDepartmentHolidays.HolidayID = tblHolidays.HolidayID
	WHERE tblBankHolidays.HolidayDate >= (SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
	ORDER BY tblHolidays.HolidayDate

Now, this all works - but it is effectively hard coded. What happens when someone adds Department 5 - or deletes Department 3? I have to come back into the stored procedure and alter it.

I'm hoping to be able to write this in such a way that the departments are retrieved from the departments table - and then 'used' - rather than manually getting the DepartmentID of each department and testing to see if this is in the tblDepartmentHolidays table - as I go through the table.

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-12 : 12:59:30

see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources