pikk pikk - 3 months ago 10
SQL Question

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?

Answer

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;