Exam 70-761 topic 1 question 17 discussion

The order is wrong. IF without BEGIN/END will only execute the first statement so the highlighted answer is flat out wrong. 1.CREATE PROCEDURE sales.modcompanyname @custId int, @newname nvarchar(40) as 2.IF NOT EXISTS (SELECT custid FROM Sales.Customers WHERE custid = @custID) 3.BEGIN THROW 55555, 'The customer ID does not exist' , 1 END 4.UPDATE sales.customers SET companyname=@newname where custid=@custID

CREATE or alter PROCEDURE modcompanyname @custId int, @newname nvarchar(40) as if exists (SELECT custid From cust where custid = @custId) UPDATE cust SET companyname=@newname where custid=@custID IF NOT EXISTS (SELECT custid FROM Cust WHERE custid = @custID) BEGIN THROW 55555, 'The customer ID does not exist Simon' , 1 END

but question requires 5 statements

all comments excellent work! but question requires 5 statements.

@Robintang0924 Excellent, thanks!

By given statements, below version should be the optimized one since it only needs to read table once when there is no existing custid in the table. 1.CREATE PROCEDURE sales.modcompanyname @custId int, @newname nvarchar(40) as 2.IF NOT EXISTS (SELECT custid FROM Sales.Customers WHERE custid = @custID) 3.BEGIN THROW 55555, 'The customer ID does not exist' , 1 END 4.UPDATE sales.customers SET companyname=@newname where custid=@custID However, if we can come up something by ourselves, then below should be even better performance since we only need to touch table once no matter we got existing custid in table or not (above solution still need to access table twice when custid existed in table): 1.CREATE PROCEDURE sales.modcompanyname @custId int, @newname nvarchar(40) as 2.UPDATE sales.customers SET companyname=@newname where custid=@custID 3. IF @@ROWCOUNT = 0 4.BEGIN THROW 55555, 'The customer ID does not exist' , 1 END