Possible Bug In TSQL And Management Studio

This was the incorrect stored procedure


ALTER PROCEDURE [dbo].[DeletePublication]

@Id int

AS

BEGIN

Begin Transaction DELETEBOOK

Delete CheckedOut Where PublicationCopyId in (Select Id From PublicationCopy Where PublicationId = @Id)

Delete PublicationVolume Where PublicationCopyId in (Select PublicationCopyId From PublicationCopy Where PublicationId = @Id)

Delete PublicationUpdate Where PublicationCopyId in (Select PublicationCopy Id From PublicationCopy Where PublicationId = @Id)

Delete PublicationCopy Where PublicationId = @Id;

Delete Publication Where Id = @Id;

Commit Transaction DELETEBOOK

END


These two lines are the problem.

Delete PublicationVolume Where PublicationCopyId in (Select PublicationCopyId From PublicationCopy Where PublicationId = @Id)

Delete PublicationUpdate Where PublicationCopyId in (Select PublicationCopy Id From PublicationCopy Where PublicationId = @Id)


There is no PublicationCopyId in the PublicationCopy table but Management Studio return no errors on the syntax of this stored procedure.

When this procedure was ran in basically deleted everything in the PublicationVolume and PublicationUpdate tables.

Which is probably the worse case behavior. I was wrong my query but deleting every record is really the worse possible thing to do here.

The statement should have failed on (Select PublicationCopyId From PublicationCopy Where PublicationId = @Id)
and not continued.

Please help us from shooting ourselfs in the foot.
Chris