2

My database model is quite simple. I have the following tables: Monitor, Computer, and Monitor_Computer_Map as my mapping table for a many-to-many relationship between the other two tables.

I have a script that will run on end users' computers as the user. (This is why I chose a stored procedure in the first place. Is there a better suggestion?). I need to insert data about the user's computer into the Computer table and data about the attached monitors into the Monitor table. What I would like to do is map the monitors to the computers in the mapping table. Is it possible to do all this in a stored procedure?

p.s I'm new to working with databases and I may be going about this all wrong.

EDIT: With the help of @Michael Green i was able to come up with the script below. However, I still can't get it working. Currently I'm reading up on how variables are set and used in SQL so I'm slowly getting it. Also, I'm using a Powershell script to do the inserting which I'm pretty sure I have on lock.

Because my database schema is so simple I'll just post it here. Note: All timestamps are set in the default values. enter image description here

-- My Table Parameter Value (TVP)
CREATE TYPE ComputerTableType AS TABLE
(Name nvarchar(15),
IPAddress nvarchar(15))


-- My Stored Procedure (SP)
CREATE PROCEDURE sp_InsertUpdateSerialInventory
@Serial nvarchar(50),
@TVPComputer ComputerTableType READONLY

AS
DECLARE @TVPComputer TABLE (Computers int)
INSERT INTO dbo.Computers (Name, IPAddress)
OUTPUT INSERTED.Id
INTO @TVPComputer
VALUES (@TVPComputer);
INSERT INTO dbo.Monitors(Serial)
OUTPUT INSERTED.Id
INTO @Serial
VALUES (@Serial);

EDIT #2: Still learning... So I've come to the conclusion i don't actually need a Table Parameter Value(TPM) for my Computers table. I only have two fields which isn't a big deal In my opinion. So what I've done is add each parameter to the top of my Stored Procedure and continue with following @Michael Green's suggestion. I can't seem to get the Table Variable working correctly. Below is my updated code. Still not working! I keep getting a "Must declare the scalar variable". I found some other topics on this error on Stack Exchange but nothing that resolves my issue. It looks like a scope issue or something?

CREATE PROCEDURE sp_InsertUpdateSerialInventory
@Serial nvarchar(50), 
@Name nvarchar(15),
@IPAddress nvarchar(15)

AS
BEGIN

    DECLARE @MonitorId TABLE (Id int);
    INSERT INTO dbo.Monitors (Serial)
    OUTPUT inserted.Id INTO @MonitorId(Id)
    VALUES (@Serial)

    DECLARE @ComputerId TABLE (Id int);
    INSERT INTO dbo.Computers (Name, IPAddress)
    OUTPUT inserted.Id INTO @ComputerId(Id)
    VALUES (@Name, @IPAddress);

    INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
    VALUES (@MonitorId, @ComputerId)
END

EDIT #3 - Working! I can't thank you enough @Michael Green for your help. Your answer took me on quite a learning journey which ultimately brought me to a resolution. Below is my working code!

CREATE PROCEDURE InsertUpdateSerialInventory --Fixed Name. That link was a good read thank you!
@Name nvarchar(15),
@IPAddress nvarchar(15),
@Serial MonitorTableType READONLY

AS
BEGIN

    DECLARE @MonitorId TABLE (MId int);

    INSERT INTO dbo.Monitors (Serial)
    OUTPUT inserted.Id INTO @MonitorId(MId)
    SELECT Serial
    FROM @Serial;

    DECLARE @ComputerId TABLE (CId int);

    INSERT INTO dbo.Computers (Name, IPAddress)
    OUTPUT inserted.Id INTO @ComputerId(CId)
    VALUES (@Name, @IPAddress);

    INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)     
    SELECT m.MId, c.CId
    FROM @MonitorId AS m CROSS JOIN @ComputerId AS c;
END
  • Do not use "sp_" as the prefix for a stored procedure's name. It invokes magic within SQL Server and will hurt your runtimes. See here. – Michael Green Mar 12 '15 at 1:27
  • Your ER diagram doesn't seem to model correctly. How can a monitor be attached to two or more computers? It seems to me the relationship is clearly parent-child from computer to monitor. It then follows your monitor table should relate directly to the computer table via a foreign key using the primary key from the computer table. – Queue Mann Mar 12 '15 at 13:06
  • Thanks for your concern @QueueMann. My database is set up to track Computer/Monitors over a period of time. When departments in our organization move, Monitors don't always end up on the same Computer it started with. What i have here is a way to track what computer the monitor is currently plugged into AND the last PC it was plugged into. Mind you, some computers have 2+ monitors. – HiTech Mar 12 '15 at 16:45
  • Ok. You need to declare your variables as int, not a table. Then change all your OUTPUT statements with SELECT <variable name> = Scope_Identity(). That should work. – Queue Mann Mar 12 '15 at 17:25
