stored procedure question - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure question

Author

Topic

mixalis
Starting Member

8 Posts

Posted - 2008-09-02 : 04:25:53

Hi all,

my problem is that I want to create a stored procedure with optional parameters:


	SELECT TASK_ID_PK,TASKNAME,ACTUALSTARTDATE,PROPOSEDSTARTDATE,ACTUALENDDATE,PROPOSEDENDDATE,HOURS,PERCENTAGECOMPLETE,
		   STATUSNAME,PRIORITYNAME,USERFIRSTNAME + ' ' + USERLASTNAME AS ASSIGNEDFROM
	FROM TASKDETAILS
	INNER JOIN TASKSHEADERS
		ON  TaskHeader_id_fk = TASK_ID_PK
	INNER JOIN TaskGroups
		ON TaskGroups_id_fk = TASKGROUP_ID_PK
	INNER JOIN STATUSES
		ON STATUS_ID_FK = STATUS_ID_PK
	INNER JOIN PRIORITIES
		ON PRIORITY_ID_FK = PRIORITY_ID_PK
	INNER JOIN USERS
		ON TASKSHEADERS.USER_ID_FROM_FK = USER_ID_PK
	WHERE 
	TASKDATE = (SELECT MAX(TASKDATE) 
				FROM TASKDETAILS
				WHERE 
				TasksHeaders.TASK_ID_PK = TASKHEADER_ID_FK)
	OR TASKSHEADERS.USER_ID_TO_FK = @USER_ID
	OR TASKSHEADERS.PROPOSEDENDDATE = @PROPOSEDENDDATE
	OR PRIORITY_ID_FK = @PRIORITY
	OR STATUS_ID_FK = @STATUS
	OR TASKSHEADERS.TASKGROUPS_ID_FK = @TASKGROUP_ID
	OR TASKSHEADERS.TASKGROUPS_ID1_FK = @TASKGROUP_ID
	OR TASKSHEADERS.TASKGROUPS_ID2_FK = @TASKGROUP_ID

if I run this I get the same record twice


218	TEST1	NULL	2008-09-01 00:00:00.000	NULL	2008-09-02 00:00:00.000	NULL
219	test2	NULL	2008-09-01 00:00:00.000	NULL	2008-09-02 00:00:00.000	NULL
220	test3	NULL	2008-09-01 00:00:00.000	NULL	2008-09-02 00:00:00.000	NULL
218	TEST1	NULL	2008-09-01 00:00:00.000	NULL	2008-09-02 00:00:00.000	123

because of the OR condition, if I change it to AND and search for the records I want I will not get the desired records...Any ideas on how to resolve the problem???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 04:33:07

i think its because USERS table have multiple records for passed USER_ID value. Try grouping on other fields and tke max of username value.

mixalis
Starting Member

8 Posts

Posted - 2008-09-02 : 04:41:34

No, table users has unique values.The thing is that the TaskDetails table has multiple records that correspond to a single TaskHeader record...I want the most recent one that's why I use MAX(DATE) but it does not seem to work properly

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 04:52:41

You have many tasks for same taskheader Pk and same date.
Do you have anything else idenifying your records. An IDENTITY column maybe?

SELECT		{Table alias or table name missing}.TASK_ID_PK,
		{Table alias or table name missing}.TASKNAME,
		{Table alias or table name missing}.ACTUALSTARTDATE,
		{Table alias or table name missing}.PROPOSEDSTARTDATE,
		{Table alias or table name missing}.ACTUALENDDATE,
		{Table alias or table name missing}.PROPOSEDENDDATE,
		{Table alias or table name missing}.HOURS,
		{Table alias or table name missing}.PERCENTAGECOMPLETE,
		{Table alias or table name missing}.STATUSNAME,
		{Table alias or table name missing}.PRIORITYNAME,
		{Table alias or table name missing}.USERFIRSTNAME + ' ' + {Table alias or table name missing}.USERLASTNAME AS ASSIGNEDFROM
FROM		TASKDETAILS 
INNER JOIN	TASKSHEADERS ON {Table alias or table name missing}.TaskHeader_id_fk = {Table alias or table name missing}.TASK_ID_PK
INNER JOIN	TaskGroups ON {Table alias or table name missing}.TaskGroups_id_fk = {Table alias or table name missing}.TASKGROUP_ID_PK
INNER JOIN	STATUSES ON {Table alias or table name missing}.STATUS_ID_FK = {Table alias or table name missing}.STATUS_ID_PK
INNER JOIN	PRIORITIES ON {Table alias or table name missing}.PRIORITY_ID_FK = {Table alias or table name missing}.PRIORITY_ID_PK
INNER JOIN	USERS ON TASKSHEADERS.USER_ID_FROM_FK = {Table alias or table name missing}.USER_ID_PK
WHERE		{Table alias or table name missing}.TASKDATE = (	SELECT	MAX(TASKDATE) 
				FROM	TASKDETAILS
				WHERE	TasksHeaders.TASK_ID_PK = {Table alias missing}.TASKHEADER_ID_FK
			)
		OR TASKSHEADERS.USER_ID_TO_FK = @USER_ID
		OR TASKSHEADERS.PROPOSEDENDDATE = @PROPOSEDENDDATE
		OR {Table alias or table name missing}.PRIORITY_ID_FK = @PRIORITY
		OR {Table alias or table name missing}.STATUS_ID_FK = @STATUS
		OR @TASKGROUP_ID IN (TASKSHEADERS.TASKGROUPS_ID_FK, TASKSHEADERS.TASKGROUPS_ID1_FK, TASKSHEADERS.TASKGROUPS_ID2_FK)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources