how to write stored procedure to insert values into two tables?

ajay raju

16y10k

hi
how to write a single stored procedure to insert values into two tables at a time. columns are different in two tables. Please give a stored procedure, urgent please help me.
Thanks.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vasanth16y ago
Accepted answer
Please check the code ,

CREATE PROCEDURE dbo.InsertTwoTables
@address1 nvarchar(500),
@mobileno nvarchar(50),
AS
BEGIN TRY
BEGIN TRANSACTION    -- Start the transaction

     Set Nocount On
     DECLARE @ID int
     INSERT INTO Table1(Address, MobileNo) VALUES (@address1, @mobileno)

     SELECT @ID=@@IDENTITY
     INSERT INTO ImageInAlbum(FKID, Address, MobileNo) VALUES (@ID, @address1, @mobileno)

     -- If we reach here, success!
     COMMIT

END TRY
BEGIN CATCH

-- Error
IF @@TRANCOUNT > 0
     ROLLBACK

-- Raise an error
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

Hope it helps out , please mark as answer.

0

Altaf Patel16y ago

CREATE PROCEDURE sp_myStoredProcedure
   @myInput  int,
   @myString varchar(100),
   @myFloat
AS
begin

Select column1, column2 From Table1 Where column1 = @myInput
Select column1, column2 From Table2 Where column1 = @myString and column2 = myFloat
end

calling store procedure... >>>>>>>
sp_myStoredProcedure ..,..,..

inside begin ---- end  you can write queries and use parameters declare above....

mark answer if usefull..

Thanx

Altaf......