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;
Your problem stems from the fact that once the transaction becomes uncommittable (i.e. an error is raised) your loop does not honor the failure and continues to insert data into the table. When the DBE attempts to commit your subsequent changes it cannot because the transaction is no longer valid. Moving your TRY/CATCH
outside of the WHILE
loop resolves part of your problem.
CREATE PROCEDURE [dbo].[ErrorTest]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @error TABLE (m nvarchar(500), d datetime2(7));
DECLARE @Try int = 0;
DECLARE @MaxTries int = 5;
BEGIN TRAN;
BEGIN TRY
WHILE @Try < @MaxTries
BEGIN
print 'begin try - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
INSERT INTO ErrorTestTable (a)
VALUES (1);
INSERT INTO ErrorLogTable(m,d)
VALUES ('successfully inserted record!', sysutcdatetime());
SET @Try += 1;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT 'begin catch - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
INSERT INTO @error(m,d)
VALUES ('pk violation!', sysutcdatetime());
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
INSERT ErrorLogTable(m,d)
SELECT *
FROM @error;
END
GO
However, once the transaction is rolled back any numbers entered during the transaction will be lost.
Also, from experience I have found problems issuing an INSERT
statement from inside a CATCH
block. Sometimes this INSERT
can also be rolled back with the transaction. However, as you can see from my answer there is a work-around which is to declare a table variable, insert your custom state into that and then perform the logging after you have correctly handled the transaction. Any data inserted into the table variable will still be available after the transaction is committed/rolled back.
You could use this to capture the numbers that are being entered, and then when the transaction encounters the primary key violation you could reinsert the numbers from the tracked table up to the number that failed, like so:
DECLARE @numbers TABLE (a INT);
DECLARE @hasError BIT = 0;
BEGIN TRAN;
BEGIN TRY
WHILE @value < @max
BEGIN
INSERT ErrorTestTable VALUES (@value);
INSERT @numbers VALUES (@value); -- this will not be reached if an error occurs...
SET @value += 1;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @hasError = 1;
END CATCH
-- re-insert the numbers that were successful
IF @hasError = 1
BEGIN
INSERT ErrorTestTable
SELECT a
FROM @numbers;
END
If you want to ensure that the subsequent numbers are entered, even if one number fails you will need to add logic outside of the transaction to do it. Personally I would rewrite the stored procedure to use a set-based operation instead of the manual loop and also include logic to try and avoid the primary key violation altogether, like so:
ALTER PROCEDURE [dbo].[ErrorTest] (
@value INT OUTPUT,
@max INT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @error TABLE (m nvarchar(500), d datetime2(7));
BEGIN TRAN;
BEGIN TRY
WITH numbers
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY o.object_id) AS RN
FROM sys.objects AS o
CROSS JOIN sys.objects AS p
)
INSERT ErrorTestTable
SELECT RN
FROM numbers
WHERE rn between @value and @max
AND NOT EXISTS (SELECT * FROM ErrorTestTable WHERE a = RN);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
INSERT INTO @error(m,d)
VALUES ('pk violation!', sysutcdatetime());
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
INSERT ErrorLogTable(m,d)
SELECT *
FROM @error;
END
GO
Best Answer
The fact of importing or creating a Stored Procedure, does not include its execution.
After creating the SP (via MySQL Shell or via bootstrap file) you must call it, in order to process the routines which are stored in it.
Stored Procedure
Call your Stored Procedure...
Extra Comment: Creation of Stored Procedures via MySQL Shell
use your_database
) where this SP will be used, or else, as you try to create the SP, the MySQL Shell will return:ERROR 1046 (3D000): No database selected
.DELIMITER ;
) in order to perform the normal commands on MySQL Shell, after creating your SP.