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?
Add a comment
|
3 Answers
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;
4 Comments
Daniel Kienböck
would it be better to create a stored function with a returnvalue? Does it make much difference to return a resultSet instead?
Daniel Kienböck
btw. if you (pikk) want to compare the password I would advice you to store them hashed... link
pikk
Hm, i am trying to run in Visual Studio 2010 using C# and the Debugger says "Procedure or function 'sp_getPermission' expects parameter '@permission', which was not supplied.". But it is the output, what do I have to supply?
Gustavo F
@pikk: you have to declare the parameter anyway, but, after the SP executes, the value will change.
Just to add to Gustavo F point, the ParameterDirection of the output parameter should be set to ParameterDirection.Output.
Comments
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
