Stored procedure with OUTPUT and READONLY
There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes. This topic provides information on output parameters. For other two, see https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver15 .
A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application. For example,
CREATE PROCEDURE GetImmediateManager @employeeID INT, @managerID INT OUTPUT AS BEGIN SELECT @managerID = ManagerID FROM HumanResources.Employee WHERE EmployeeID = @employeeID END
Source: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-output-parameters?view=sql-server-ver15
If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure. Here is another example,
USE AdventureWorks2012; GO IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetEmployeeSalesYTD; GO CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD @SalesPerson nvarchar(50), @SalesYTD money OUTPUT AS SET NOCOUNT ON; SELECT @SalesYTD = SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson; RETURN GO
The following example calls the procedure created in the first example and saves the output value returned from the called procedure in the @SalesYTD variable, which is local to the calling program.
DECLARE
@SalesYTDBySalesPerson money; EXECUTE Sales.uspGetEmployeeSalesYTD, N'Blythe',
@SalesYTD = @SalesYTDBySalesPerson OUTPUT; GO
Input values can also be specified for OUTPUT parameters when the procedure is executed. This allows the procedure to receive a value from the calling program, change or perform operations with the value, and then return the new value to the calling program. In the previous example, the @SalesYTDBySalesPerson variable can be assigned a value before the program calls the Sales.uspGetEmployeeSalesYTD procedure. The execute statement would pass the @SalesYTDBySalesPerson variable value into the @SalesYTD OUTPUT parameter. Then in the procedure body, the value could be used for calculations that generate a new value. The new value would be passed back out of the procedure through the OUTPUT parameter, updating the value in the @SalesYTDBySalesPerson variable when the procedure exits. This is often referred to as "pass-by-reference capability."
If you specify OUTPUT for a parameter when you call a procedure and that parameter is not defined by using OUTPUT in the procedure definition, you get an error message. However, you can execute a procedure with output parameters and not specify OUTPUT when executing the procedure. No error is returned, but you cannot use the output value in the calling program.
For table-valued parameters, however, you cannot use OUTPUT. Instead, you must define it as READONLY, which means that the rows in the table are static. Hence, unlike other parameter values, if you try to INSERT, UPDATE, or DELETE rows you receive an error. For example,
ALTER PROCEDURE Examples.Machine_MultiSelect @MachineList Examples.SurrogateKeyList READONLY AS SET NOCOUNT ON; SELECT Machine.MachineId, Machine.MachineNumber FROM Examples.Machine
JOIN @MachineList AS MachineList ON MachineList.SurrogateKeyId = Machine.MachineId;
Call this procedure as follows,
DECLARE
@MachineList Examples.SurrogateKeyList; INSERT INTO
@MachineList (SurrogateKeyId) VALUES
(1),
(3); EXECUTE Examples.Machine_MultiSelect
@MachineList = @MachineList;
we then create a table USER DEFINED TYPE, but this time it is defined in a specific manner. We named this USER DEFINED TYPE the same as the TABLE object to reinforce that they are different name spaces,
CREATE TYPE Examples.Machine AS TABLE ( MachineId int NOT NULL PRIMARY KEY, MachineNumber char(3) NOT NULL UNIQUE, Description varchar(50) NOT NULL );
Now, create the STORED PROCEDURE to insert rows by using this table type for the parameter:
CREATE PROCEDURE Examples.Machine_MultiInsert @MachineList Examples.Machine READONLY AS SET NOCOUNT ON; INSERT INTO
Examples.Machine(MachineId, MachineNumber, Description) SELECT MachineId, MachineNumber, Description FROM @MachineList;
Now you can call this STORED PROCEDURE after inserting rows into a table variable that works in a very natural manner:
DECLARE
@NewMachineRows Examples.Machine; INSERT INTO
@NewMachineRows (MachineId, MachineNumber, Description) VALUES
(4,'004','NewThing4'),
(5, '005','NewThing5'); EXECUTE
Examples.Machine_MultiInsert @MachineList = @NewMachineRows;
A table-valued function has ability to combine the results from it with other views and tables.
Example,
Answer:
