Creating a SQL Server stored procedure for Person/Customer/Worker relation-CodePudding

I had a three SQL Server tables. I tried to create a stored procedure that Person table where I store person information, Customer table that takes the info from person table, but it holds the person status, and the same for worker data table.

Sample data:

Tables:

Person                          Customer                     Worker
ID(BigInt)                      ID(BigInt)                   ID(BigInt)
FirstName(nvarchar(100))        PersonID(BigInt)             PersonID(BigInt)
MiddleNameS(nvarchar(100))      Status(nvarchar(100))        Status(nvarchar(100))
Surname(nvarchar(100))              
Phone(varchar(20))              
EMail(nvarchar(100))

Stored procedure: InsertCustomer has all person info of person when execute the procedure it should take the person info and store it in the Person table and Customer table need to keep the customerID, personID and status.

CodePudding user response：

You tagged your question as TSQL, so I'm assuming that is what you want, despite using an invalid syntax to describe the tables.

First, let's talk about providing demo data and objects. This is a straightforward way to do so, that anyone can just copy and paste:

DECLARE @Person TABLE (ID BIGINT IDENTITY, FirstName NVARCHAR(100), MiddleNameS NVARCHAR(100), Surname NVARCHAR(100), Phone VARCHAR(20), EMail NVARCHAR(100));
DECLARE @Customer TABLE (ID BIGINT IDENTITY, PersonID BIGINT, Status NVARCHAR(100));
DECLARE @Worker TABLE (ID BIGINT IDENTITY, PersonID BIGINT, Status NVARCHAR(100));

You can insert demo data into these tables easily, but for this case we don't need any.

On to the question. The previous comment is correct. Based on your description this should be one table, not three. The CustomerStatus and WorkerStatus are just properties of your Person object. They have no other properties, and wouldn't exist multiple times.

This makes the correct answer for the content of your stored procedure something like:

INSERT INTO @Person (FirstName, MiddleNameS, Surname, Phone, EMail, CustomerStatus, WorkerStatus) VALUES
(@FirstName, @MiddleNameS, @Surname, @Phone, @Email, @CustomerStatus, @WorkerStatus);

This simply inserts the values (assuming they would be passed to the sproc) into the table.

If you insist you want to use the three table schema, you're going to be looking at some additional complexity, but there is fairly standard way to do it.

DECLARE @holder TABLE (PersonID BIGINT, CustomerStatus NVARCHAR(100), WorkerStatus NVARCHAR(100))
INSERT INTO @Person (FirstName, MIddleNameS, Surname, Phone, Email) 
OUTPUT INSERTED.ID, @CustomerStatus, @WorkerStatus INTO @holder  (PersonID, CustomerStatus, WorkerStatus) VALUES 
(@FirstName, @MiddleNameS, @Surname, @Phone, @Email);

INSERT INTO @Customer (PersonID, Status)
SELECT PersonID, CustomerStatus
  FROM @holder;

INSERT INTO @Worker (PersonID, Status)
SELECT PersonID, WorkerStatus
  FROM @holder;

Let's look over what's going on here. First we declare a table variable to hold the generated ID and the worker and customer status values for us.

Then we perform an insert into Person, using an OUTPUT clause. This allows us to capture the row values inserted into the table.

Now we just need to insert the values into their tables with the statuses.

This answers your question, even though you're being advised to adjust your schema. We don't know all the reasons you might want to do this, maybe somewhere in the requirements it becomes necessary, we don't know.

Finally a word on the sort of scenario you might want to use a schema like this. Consider contact information. You may want to store multiple different values for a single Person (cell phone, email, home phone, work email etc). Because you have a one-to-many relationship it makes total sense for it to be broken away from the parent table (my comment about sub-properties earlier).