Sri Sri - 1 year ago 55
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)
Insert into Account(AccID,AccName,Email) values(@AccID, @AccName, @Email)
Insert into Image(AccID,ImageID, ImageFile) values(@AccID, @ImageID, @ImageFile)

Answer Source

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:

@AccountID int
--first insert into an account
--then insert into an image
EXEC dbo.InsertImage

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download