mercredi 11 mars 2015

Insert/Update Stored Procedure


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 are not set manually. 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);




Aucun commentaire:

Enregistrer un commentaire