locked
Delete From Multiple Tables (2) RRS feed

  • Question

  • User31738214 posted

    im deleting from mulitple tables with ID in each

    error i keep getting is stored procedure has to many arugments specified 

    ALTER PROCEDURE [dbo].[usp_DeleteStudent]
    @studentNum INT
    AS
    SET NOCOUNT OFF
    DELETE
    FROM STUDENT
    WHERE studentNum = @studentNum;
    DELETE
    FROM CONTACT
    WHERE studentNum = @studentNum;

    Friday, March 8, 2013 12:58 PM

Answers

All replies

  • User-972560101 posted

    Hi

    Write Begin and End to execute the Stored Procedure function...

    Friday, March 8, 2013 1:04 PM
  • User31738214 posted

    no its not that thanks though

    this is for insert

    GO
    ALTER procedure [dbo].[usp_InsertContact]

    @contactFName varchar(25),
    @contactLName varchar(25),
    @contactCity varchar(25),
    @contactAddress varchar(50),
    @contactProv varchar(25),
    @contactPCode char(6),
    @contactPhone char(12),
    @contactEmail varchar(25),
    @studentNum INT ,
    @contactID INT OUTPUT
    AS
    SET NOCOUNT OFF
    insert into CONTACT(contactFName,contactLName,contactCity,contactAddress,contactProv,contactPCode,contactPhone,contactEmail,studentNum)
    values(@contactFName,@contactLName,@contactCity,@contactAddress,@contactProv,@contactPCode,@contactPhone,@contactEmail,@studentNum)
    SELECT @contactID = SCOPE_IDENTITY() WHERE @@ROWCOUNT = 1;

    works fine

    update

    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[usp_UpdateStudent]
    @Num INT,
    @School varchar(25),
    @Year int,
    @Program varchar(50),
    @Bio varchar(2000),
    @UName varchar(20)
    AS
    set nocount off;
    update STUDENT
    set studentSchool = @School,
    studentYear = @year,
    studentProgram = @Program,
    studentBio = @Bio,
    studentUserName = @UName
    where studentNum = @Num

    same error as up above

    and delete

    ALTER PROCEDURE [dbo].[usp_DeleteStudent]
    @studentNum INT
    AS
    begin
    SET NOCOUNT OFF
    DELETE
    FROM STUDENT
    WHERE studentNum = @studentNum;
    DELETE
    FROM CONTACT
    WHERE studentNum = @studentNum;
    end

    Friday, March 8, 2013 1:14 PM
  • User260886948 posted

    Hi,

    Delete From Multiple Table or Update Multiple Table in Single Statement is not possible in sql server. But insert to multiple tables is possible. So you get error when do the updating and deleting.

    For more information, please try to refer to:

    http://blog.sqlauthority.com/2012/09/27/sql-server-not-possible-delete-from-multiple-table-update-multiple-table-in-single-statement/ .

    Hope it can help you.

    Best Regards,
    Amy Peng 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 15, 2013 3:55 AM