Sri Sri - 6 months ago 14
SQL Question

Is it good to insert 2 table at a time by using Single Stored Procedure

i have 2 table Account Table and Image Table and i want to insert this two table at a time when users create Account. Is that below Stored Procedure is good to insert 2 tables at a time or is their any other best way that i can do...


Create procedure spAccountImageInsert(@AccID int, @AccName varchar(50), @Email money,
@ImageID int, @ImageFile image)
as
begin
Insert into Account(AccID,AccName,Email) values(@AccID, @AccName, @Email)
Insert into Image(AccID,ImageID, ImageFile) values(@AccID, @ImageID, @ImageFile)
end

Answer

It's fine but always remember this could eventually grow. If other developers touch your code this could get ugly.

What you can do is create two stored procedures one to insert an account and one to insert an image. This at least gives you some sort of separation between your entities (this allows you to reuse names and avoids various conflicts, not to mention other things you may want to do in your sproc). Once you insert the account you can call the stored procedure to insert an image, like so:

CREATE PROCEDURE InsertAccount
@AccountID int
AS BEGIN
--first insert into an account
INSERT INTO ... VALUES ...
--then insert into an image
EXEC dbo.InsertImage
END

Or even better a stored procedure that just executes each one:

EXEC dbo.InsertAccount
EXEC dbo.InsertImage

And don't forget about triggers...that could be a possible solution as well.