4

One execution of your script will produce one computer name and a list of one or more monitors. You can pass these to a SQL Server stored procedure, you just have to get the parameter types correct.

The computer name is simplest. With just this the SP definition would look like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100)
as
    ...

I use varchar(100) as an example. You use whatever's right for you. The list of monitors is more difficult because it may contain many values. For this you have to use a table-valued parameter and for that you will have to define a type:

create type MonitorList AS TABLE (
    MonitorName varchar(100)
    );
GO

Now the SP looks like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100),
    @Monitors     MonitorList    READONLY
as
    ...

Now to the body of the SP. If you use the names as the primary key in tables Computer and Monitor the INSERT statements are straightforward. Assuming you have primary keys and foreign keys defined -- you should -- you have to insert in the correct sequence to respect these key definitions i.e. into Computer and Monitor and only then into the mapping table.

insert dbo.Computer (ComputerName)
values (@ComputerName);

The type we defined, and variables created off it, behave just like any other table:

insert dbo.Monitor (MonitorName)
select MonitorName
from @Monitors;

insert Monitor_Computer_Map (ComputerName, MonitorName)
select @ComputerName, MonitorName
from @Monitors;

If your tables have surrogate keys that use IDENTITY, however, you have more work to do. You have to capture these identity values as they are generated in the parent tables and use them in the mapping table. This can be done with the OUPUT clause:

declare @ComputerID table ( ComputerID int);

insert dbo.Computer (ComputerName)
output INSERTED.ID
into @ComputerID
values (@ComputerName);

Do the same thing for monitors and use the local table variables to populate the mapping table.

Of course you want to have appropriate validation, duplicate checking and error handling in the body of the SP, too.

You don't say what scripting language you use. The documentation for it will tell you how to declare and populate stored procedure parameters for SQL Server.

Response to OP's EDIT #2:

First, a few tips. Please post the full error message; it helps immensely with debugging. Second, if you're using SSMS you can double-click an error and it will highlight the code in error. Third, get in the habit of closing your statements with a semicolon. It is not required yet but it will be soon.

If all computers have exactly one monitor then the TVP is not needed. You are correct. How many developers have only one monitor these days? I've seen finance traders' stations with eight. In these cases you do want a TVP. Please, please, please do not be tempted to write @Serial1, @Serial2, @Serial3 ....

Your code will throw the error Must declare the scalar variable "@MonitorId". This is because of your third INSERT statement:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
VALUES (@MonitorId, @ComputerId);

When you use the INSERT..VALUES syntax SQL Server demands that there can be only one value per variable. You have provided table valued variables which could (potentially) hold many values. What you need is the INSERT..SELECT syntax. Yes, I know @ComputerID only has one row but it is a table and it could have many rows. What you need is:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
SELECT m.Id, c.Id
from @ComputerId AS c
cross join @MonitorId AS m;
  • This looks great! I do use a Primary Key of int on all my tables which I also set as my Identity. I'm still wrapping my head around it and trying to put it into an actual stored procedure. If i can get it working I'll be sure to accept your answer. – HiTech Mar 11 '15 at 16:42
  • I got to be honest with you. I'm struggling with this SP. I was successfully able to create a new table with a single column, Type Value Param with a single param, and a SP with a single param to test what i had just learned. But once i added another param and added related tables i got lost. I edited my post to reflect where I'm at. Any help would be appreciated. I'm still reading up declaring scalar variables etc. – HiTech Mar 11 '15 at 21:57
  • If i could, i would have upvoted your answer except i need 15 rep. Just know I'm grateful for all your help! I learned so much!!! My OP was updated with my resolution. – HiTech Mar 12 '15 at 19:42
1

Sure, you can insert records about monitors, computers, and their relationship(s) without a lot trouble. You just need to pass the requisite info to the SP from your script.

And using a SP instead of spreading SQL code all over a bunch of computers/servers is not among the worst ideas I've ever heard.

  • Thanks for the prompt reply. You wouldn't happen to have a simple example on how to do this? I'm either not searching the right keywords (Likely case) or Google doesn't offer much resources on using SP to insert data into a many to many relationship. – HiTech Mar 10 '15 at 20:53
  • The example Michael Green posted is as good as an example will get without knowing your schema, data types, and scripting language. – user41207 Mar 11 '15 at 12:38
  • Okay. Thanks for confirming it's possible to do what I'm trying to achieve. That was part of the problem =) I updated my post with my DB schema and where I'm currently at. – HiTech Mar 11 '15 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.