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;
@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
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.
Member
2 Points
22 Posts
Delete From Multiple Tables (2)
Mar 08, 2013 12:58 PM|Jogginjoe|LINK
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;
Participant
1200 Points
494 Posts
Re: Delete From Multiple Tables (2)
Mar 08, 2013 01:04 PM|Pbalan.in|LINK
Hi
Write Begin and End to execute the Stored Procedure function...
Member
2 Points
22 Posts
Re: Delete From Multiple Tables (2)
Mar 08, 2013 01:14 PM|Jogginjoe|LINK
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
Star
14544 Points
1481 Posts
Re: Delete From Multiple Tables (2)
Mar 15, 2013 03:55 AM|Amy Peng - MSFT|LINK
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