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)
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.