Can a stored procedure have output parameters and return statement? If so c开发者_运维技巧an anybody give me a simple example.thank you all.
Stored procedure can return integer type only in a return statement and can have any number of out parameters. See this for references supporting this.
Simplest eg of stored procedure
Return integer value from SP
CREATE procedure [sys].[sp_name]
(
@var1 bit = 0,
@publisher smallint
)
AS
BEGIN
IF @var1<> 0
RETURN (@publisher )
END
Using Out Parameter
CREATE PROCEDURE GetImmediateManager
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END
You can use Transactions, Exception handling(try Catch), DDL and DML Queries, calling another stored procedure within one stored procedures and many more operations.
Please comments for more functionalities supported in stored procedure
If you mean the standard RETURN statement that gives an integer then yes
If you mean a UDF RETURN , then no. But a stored proc can have a normal SELECT
You are free to use both OUTPUT
params and a single RETURN
value:
CREATE PROCEDURE RaiseToPower (
@IN INT,
@OUT INT OUTPUT
)
AS
DECLARE @POWER INT
SET @POWER = 3
SET @OUT = POWER(@IN, @POWER)
RETURN @POWER
GO
/**/
DECLARE @POW INT, @RESULT INT
EXEC @POW = dbo.RaiseToPower 2, @RESULT OUTPUT
SELECT 2, 'raised to', @POW, 'is', @RESULT
>> 2 raised to 3 is 8
No. It is either a stored procedure or a scalar function.
A scalar function returns a value and takes 0 to n in parameters.
A stored procedure can take from 0 to n input parameters and can have 0 to n output parameters.
精彩评论