Hi
Many a times we have to insert multiple data on click of one button. Like inserting all the checked records in the favorite table of the member. It is not a good idea to insert the records by using as many insert queries.
At these points we should insert all the records with only one call to the database. We can use a stored procedure that takes a comma (or any separator you want to use) list of values and insert them in the database. This way the whole process will be very fast and efficient.
In this example we will insert data in the MemberProductfaveriote table. We will be insert the member ID and the MemberProductId(all the product selected as favorite from the list). We will pass the MemberID and the comma separated list of MemberProductId.
The stored procedure will insert each of the record in the database.
In the stored procedure we also check if the record is already inserted in the database. If the record in already present in the database we will not insert the record. We will only insert the record if the record in not present in the database. Here is the stored procedure
CREATE PROCEDURE dbo.insertMemberProductFavorite
(
@MemberId int,
@MemberProductId varchar(100)
)
AS
DECLARE @count int
DECLARE @str VARCHAR(8000)
DECLARE @spot SMALLINT
WHILE @MemberProductId <> ''
BEGIN
SET @spot = CHARINDEX(',', @MemberProductId)
IF @spot>0
BEGIN
SET @str = CAST(LEFT(@MemberProductId, @spot-1) AS INT)
SET @MemberProductId = RIGHT(@MemberProductId, LEN(@MemberProductId)-@spot)
END
ELSE
BEGIN
SET @str = CAST(@MemberProductId AS INT)
SET @MemberProductId = ''
END
SELECT @count=count(1) FROM MemberProductFavorite
WHERE MemberProductId=@str
AND MemberId=@MemberId
IF @count =0
BEGIN
INSERT INTO MemberProductFavorite
(MemberId,
MemberProductId)
VALUES
( @MemberId,
@str)
END
END
RETURN
GO
Hope this helps
Thanks