Ggtcf

I have a procedure like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[INSERT_MessageOwner]
 @ownertype AS INT,
 @ownertenant AS INT
AS
BEGIN
 SET NOCOUNT ON;

 INSERT INTO ADMINROTAS.dbo.MessageOwner
 OUTPUT Inserted.MessageOwnerID
 VALUES (@ownertype, @ownertenant);
END

If I call that procedure like this:

DECLARE @messageOwnerType INT;
SET @messageOwnerType = 3;
...
EXECUTE [ADMINROTAS].[dbo].INSERT_MessageOwner @messageOwnerType
...

I get

Procedure or function 'INSERT_MessageOwner' expects parameter '@ownertenant', which was not supplied.

If I call it like this:

DECLARE @messageOwnerType INT;
DECLARE @messageOwnerDB INT;
SET @messageOwnerType = 3;
SET @messageOwnerDB = DB_ID();
...

EXECUTE [ADMINROTAS].[dbo].INSERT_MessageOwner @messageOwnerType, @messageOwnerDB
...

I get

Procedure or function INSERT_MessageOwner has too many arguments specified.

Does anyone know what is going on here? I guess it may be something to do with the OUTPUT clause, but I am having difficulty figuring it out or finding just the right teaching on it.

I would really appreciate it if someone could put me in the picture. Having said all this, is there something wrong with this error message? You left off an argument! Add one. Now you have too many!

As an additional, I would be really grateful if you could help me understand how to get the results of the stored procedure into a variable. This stored procedure has worked usefully in JDBC, but now I want to use it with T-SQL.

Thank you so much.

few missing () and it's good policy to add your column names for insert

ALTER PROCEDURE [dbo].[INSERT_MessageOwner] (
 @ownertype AS INT,
 @ownertenant AS INT
)
AS
BEGIN
 SET NOCOUNT ON;

 INSERT INTO ADMINROTAS.dbo.MessageOwner (yourcolumn1, yourcolumn2)
 OUTPUT Inserted.MessageOwnerID
 VALUES (@ownertype, @ownertenant);
END

I have no problem running this:

CREATE DATABASE ADMINROTAS

GO

CREATE TABLE ADMINROTAS.dbo.MessageOwner
(
 MessageOwnerID INT IDENTITY PRIMARY KEY,
 ownertype INT, 
 ownertenant INT
)

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[INSERT_MessageOwner]
 @ownertype AS INT,
 @ownertenant AS INT
AS
BEGIN
 SET NOCOUNT ON;

 INSERT INTO ADMINROTAS.dbo.MessageOwner
 OUTPUT Inserted.MessageOwnerID
 VALUES (@ownertype, @ownertenant);
END

GO

DECLARE @messageOwnerType INT;
SET @messageOwnerType = 3;
EXECUTE [ADMINROTAS].[dbo].INSERT_MessageOwner @messageOwnerType

GO

DECLARE @messageOwnerType INT;
DECLARE @messageOwnerDB INT;
SET @messageOwnerType = 3;
SET @messageOwnerDB = DB_ID();

EXECUTE [ADMINROTAS].[dbo].INSERT_MessageOwner @messageOwnerType, @messageOwnerDB

Maybe you missed something when writing the question.
I suppose that MessageOwnerID is the identity column of your table. To get this value, you first need to declare a table varible like @Temp with the structure of the column you want output from your insert:

DECLARE @Temp TABLE
( 
 MessageOwnerID INT
)

Second, you need to change your OUTPUT statement to insert this result into the variable:

OUTPUT Inserted.MessageOwnerID INTO @Temp

You now have this results in your table. You can write a select statement at the end of your procedure, or because you are sure you are inserting only one record, you can declare a variable @MessageOwnerID let's say. In order to return this value to the caller, you have to declare it as OUTPUT parameter. Set it's value to the value from the temp table and read it on execute. Your procedure is modifying like this:

ALTER PROCEDURE [dbo].[INSERT_MessageOwner]
 @ownertype AS INT,
 @ownertenant AS INT,
 @MessageOwnerID INT = NULL OUTPUT
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @Temp TABLE
 ( 
 MessageOwnerID INT
 )

 INSERT INTO ADMINROTAS.dbo.MessageOwner
 OUTPUT Inserted.MessageOwnerID INTO @Temp
 VALUES (@ownertype, @ownertenant);

 SELECT @MessageOwnerID = MessageOwnerID FROM @Temp
END

Remember the OUTPUT keyword on the parameter declaration!

The call to the procedure changes to this:

DECLARE @messageOwnerType INT;
DECLARE @messageOwnerDB INT;
DECLARE @MessageOwnerID INT;
SET @messageOwnerType = 3;
SET @messageOwnerDB = DB_ID();

EXECUTE [ADMINROTAS].[dbo].INSERT_MessageOwner @messageOwnerType, @messageOwnerDB, @MessageOwnerID = @MessageOwnerID OUTPUT
SELECT @MessageOwnerID

Also, remember the OUTPUT keyword on all and the key-value pair of parameter name and variable (you should always use this way to call procedures).

Get a try and come back!