CODEWITHSUNDEEP
stored-proceduressql-server-2008-r2
sander
sander

Reputation: 749

Deleting rows using stored procedure with parameter

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.

Upvotes: 3

Views: 52249

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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

Upvotes: 13

Related Questions