CODEWITHSUNDEEP
sqlsql-serverstored-procedures
pikk
pikk

Reputation: 855

Stored Procedure with both input and output parameters

I have a table with 3 columns: username, password and permission. I am trying to write a stored procedure to accept @username as an input parameter and then output a parameter @permission. How do I do this?

Upvotes: 4

Views: 44124

Answers (3)

Gustavo F
Gustavo F

Reputation: 2206

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;

Upvotes: 9

ram
ram

Reputation: 1

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

Upvotes: 0

Sandeep
Sandeep

Reputation: 399

Just to add to Gustavo F point, the ParameterDirection of the output parameter should be set to ParameterDirection.Output.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.direction%28v=vs.110%29.aspx

Upvotes: 0

Related Questions