SQL 具有输入和输出参数的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8888021/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure with both input and output parameters
提问by pikk
I have a table with 3 columns: username, passwordand permission. I am trying to write a stored procedure to accept @usernameas an input parameter and then output a parameter @permission. How do I do this?
我有一个包含 3 列的表：username,password和permission。我正在尝试编写一个存储过程来接受@username作为输入参数，然后输出一个参数@permission。我该怎么做呢？
回答by Gustavo F
More might be needed, but according to your question, this is the code:
可能需要更多，但根据您的问题，这是代码：
CREATE PROCEDURE [dbo].[GetPermission]
@userName varchar(50),
@permission int output
AS
BEGIN
select @permission = PERMISSION from USERS where UserName = @userName
END;
EDIT:
编辑：
Another option is to create a function, example:
另一种选择是创建一个函数，例如：
CREATE FUNCTION [dbo].[GetPermission](@userName [varchar(50)])
RETURNS [int]
AS
BEGIN
declare @permission int
select @permission = PERMISSION from USERS where UserName = @userName
return @permission
END;
回答by Sandeep
Just to add to Gustavo F point, the ParameterDirection of the output parameter should be set to ParameterDirection.Output.
只是为了添加到 Gustavo F 点，输出参数的 ParameterDirection 应设置为 ParameterDirection.Output。
回答by ram
CREATE PROC SP_ORDERS AS BEGIN SELECT DISTINCT E.EmployeeID,E.FirstName+SPACE(3)+E.LastName AS CUTNAME,E.City,ET.TerritoryDescription,P.ProductName, OD.Discount,SUM(OD.Quantity*OD.UnitPrice)AS TOTAL FROM [DimOrder Details] OD JOIN DimOrders O ON OD.OrderID=O.OrderID JOIN DimProducts P ON OD.ProductID=P.ProductID JOIN DimEmployees E ON O.EmployeeID=E.EmployeeID JOIN DimCustomers C ON O.CustomerID=C.CustomerID JOIN DimEmployeeTerritories ET ON E.EmployeeID=ET.EmployeeID GROUP BY E.EmployeeID,E.FirstName,E.LastName,E.City,ET.TerritoryDescription,P.ProductName,OD.Discount END
