There are 3 tables:

CREATE TABLE Factory ( Factory_Code char(4) not null unique, Factory_Desc varchar(100) not null unique, Type int default 1, CONSTRAINT pk_FactoryID PRIMARY KEY(Factory_Code) ) GO CREATE TABLE Volume ( Year year(4), Factory_Code char(4) not null unique, Month month(2), Product char(8) not null, Volume double, CONSTRAINT pk_VolumeID PRIMARY KEY (Year, Factory_Code, Month, Product), CONSTRAINT fk_ Factory_Code FOREIGN KEY (Factory_Code) REFERENCES Factory (Factory_Code) ) GO CREATE TABLE Cost ( Year year(4), Factory_Code char(4) not null, Month month(2), Product char(8) not null, Cost_Type int, Cost double, LastUpdated datetime default (getdate()), CONSTRAINT pk_CostID PRIMARY KEY (Year, Factory_Code, Month, Product) CONSTRAINT FK_ Factory_Code FOREIGN KEY (Factory_Code) REFERENCES Factory (Factory_Code) )

Task:

For each factory of those that do not have volumes in the 9th month of 2015, run some existing procedure Warning_Send (@Factory_Code). Those. the procedure takes as a parameter the factory code, and it must be run several times - for each factory that matches the condition.

My code is:

 CREATE PROCEDURE Warning_Send1 @Factory_Code char(4) AS IF @Factory_Code in ( SELECT Factory_Code FROM Volume WHERE Year<>2015 AND Month<>9 ) EXEC Warning_Send @Factory_Code ELSE PRINT 'Не подходит под заданные условия'

Opportunities to check the code is not, tell me, please, is it correct? Thank you in advance.

1 answer 1

Is it correct?

No, it is incorrect. Condition

 WHERE Year<>2015 AND Month<>9

checks the EXISTENCE of volumes in at least one, except September, the month of any year, except 2015. It should be the opposite -

 IF @Factory_Code NOT IN ( SELECT Factory_Code FROM Volume WHERE Year=2015 AND Month=9 )
  • Yes, I absolutely agree, it is rather a typo. And if you generally evaluate the syntax? - docfisherman