This select is not compiling - 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

This select is not compiling

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-11-09 : 17:23:06

this simple code is causing the sp to not compile with all sorts of errors : all i am trying to do is check this table at the customer site if its empty then we don't display the app.

if (select count(*) from oen.dbd_c_modules) > 0 begin

insert into [OEN].[DBD_C_MODULES]([MODULE_ID], [DESCRIPTION], ACTIVE)
VALUES(11,'Medications',1)

end

go

errors:
Msg 102, Level 15, State 1, Procedure DBD_GET_MEDICINE, Line 24
Incorrect syntax near 'end'.
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@DATEFROM".
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@DATEFROM".
Msg 137, Level 15, State 2, Line 39
Must declare the scalar variable "@FACILITYKEY".
Msg 156, Level 15, State 1, Line 52
Incorrect syntax near the keyword 'AND'.
Msg 156, Level 15, State 1, Line 56
Incorrect syntax near the keyword 'AND'.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-11-09 : 17:31:39

Post the entire SP creation code - I don't see any problem with what you have.

Be One with the Optimizer
TG

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-11-09 : 17:39:43

OK thank you TG, sorry its a bit unformatted but this is driving me batty.

USE [PRO2]
GO
/****** Object: StoredProcedure [OEN].[DBD_GET_MEDICINE] Script Date: 11/09/2011 15:33:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [OEN].[DBD_GET_MEDICINE] ( @FACILITYKEY varchar(1000), @DATEFROM DATETIME, @DATETHRU DATETIME, @UNITSTR VARCHAR(100),
@INCLUDEDISCH NUMERIC(1, 0), @PATNUMBER NUMERIC(9, 0), @MEDNAME VARCHAR(100)
)
AS
BEGIN

--declare @row_count int

--select count(*) from [OEN].[DBD_GET_MEDICINE]

if (select count(*) from OEN.DBD_C_MODULES) > 0 begin

insert into [OEN].[DBD_C_MODULES]([MODULE_ID], [DESCRIPTION], ACTIVE)
VALUES(11,'Medications',1)

end

go

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(8), SM.ADMIN_TIME, 1)) AS [Date], COUNT( SM.ADMIN_TIME) AS [Count]
FROM ( SELECT RTRIM(M.LAST_NAME) + CASE WHEN RTRIM(M.FIRST_NAME) <> '' THEN ', '
ELSE ''
END + RTRIM(M.FIRST_NAME) PATIENT_NAME,
CASE WHEN M.NURSING_UNIT is not null THEN M.NURSING_UNIT ELSE '' END NURSING_UNIT, CASE WHEN M.UNIT_CODE is not null THEN M.UNIT_CODE ELSE '' END UNIT_CODE,
SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ROOM, CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' '
+ SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7) ADMIN_TIME,
AM.ORDER_NAME,
AM.DOSAGE,
AM.DOSAGE_FORM,
CASE WHEN AL.ADMIN_RESULT = -9 THEN 'Y' ELSE '' END NOTE_ENTERED, AM.ORDER_KEY FROM ( ( ( ( ( SELECT OEN.DATEONLY(@DATEFROM + TEMP.PIV) DATE_ORDER,
DATEPART(dd, @DATEFROM + TEMP.PIV) DAY_ORDER FROM ( SELECT TOP ( DATEDIFF(dd, @DATEFROM, @DATETHRU)
+ 1 )
ROW_NUMBER() OVER ( ORDER BY DX_KEY ) PIV FROM OEN.GEN_C_DX
) TEMP
WHERE @DATEFROM + TEMP.PIV <= @DATETHRU
) DTR
INNER JOIN OPTC.ORD_M_ADMIN AM ON DTR.DATE_ORDER
+ ( AM.ADMIN_TIME
- OEN.DATEONLY(AM.ADMIN_TIME) ) BETWEEN AM.START_DATE AND AM.END_DATE
)
LEFT OUTER JOIN OPTC.ORD_D_ADMIN_LOG AL ON AM.ORDER_KEY = AL.ORDER_KEY
--AG AND OEN.DATEONLY(AL.ADMIN_DATETIME) = DTR.DATE_ORDER AND OEN.DATEONLY(AL.ADMIN_TIME) = DTR.DATE_ORDER AND AL.ADMIN_TIME
-- OEN.DATEONLY(AL.ADMIN_TIME) = AM.ADMIN_TIME
-- OEN.DATEONLY(AM.ADMIN_TIME)
)
LEFT OUTER JOIN OPTC.ORD_C_ADMIN_RESULT AR ON AL.ADMIN_RESULT = AR.RESULT_KEY
)
INNER JOIN OEN.GEN_M_PATIENT_MAST M ON AM.PAT_NUMBER = M.PAT_NUMBER AND AM.FACILITY_KEY = M.FACILITY_KEY AND ( @UNITSTR IS NULL OR @UNITSTR = ''
OR CHARINDEX(M.UNIT_CODE, @UNITSTR)
% 2 = 1
)
)
LEFT OUTER JOIN OEN.GEN_M_DOCTOR_MAST DR ON M.PRIMARY_DOCTOR_KEY = DR.DOCTOR_KEY AND M.FACILITY_KEY = DR.FACILITY_KEY
WHERE AM.FACILITY_KEY IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,',')) AND ( AM.PAT_NUMBER = @PATNUMBER OR @PATNUMBER = -1
)
AND ( @MEDNAME IS NULL
OR @MEDNAME = ''
OR AM.ORDER_NAME LIKE ( '%' + @MEDNAME + '%' )
)
AND AM.PRN = 0
AND OPTC.ORD_IS_ADMIN(AM.START_DATE, DTR.DATE_ORDER, AM.DAYS_REQ) = 1 AND NOT EXISTS ( SELECT AL1.ORDER_KEY FROM OPTC.ORD_D_ADMIN_LOG AL1
WHERE AL1.ORDER_KEY = AM.ORDER_KEY --AG AND OEN.DATEONLY(AL1.ADMIN_DATETIME) = DTR.DATE_ORDER AND OEN.DATEONLY(AL1.ADMIN_TIME) = DTR.DATE_ORDER AND AL1.ADMIN_TIME
-- OEN.DATEONLY(AL1.ADMIN_TIME) = AM.ADMIN_TIME
-- OEN.DATEONLY(AM.ADMIN_TIME)
AND CHARINDEX(CAST(ABS(AL1.ADMIN_RESULT) AS VARCHAR(2)),
'01234') > 0 )
AND NOT EXISTS ( SELECT OM.ORDER_KEY
FROM OPTC.ORD_M_ORDER OM
WHERE OM.ORDER_STATUS = 0
AND OM.HOLD_ON_FROM = AM.ORDER_KEY
AND DTR.DATE_ORDER BETWEEN OM.START_DATE AND OM.END_DATE ) AND ( ( @INCLUDEDISCH = 0 ) OR ( 1 = ( CASE WHEN ( @INCLUDEDISCH = 1 )
AND M.DISCHARGE_DATE IS NULL THEN 1 WHEN ( @INCLUDEDISCH = 2 ) AND ( M.DISCHARGE_DATE IS NOT NULL ) THEN 1 ELSE 0 END ) )
)
) SM
GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR(8), SM.ADMIN_TIME, 1)) END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-11-09 : 17:50:33

You can't have a "GO" within the SP code. So get rid of this one:


insert into [OEN].[DBD_C_MODULES]([MODULE_ID], [DESCRIPTION], ACTIVE)
VALUES(11,'Medications',1)


end

go

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources