How to insert the foreign key into a subtype table in SQL Server

Question

I have a supertype table called Student, and its subtype table called OtherStudents. I am wondering how to write a stored procedure that will insert the foreign key value into OtherStudents table every time a new record with a student type Other is inserted into Student.

Student table:

    CREATE TABLE [dbo].[Student]
    (
        [STUD_ID] [INT] IDENTITY(1000009,1) NOT NULL,
        [STUD_NAM] [VARCHAR](30) NOT NULL,
        [STUD_EMAIL] [VARCHAR](50) NULL,
        [CAMP_NAM] [VARCHAR](50) NULL,
        [CAMP_ZIP] [INT] NULL,
        [STUD_TYP] [CHAR](5) NOT NULL,

        CONSTRAINT [PK_Student] 
            PRIMARY KEY CLUSTERED ([STUD_ID] ASC)
    )
    GO

    ALTER TABLE [dbo].[Student] WITH CHECK 
        ADD CONSTRAINT [FK_Student_Campus] 
            FOREIGN KEY([CAMP_NAM], [CAMP_ZIP]) REFERENCES [dbo].[Campus] ([CAMP_NAM], [CAMP_ZIP])
    GO

    ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Campus]
    GO


`OtherStudents` table:

    CREATE TABLE [dbo].[OtherStudents]
    (
        [OSTUD_ID] [INT] IDENTITY(1000009,1) NOT NULL,
        [STUD_ST] [VARCHAR](30) NULL,
        [STUD_APT] [VARCHAR](5) NULL,
        [STUD_CITY] [CHAR](6) NULL,
        [STUD_STATE] [CHAR](2) NULL,
        [STUD_ZIP] [INT] NULL,
        [RPT_ATTM] [VARBINARY](4000) NULL,
        [RPT_EYESCR] [VARBINARY](4000) NULL,
        [DATE_LASTPASS] [DATE] NULL,
        [DATE_LASTVSP] [DATE] NULL,
        [STUD_ID] [INT] NULL,

        CONSTRAINT [PK_OtherStudents] 
            PRIMARY KEY CLUSTERED ([OSTUD_ID] ASC)
    )
    GO

    ALTER TABLE [dbo].[OtherStudents] WITH CHECK 
        ADD CONSTRAINT [FK_OtherStudents_Student] 
            FOREIGN KEY([STUD_ID]) REFERENCES [dbo].[Student] ([STUD_ID])
    GO

    ALTER TABLE [dbo].[OtherStudents] CHECK CONSTRAINT [FK_OtherStudents_Student]
    GO

I wrote two stored procedures:

        ALTER PROCEDURE [dbo].[BusPassStudents_Insert]
            (@STUD_ST VARCHAR(30),
             @STUD_APT VARCHAR(5),
             @STUD_CITY CHAR(6),
             @STUD_STATE CHAR(2),
             @STUD_ZIP INT,
             @RPT_ATTM AS VARBINARY(4000) = NULL,
             @RPT_EYESCR AS VARBINARY(4000) = NULL,
             @DATE_LASTPASS DATE,
             @DATE_LASTVSP AS DATE = NULL)
        AS
        BEGIN
            SET NOCOUNT ON

            INSERT INTO dbo.OtherStudents (STUD_ST, STUD_CITY, STUD_STATE, STUD_ZIP, RPT_ATTM, RPT_EYESCR,DATE_LASTPASS, DATE_LASTVSP)
            VALUES (@STUD_ST, @STUD_CITY, @STUD_STATE, @STUD_ZIP, @RPT_ATTM, @RPT_EYESCR, @DATE_LASTPASS, @DATE_LASTVSP)
        END

        ALTER PROCEDURE [dbo].[Stud_InsertNew]
            (@STUD_NAM VARCHAR(30),
             @STUD_EMAIL VARCHAR(50),
             @CAMP_NAM VARCHAR(50),
             @CAMP_ZIP INT,
             @STUD_TYP CHAR(5))
        AS
        BEGIN
            SET NOCOUNT ON

            INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
            VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);

            DECLARE @STUD_ID INT
            SET @STUD_ID = SCOPE_IDENTITY()
            SELECT @STUD_ID
            WHERE @STUD_TYP = 'Other'

        END

Here is my C# code I have:

int campzip = int.Parse(ddlCamp.SelectedValue.Trim());
int StudentZip = int.Parse(txtStdZip.Text);
DateTime DateLastPass = DateTime.ParseExact(txtDateLastPass.Text, "yyyy-MM-dd", null);

using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("cis-laredoConnectionString")))
{
    connection.Open();

    var cmd = new SqlCommand("dbo.Stud_InsertNew", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@STUD_NAM", txtStdName.Text);
    cmd.Parameters.AddWithValue("@STUD_EMAIL", txtStdEmail.Text);
    cmd.Parameters.AddWithValue("@CAMP_NAM", ddlCamp.SelectedItem.ToString());
    cmd.Parameters.AddWithValue("@CAMP_ZIP", campzip);
    cmd.Parameters.AddWithValue("@STUD_TYP", "Other");

    int getStudID = (int) cmd.ExecuteScalar();
    cmd.Dispose();

    connection.Close();

    connection.Open();
    var cmd2 = new SqlCommand("BusPassStudents_Insert", connection);
    cmd2.CommandType = CommandType.StoredProcedure;

    cmd2.Parameters.AddWithValue("@STUD_ID", getStudID);
    cmd2.Parameters.AddWithValue("@STUD_ST", txtStdStreet.Text);
    cmd2.Parameters.AddWithValue("@STUD_APT", txtStdApt.Text);
    cmd2.Parameters.AddWithValue("@STUD_CITY", txtStdCity.Text);
    cmd2.Parameters.AddWithValue("@STUD_STATE", txtStdState.Text);
    cmd2.Parameters.AddWithValue("@STUD_ZIP", StudentZip);
    cmd2.Parameters.AddWithValue("@RPT_ATTM", fuAttend.FileBytes);
    cmd2.Parameters.Add("@DATE_LASTPASS", SqlDbType.Date).Value = DateLastPass;

    cmd2.ExecuteNonQuery();
    cmd2.Dispose();

    connection.Close();
}

I am trying to use ExecuteScalar to retrieve the inserted STUD_ID in Student table and post it back into the OtherStudents table.

I am getting error:

System.InvalidCastException: 'Specified cast is not valid.'

Any help would be appreciated!

Answer 1

You have to select something from your stored procedure to get the scalar value in the C# which is you missed, so in your Stud_InsertNew you can select the STUD_ID and use ExecuteScalar() in C# code as:

int getStudID = (int)cmd.ExecuteScalar();

Changes in stored procedure:

ALTER PROCEDURE [dbo].[Stud_InsertNew]
    (@STUD_NAM VARCHAR(30),
     @STUD_EMAIL VARCHAR(50),
     @CAMP_NAM VARCHAR(50),
     @CAMP_ZIP INT,
     @STUD_TYP CHAR(5))
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
    VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);

    // Need this line which returns the latest inserted Student Id
    SELECT TOP 1 [STUD_ID]
    FROM dbo.Student
    ORDER BY [STUD_ID] DESC
END

After a comment from Mitch Wheat and also found here, that the best way to use SCOPE_IDENTIY() if you want to get the latest inserted records primary key's value, so you just need to change the SELECT statement to this:

DECLARE @STUD_ID INT
SET @STUD_ID = SCOPE_IDENTITY()
SELECT @STUD_ID

Answer 2

This is how I'd like to approach this.

1. Convert two procedures into a single one by passing all the required parameters.

2. Make use of the "inserted" table data to get the information that was just Inserted into table A and store the reference in table B.

*(i) You may want to pass the rest of the parameters required to store the data in table B. I haven't included them in the stored procedure.

(ii) I'm trying to insert data into table B if only the check on birthday passes. In your case the predicate/condition will differ.

USE SOMEDB;


CREATE TABLE A
(Id Integer IDENTITY(1,1),
[Name] VARCHAR(20),
[Birthday] DATE
)

create table B
(
B_Id INTEGER IDENTITY(10001,1),
A_Id Integer NULL,
ColumnX VARCHAR(20),
ColumnY varchar(20))

CREATE PROCEDURE [uspInsertIndividualDetails]
(@name varchar(20),
@birthday date
)
AS
BEGIN

CREATE TABLE #tmpXYZ(iD INT,birthday date);

INSERT INTO A([Name],[Birthday])
OUTPUT inserted.Id,inserted.Birthday into #tmpXYZ
select @name, @birthday


IF exists (select 1 from #tmpXYZ where Birthday >'2018-01-01')
begin
INSERT INTO B(A_Id,ColumnX, ColumnY)
SELECT  Id,'A Value', 'Some Other Value' FROM #tmpXYZ
end

END
execute [uspInsertIndividualDetails] @name ='John Doe', @birthday = '1972-10-01'

SELECT * FROM a
SELECT * FROM b
execute [uspInsertIndividualDetails] @name ='John DoeJr', @birthday = '2018-10-01'

SELECT * FROM a
SELECT * FROM b

Hope this helps.

Answer 3

You can do this without making database connection twice by making one stored procedure which combines the two stored procedure's input parameters as:

Reformed stored procedure:

ALTER PROCEDURE [dbo].[Stud_InsertNew]
    (@STUD_NAM VARCHAR(30),
     @STUD_EMAIL VARCHAR(50),
     @CAMP_NAM VARCHAR(50),
     @CAMP_ZIP INT,
     @STUD_TYP CHAR(5),
     // Parameters for other student
     @STUD_APT VARCHAR(5),
     @STUD_CITY CHAR(6),
     @STUD_STATE CHAR(2),
     @STUD_ZIP INT,
     @RPT_ATTM AS VARBINARY(4000) = NULL,
     @RPT_EYESCR AS VARBINARY(4000) = NULL,
     @DATE_LASTPASS DATE,
     @DATE_LASTVSP AS DATE = NULL)
AS
BEGIN
    DECLARE @OTHERCONSTANT NVARCHAR(MAX)

    SET @OTHERCONSTANT = 'Other' // You can set to a text that coming from C#

    SET NOCOUNT ON

    INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
    VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);

    IF(@STUD_TYP = @OTHERCONSTANT)
    BEGIN
        // Need this line which returns the latest inserted Student Id
        DECLARE @STUD_ID  INT = NULL
        SET @STUD_ID = SCOPE_IDENTITY()

        INSERT INTO dbo.OtherStudents (STUD_ST, STUD_CITY, STUD_STATE, STUD_ZIP, RPT_ATTM, RPT_EYESCR,DATE_LASTPASS, DATE_LASTVSP)
        VALUES (@STUD_ST, @STUD_CITY, @STUD_STATE, @STUD_ZIP, @RPT_ATTM, @RPT_EYESCR, @DATE_LASTPASS, @DATE_LASTVSP)
    END
END

C# code:

using(var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("cis-laredoConnectionString"))) {
 connection.Open();
 var cmd = new SqlCommand("dbo.Stud_InsertNew", connection);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("@STUD_NAM", txtStdName.Text);
 cmd.Parameters.AddWithValue("@STUD_EMAIL", txtStdEmail.Text);
 cmd.Parameters.AddWithValue("@CAMP_NAM", ddlCamp.SelectedItem.ToString());
 cmd.Parameters.AddWithValue("@CAMP_ZIP", campzip);
 cmd.Parameters.AddWithValue("@STUD_TYP", "Other");

 cmd.Parameters.AddWithValue("@STUD_ST", txtStdStreet.Text);
 cmd.Parameters.AddWithValue("@STUD_APT", txtStdApt.Text);
 cmd.Parameters.AddWithValue("@STUD_CITY", txtStdCity.Text);
 cmd.Parameters.AddWithValue("@STUD_STATE", txtStdState.Text);
 cmd.Parameters.AddWithValue("@STUD_ZIP", StudentZip);
 cmd.Parameters.AddWithValue("@RPT_ATTM", fuAttend.FileBytes);
 cmd.Parameters.Add("@DATE_LASTPASS", SqlDbType.Date).Value = DateLastPass;

 cmd.ExecuteNonQuery();
 cmd.Dispose();

 if (connection.State == ConnectionState.Open) {
  connection.Close();
 }
}