SQL Stored Procedure Error

Perhaps I am not getting SQL Parameters correctly, but this doesn't work for me. Am I doing something wrong? I get a Incorrect Syntax near the keyword 'AS' when I try to save this stored proce开发者_StackOverflow社区dure.

ALTER PROCEDURE MyProc
AS
DECLARE @CID int

DELETE FROM Contacts 
WHERE ContactID=@CID

RETURN

You should try this -

ALTER PROCEDURE MyProc
   @CID int
AS
BEGIN

DELETE FROM Contacts 
WHERE ContactID=@CID

RETURN 

END
GO

You are missing a procedure name

ALTER PROCEDURE sp_YOURSPROC

Edit:

You have a procedure with that name? If not you might need CREATE rather than ALTER. But that would give you an invalid object name error.

Should @CID be a parameter? otherwise it seems useless. But even if that is the case then you wouldn't get any error, it just wouldn't execute correctly.

ALTER PROCEDURE MyProc
   @CID INT
AS
   DELETE FROM Contacts 
   WHERE ContactID=@CID

You need to change it to be:

ALTER PROCEDURE MyProc
    @CID int
AS

Begin

DELETE FROM Contacts 
WHERE ContactID=@CID

end

GO

You've declared @CID inside the procedure. It should be declared a parameter though:

ALTER PROCEDURE MyProc

    @CID int -- it's a parameter

AS

   DELETE 
   FROM Contacts 
   WHERE ContactID = @CID

If the stored procudure doesn't exist in the database yet, you'll have to use CREATE PROCEDURE:

CREATE PROCEDURE MyProc

    @CID int -- it's a parameter

AS

   DELETE 
   FROM Contacts 
   WHERE ContactID = @CID