There are a few ways that this could be accomplished; however, there is not enough information in the original question to know which method will work for you.
If the databases are on the same server AND the sql user has permission AND the second database to be accessed is constant you can alter the stored procedure to do the insert into the second DB
ALTER PROCEDURE dbo.SP1 (
@Value1 INT,
@Value2 INT
) AS
BEGIN
INSERT LocalTable (Col1, Col2)
VALUES (@Value1, @Value2)
INSERT SecondDatabase.dbo.RemoteTable (Col1, Col2)
VALUES (@Value1, @Value2)
END
If this is not the case, then you are going to need to bring values back from the stored procedure via
OUTPUT
parameters, AND retrieve them AND open up your second connection and execute another command
ALTER PROCEDURE dbo.SP1 (
@Value1 INT,
@Value2 INT,
@Value3 INT OUTPUT
) AS
BEGIN
INSERT LocalTable (Col1, Col2)
VALUES (@Value1, @Value2)
INSERT SecondDatabase.dbo.RemoteTable (Col1, Col2)
VALUES (@Value1, @Value2)
SET @Value3 = @Value1 + @Value2 + @Value3
END
and your calling code would need to add in the new parameter, and set it to OUTPUT or INPUT/OUTPUT
Note: I skipped the usings,try, catch, open, close for brevity
SqlConnection conn1 = new SqlConnection(ConnectionString1);
SqlCommand cmd1 = new SqlCommand(CommandText1, conn1) {CommandType = CommandType.StoredProcedure};
cmd1.Parameters.AddWithValue("@Value1", Value1);
cmd1.Parameters.AddWithValue("@Value2", Value2);
SqlParameter Param3 = new SqlParameter("@Value3", Value3);
Param3.Direction = ParameterDirection.InputOutput;
cmd1.Parameters.Add(Param3);
cmd1.ExecuteNonQuery();
int NewValue3 = (int)Param3.value;
SqlConnection conn2 = new SqlConnection(ConnectionString2);
SqlCommand cmd2 = new SqlCommand(CommandText2, conn2) {CommandType= CommandType.StoredProcedure};
cmd2.Parameters.AddWithValue("@Value1", Value1);
cmd2.Parameters.AddWithValue("@Value2", Value2);
cmd2.Parameters.AddWithValue("@Value2", Value3);
cmd2.ExecuteNonQuery();
I hope all this helps