Insert into 2 tables with id from first

Question

I have 2 tables

tbl_orgs:

and tbl_location_records:

I am using a stored procedure to insert data into these tables.

Insert Organization

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsOrg]
    (@orgName nvarchar(50),
     @orgCity nvarchar(50),
     @orgArea nvarchar(50),
     @orgTel nvarchar(50),
     @orgEmail nvarchar(50),
     @orgType nvarchar(50),
     @orgStatus nvarchar(50),
     @strOwner nvarchar(50),
     @db_tstamp datetime2)
AS
    SET NOCOUNT OFF;

    INSERT INTO [tbl_orgs] ([orgName], [orgCity], [orgArea], [orgTel], [orgEmail], [orgType], [orgStatus], [strOwner], [db_tstamp]) 
    VALUES (@orgName, @orgCity, @orgArea, @orgTel, @orgEmail, @orgType, @orgStatus, @strOwner, @db_tstamp);

    SELECT 
        orgID, orgName, orgCity, orgArea, orgTel, orgEmail, orgType, 
        orgStatus, strOwner, db_tstamp 
    FROM 
        tbl_orgs 
    WHERE 
        (orgID = SCOPE_IDENTITY())

Insert Location Record

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsLoc]
    (@userID int,
     @orgID int,
     @jobID int,
     @strLat numeric(18, 0),
     @strLong numeric(18, 0),
     @strOwner varchar(50),
     @db_tstamp datetime2)
AS
    SET NOCOUNT OFF;

    INSERT INTO [tbl_location_records] ([userID], [orgID], [jobID], [strLat], [strLong], [strOwner], [db_tstamp]) 
    VALUES (@userID, @orgID, @jobID, @strLat, @strLong, @strOwner, @db_tstamp);

    SELECT 
        recordID, userID, orgID, jobID, strLat, strLong, strOwner, db_tstamp 
    FROM 
        tbl_location_records 
    WHERE 
        (recordID = SCOPE_IDENTITY())

I want to use single form to add inserted organization's location record once organization record successfully inserted.

protected void btnSubmit_Click(object sender, EventArgs e)
{
    string cs = ConfigurationManager.ConnectionStrings["IBS_3"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand("spInsOrg", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("orgName", SqlDbType.NVarChar).Value = txtOrgName.Text;
        cmd.Parameters.Add("orgCity", SqlDbType.NVarChar).Value = txtCity.Text;
        cmd.Parameters.Add("orgArea", SqlDbType.NVarChar).Value = txtArea.Text;
        cmd.Parameters.Add("orgTel", SqlDbType.NVarChar).Value = txtTele.Text;
        cmd.Parameters.Add("orgEmail", SqlDbType.NVarChar).Value = txtEmail.Text;
        cmd.Parameters.Add("orgType", SqlDbType.NVarChar).Value = txtOrgType.Text;
        cmd.Parameters.Add("orgStatus", SqlDbType.NVarChar).Value = txtStatus.Text;
        cmd.Parameters.Add("@strOwner", SqlDbType.VarChar).Value = User.Identity.Name;
        cmd.Parameters.Add("@db_tstamp", SqlDbType.DateTime2).Value = DateTime.Now;

        conn.Open();
        cmd.ExecuteNonQuery();

        SqlCommand cmdloc = new SqlCommand("spInsLoc", conn);
        cmdloc.CommandType = CommandType.StoredProcedure;

        cmdloc.Parameters.Add("orgID", SqlDbType.Int).Value = 
    }
}

Trying to get some understanding from this link but I m clueless on this..

Inserting to one table, insert the ID to second table

Any help appreciated.

Thanks.

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Feb 26 '17 at 20:57
@marc_s sure thanks. I was using 'sp' only though. But sure I'll keep this in mind. — Mashhoor Gulati, Feb 26 '17 at 21:28

score 0 · Accepted Answer · edited Feb 26 '17 at 21:40

Add output parameter to the first stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spInsOrg]
(
  @orgID int output,
  @orgName nvarchar(50),
  @orgCity nvarchar(50),
  @orgArea nvarchar(50),
  @orgTel nvarchar(50),
  @orgEmail nvarchar(50),
  @orgType nvarchar(50),
  @orgStatus nvarchar(50),
  @strOwner nvarchar(50),
  @db_tstamp datetime2
  )
  AS
  SET NOCOUNT OFF;
  INSERT INTO [tbl_orgs] ([orgName], [orgCity], [orgArea], [orgTel], [orgEmail], [orgType], [orgStatus], [strOwner], [db_tstamp]) VALUES (@orgName, @orgCity, @orgArea, @orgTel, @orgEmail, @orgType, @orgStatus, @strOwner, @db_tstamp);
  SELECT @orgID =  SCOPE_IDENTITY()

add output param to command..

        using (SqlConnection conn = new SqlConnection(cs))
        {

            SqlCommand cmd = new SqlCommand("spInsOrg", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            var outParam = cmd.Parameters.Add("@orgID", SqlDbType.Int);
            outParam.Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@orgName", SqlDbType.NVarChar).Value = txtOrgName.Text;
            cmd.Parameters.Add("@orgCity", SqlDbType.NVarChar).Value = txtCity.Text;
            cmd.Parameters.Add("@orgArea", SqlDbType.NVarChar).Value = txtArea.Text;
            cmd.Parameters.Add("@orgTel", SqlDbType.NVarChar).Value = txtTele.Text;
            cmd.Parameters.Add("@orgEmail", SqlDbType.NVarChar).Value = txtEmail.Text;
            cmd.Parameters.Add("@orgType", SqlDbType.NVarChar).Value = txtOrgType.Text;
            cmd.Parameters.Add("@orgStatus", SqlDbType.NVarChar).Value = txtStatus.Text;
            cmd.Parameters.Add("@strOwner", SqlDbType.VarChar).Value = User.Identity.Name;
            cmd.Parameters.Add("@db_tstamp", SqlDbType.DateTime2).Value = DateTime.Now;
            conn.Open();
            cmd.ExecuteNonQuery();
            var orgId = (int)outParam.Value;
            SqlCommand cmdloc = new SqlCommand("spInsLoc", conn);
            cmdloc.CommandType = CommandType.StoredProcedure;

        }

then how to use it in second stored procedure... and then what should I change in my asp.net code.? — Mashhoor Gulati, Feb 26 '17 at 20:36
Getting error "The name 'outParam' does not exist in the current context". — Mashhoor Gulati, Feb 26 '17 at 20:45
check this line .. var outParam = cmd.Parameters.Add("orgID", SqlDbType.Int); — levent, Feb 26 '17 at 20:51
Additional information: Procedure or function 'spInsOrg' expects parameter '@orgID', which was not supplied. — Mashhoor Gulati, Feb 26 '17 at 21:21
Added this as you suggested. var outParam = cmd.Parameters.Add("orgID", SqlDbType.Int); var orgId = (int)outParam.Value; SqlCommand cmdloc = new SqlCommand("spInsLoc", conn); cmdloc.CommandType = CommandType.StoredProcedure; cmdloc.Parameters.Add("orgID", SqlDbType.Int).Value = orgId; — Mashhoor Gulati, Feb 26 '17 at 21:25
Thanks a lot man. This answered my question. I learnt new things. Though found different issues in my db design I guess.But that qualifies for separate question. Thanks again. :) — Mashhoor Gulati, Feb 26 '17 at 22:06

Insert into 2 tables with id from first

1 Answers1