can You help me fix this sp - 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

General SQL Server Forums

New to SQL Server Programming

can You help me fix this sp

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-11-05 : 22:09:14

the boss handed me his changes but i cant figure out - do you see what is wrong here?

USE [PRO2]
GO

/****** Object: StoredProcedure [dbo].[GET_SCHEDULE_ALL_DETAIL] Script Date: 11/04/2011 13:18:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[GET_SCHEDULE_ALL_DETAIL]
@FACILITYKEY varchar(1000),
@UNITSTR VARCHAR(100),
@FromDate datetime,
@ToDate datetime
AS
BEGIN

(SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, M.REFERENCE_DATE ,
RTRIM(P.LAST_NAME) + CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
ELSE ''
END + RTRIM(P.FIRST_NAME) PATIENT_NAME
,CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE,
CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE
ELSE WHEN S.PAT_NUMBER NOT IN (SELECT M.PAT_NUMBER FROM [OPTC].MD3_M_MAST M)
(CASE WHEN S.A3A_DATE_USER < GETDATE() THEN 'NOT STARTED - LATE' ELSE 'NOT STARTED')
ELSE WHEN EXPECTED SIGN DATE < GETDATE() THEN 'UNCOMPLETED LATE' ELSE
** here supposed to new conditions.

ELSE 'UNCOMPLETED' END AS ASSESSMENTS
FROM [OPTC].MDS_M_SCHEDULE S INNER JOIN OPTC.MD3_M_MAST M
ON S.PAT_NUMBER=M.PAT_NUMBER
LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
WHERE S.PAT_NUMBER=M.PAT_NUMBER AND M.REFERENCE_DATE < GETDATE()
AND S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
AND S.FACILITY_KEY IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,','))
AND ( @UNITSTR IS NULL
OR @UNITSTR = ''
OR CHARINDEX(P.UNIT_CODE, @UNITSTR)% 2 = 1 ))
END

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-05 : 22:47:37

What are we supposed to fix? Your post is not very clear at all. Does it not compile? Does it error? Does it return an incorrect result? We really need you to explain this better for us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-06 : 01:37:13

It definitely won't compile, due to the
** here supposed to new conditions.
half way down, but what those new conditions are, is another matter. My crystal ball's broken again.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-06 : 03:12:54

this else is also wrong

ELSE WHEN S.PAT_NUMBER NOT IN (SELECT M.PAT_NUMBER FROM [OPTC].MD3_M_MAST M)
(CASE WHEN S.A3A_DATE_USER < GETDATE() THEN 'NOT STARTED - LATE' ELSE 'NOT STARTED')

you cant have control flow statement like this within CASE. its an expression which should return a value.
You may be better off letting us know what you're trying to achieve here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources