select statement in the query is causing to not compile

1
votes

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'.

code: is here: http://www.mediafire.com/?16qr8xaszq025b6

2
sql-server-2008
What flavour of SQL are you using? - Borodin
Can you show all of the code for the SP? - Joe Stefanelli
Have you declared the variables it's complaining about? - Daryl
@Borodin - The error messages look like SQL server messages, although which specific release is unknown. The syntax is also consistent with SQL Server. - Andrew
its sql server 2008 r2 here is the code. - Booksman

2 Answers

2
votes

I think we need to see all the code - the errors list variables that are not within the snippet and the GO keyword is not a part of TSQL syntax for a stored procedure, it's an end of batch marker for SSMS. (That GO keyword may be causing your problems if it is really there)

Looking at the full code you posted, you have placed GO keywords in your stored procedure, that is not valid TSQL Syntax, that 'ends' the batch you are sending to the server, so cuts the stored procedure in half. The errors then come from it complaining about the GO keyword, then attempting to run the second batch you are submitting (the second half of the procedure) at which point it doesn't know what the variables are that you have declared, so throws errors on those for good measure.

Just remove the 'GO' within your code and try compile it again.

1
votes

You don't need if(...):

INSERT INTO [OEN].[DBD_C_MODULES]([MODULE_ID], [DESCRIPTION], ACTIVE)
VALUES(11,'Medications',1)
WHERE (select count(*) from oen.dbd_c_modules) > 0
  ;

[personally, I frown upon the uncorrelated subquery, but that is another matter]

EDIT: Alternative formulation:

INSERT INTO [OEN].[DBD_C_MODULES]([MODULE_ID], [DESCRIPTION], ACTIVE)
VALUES(11,'Medications',1)
WHERE NOT EXISTS (select * from oen.dbd_c_modules)
  ;