Deleting rows using stored procedure with parameter

Question

I am using SQL Server 2008 R2 and I want to create a stored procedure that deletes from two tables using a paramater (id).

This is the stored procedure:

CREATE PROCEDURE [dbo].[sp_deleteDecision]
@ID int
AS

DELETE FROM [dbo].[tblDecisionInvolvement] as di
WHERE di.DecisionId = @ID
DELETE FROM [dbo].[tblDecision] as d
WHERE d.Id =@ID

GO

This is the error I get when I try to create it:

Msg 156, Level 15, State 1, Procedure sp_deleteDecision, Line 6
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Procedure sp_deleteDecision, Line 8
Incorrect syntax near the keyword 'as'.

Note that changing the DELETE FROM to

SELECT * FROM

it works.

Is it even possible to delete something using parameters?

Ty.

Where did you see syntax that used AS in a delete this way? Did you look at the official documentation for the DELETE command when you encountered this error? — Aaron Bertrand
– Aaron Bertrand, Commented Aug 7, 2012 at 12:48
No, I was looking at the stored procedure page. I did not realise that adding aliases was causing the problem. — sander
– sander, Commented Aug 7, 2012 at 12:57

Damien_The_Unbeliever · Accepted Answer · 2012-08-07 12:46:16Z

13

You aren't allowed to introduce an alias at that part of a DELETE statement - nor do you need one in this case:

USE ChessDb01
GO
CREATE PROCEDURE [dbo].[sp_deleteDecision]
@ID int

AS

DELETE FROM [dbo].[tblDecisionInvolvement]
WHERE DecisionId = @ID
DELETE FROM [dbo].[tblDecision]
WHERE Id =@ID

GO

For a more complex query, you might want to use an alias, but note that (confusingly), the DELETE will have two FROM clauses - and you can only introduce the alias in the second one:

DELETE FROM di
FROM [dbo].[tblDecisionInvolvement] di
            inner join
      AnotherTable tab
           on
              di.Column = tab.Column2
WHERE tab.Column99 = @Value

answered Aug 7, 2012 at 12:46

Damien_The_Unbeliever

241k28 gold badges357 silver badges469 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aaron Bertrand Over a year ago

I usually leave out the first FROM in your second example.

sander Over a year ago

Thank you for the fast reply. I removed the aliases and it works now. I feel relieved; thank you!

praveen Over a year ago

Aliasing can be used in Delete but it just over complicates it. Example Delete a from Table as a where column=@id

Collectives™ on Stack Overflow

Deleting rows using stored procedure with parameter

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related