asked on

How do you do delete and update stored procedures?

I am trying to do a delete and update stored procedure based on a certain value. How would you delete and update with stored procedures if this is the insert stored procedure?

USE [EmployeeScreening]
GO
/****** Object:  StoredProcedure [dbo].[InsertMemberFamilyInfo]    Script Date: 07/14/2009 15:50:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Brett Smith>
-- Create date: <07/13/09>
-- Description:	<Insert Employee Family Information>
-- =============================================
ALTER PROCEDURE [dbo].[InsertMemberFamilyInfo]
	-- Add the parameters for the stored procedure here	
	@MemberFamilyID				int = NULL OUTPUT,
	@EmployeeID					int,
	@MemberFamilyNumber			int,
	@Allergies					nvarchar(50),
	@Weight						int,
	@Age						int,
	@DateOfBirth				datetime,
	@MemberFamilyCreatedDate	datetime,
	@MemberFamilyCreatedBy		nvarchar(50)
AS
BEGIN
 
	    -- Insert statements for procedure here
	INSERT INTO MemberFamilyInfo (EmployeeID, MemberFamilyNumber, Allergies,
	Weight, Age, DateOfBirth, MemberFamilyCreatedDate, MemberFamilyCreatedBy) VALUES
(@EmployeeID, @MemberFamilyNumber, @Allergies, @Weight, @Age, @DateOfBirth,
@MemberFamilyCreatedDate, @MemberFamilyCreatedBy);
 
SET @MemberFamilyID = SCOPE_IDENTITY();
		
	END;
 
DECLARE @ID INT;
EXEC dbo.InsertMemberFamilyInfo @MemberFamilyID = @ID OUT,
@EmployeeID = 1,
@MemberFamilyNumber = 1,
@Allergies='',
@Weight=1,
@Age = 99,
@DateOfBirth='19000101',
@MemberFamilyCreatedDate = '20090714',
@MemberFamilyCreatedBy = 'Me'
	 
	 
 
SELECT @ID ID;

Open in new window

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

you run the ALTER procedure ONCE,
and then run the other code.

what you posted looks like you run all in 1, making the second part actually being part of the stored proc itself...
in the query window (sql management studio), pout a line GO between the 2 parts (before DECLARE @ID INT;)

chapmandew

delete:

USE [EmployeeScreening]
GO
/****** Object: StoredProcedure [dbo].[InsertMemberFamilyInfo] Script Date: 07/14/2009 15:50:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Brett Smith>
-- Create date: <07/13/09>
-- Description:      <Insert Employee Family Information>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteMemberFamilyInfo]
      -- Add the parameters for the stored procedure here
      @MemberFamilyID                        int
AS
BEGIN

       -- Insert statements for procedure here
DELETE MemberFamilyInfo
WHERE MemberFamilyID = @MemberFamilyID

END;

EXEC dbo.DeleteMemberFamilyInfo @MemberFamilyID = 33 ,

Aneesh

CREATE PROCEDURE [dbo].[UpdateMemberFamilyInfo]
-- Add the parameters for the stored procedure here
@EmployeeID int
,@MemberFamilyNumber int
,@Allergies nvarchar(50)
,@Weight int
,@Age int
,@DateOfBirth datetime
,@MemberFamilyCreatedDate datetime
,@MemberFamilyCreatedBy nvarchar(50)
AS
BEGIN

UPDATE MemberFamilyInfo
SET EmployeeID = @EmployeeID
,MemberFamilyNumber = @MemberFamilyNumber
,Allergies = @Allergies
,Weight = @Weight
,Age = @Age
,DateOfBirth = @DateOfBirth
,MemberFamilyCreatedDate = @MemberFamilyCreatedDate
,MemberFamilyCreatedBy = @MemberFamilyCreatedBy
WHERE MemberFamilyID = @MemberFamilyID
END ;

GO

CREATE PROCEDURE [dbo].[DeleteMemberFamilyInfo]
-- Add the parameters for the stored procedure here
@MemberFamilyID int
AS
BEGIN

-- Insert statements for procedure here
delete from MemberFamilyInfo
where MemberFamilyID = @MemberFamilyID

END ;

matty1stop

Its a little hard to tell what you are asking but is this it? (delete)

Matt

USE [EmployeeScreening]
GO
/****** Object:  StoredProcedure [dbo].[InsertMemberFamilyInfo]    Script Date: 07/14/2009 15:50:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Brett Smith>
-- Create date: <07/13/09>
-- Description:	<Insert Employee Family Information>
-- =============================================
Create PROCEDURE [dbo].[DeleteMemberFamilyInfo]
	-- Add the parameters for the stored procedure here	
	@MemberFamilyID				int = NULL OUTPUT,
AS
BEGIN
 
	    -- delete statements for procedure here
		delete MemberFamilyInfo 
		where MemberFamilyNumber = @MemberFamilyNumber
		
	END;

Open in new window

chapmandew

this is how you run the update...i forgot to swap out the name

EXEC dbo.UPdateMemberFamilyInfo @MemberFamilyID = 33 ,
@EmployeeID = 1,
@MemberFamilyNumber = 1,
@Allergies='',
@Weight=1,
@Age = 99,
@DateOfBirth='19000101',
@MemberFamilyCreatedDate = '20090714',
@MemberFamilyCreatedBy = 'Me'