How to Declare Input-Output Parameters in SQL Server Stored Procedure/Function

How To Declare Input-Output Parameters In SQL Server Stored Procedure/Function?

If you declare a parameter as OUTPUT, it acts as Both Input and OUTPUT

CREATE PROCEDURE SimpleInOutProcedure 
(
    @p_InputInt  INT,
    @p_OutputInt INT OUTPUT
)
AS
BEGIN
    SELECT 
       @p_OutputInt = @p_OutputInt
END
GO

DECLARE @p_OutputInt int = 4
EXEC SimpleInOutProcedure @p_InputInt = 1, @p_OutputInt = @p_OutputInt OUTPUT
SELECT @p_OutputInt

Execute stored procedure with an Output parameter?

The easy way is to right-click on the procedure in Sql Server Management Studio (SSMS), select 'Execute stored procedure..." and add values for the input parameters as prompted. SSMS will then generate the code to run the procedure in a new query window, and execute it for you. You can study the generated code to see how it is done.

Stored procedure output variable being stored as input/output type

As @GSerg commented, you can't.

In SQL, an OUTPUT parameter acts as both INPUT and OUTPUT, see this answer

You're obtaining an INOUT value because of the mapping used to refer ORACLE procedure parameter types, which are IN, OUT and INOUT.

From dba-oracle.com:

A variable passed as mode IN is always read-only. A variable
using IN mode can be read and used by the procedure/function but can
not be changed and it cannot be the receiver of an assignment
operation.

A variable passed in OUT mode is used to pass information back from
the procedure to the calling program. It is a write-only variable and
has no value until the block assigns it a value.

A variable passed in INOUT mode has characteristics of both the IN and
the OUT mode. The variable value is passed in and can be read by the
procedure. The procedure can also change the value and it will be
copied back to the passed variable when the procedure completes.

How do I specify parameter output only direction in a stored procedure in MS SQL Server 2012?

As far as I know, you can't do that in MSSQL, and you would have to specify the direction when calling the SP (as well as in the parameter declaration of the SP itself).

The documentation you found with C# examples is for calling an MSSQL SP from .NET C# code.

Hope this helps.

Returning output parameter from stored procedure

You need to pass parameters, as neither have default values. As @p_prefix is an OUTPUT parameter, in order to consume that value, you need to declare a variable to pass to the parameter:

DECLARE @p_prefix varchar(3);
EXEC SFT.usp_GetPrefix @p_table = 'MAC_CHEESE', @p_prefix OUTPUT;

--PRINT @p_prefix; --Or whatever you're going to do with the value.

As a side note, you can make your Procedure considerably shorter:

CREATE PROCEDURE SFT.usp_GetPrefix @p_table  VARCHAR(30), @p_prefix VARCHAR(3) OUTPUT AS
BEGIN

    SET @p_prefix = CASE LEFT(@p_table,3) WHEN 'MAC' THEN 'MAC' ELSE 'UNK' END;

END

Stored Procedure with both input and output parameters

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;

How to get input and output parameter to the all stored procedures in T-SQL

You can use this query to get all the parameters in stored procedures

SELECT 
    SCHEMA_NAME(SCHEMA_ID) AS [Schema], 
    SO.name AS [Object Name],
    SO.Type_Desc AS [Object Type (UDF/SP)],
    P.parameter_id AS [Parameter ID],
    P.name AS [Parameter Name],
    TYPE_NAME(P.user_type_id) AS [Parameter Datatype],
    P.max_length AS [ParameterMaxBytes],
    P.is_output AS [IsOutPutParameter]
FROM 
    sys.objects AS SO
INNER JOIN 
    sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID
WHERE 
    SO.OBJECT_ID IN (SELECT OBJECT_ID 
                     FROM sys.objects
                     WHERE TYPE IN ('P', 'FN'))
ORDER BY 
    [Schema], SO.name, P.parameter_id

How to set multiple output parameters in stored procedures

Finally, I was able to achieve the desired result with the below code :

    --Creating a table variable
    declare @TempTable table(ticketnum varchar(50))

    --Inserting values in table variable using procedure
    insert @TempTable
    select TICKETNUMBER 
        FROM TicketHistory th 
    WHERE TICKETTIME IN 
    (
        SELECT MAX(S2.TICKETTIME) 
        FROM TicketHistory S2 
        WHERE th.TICKETNUMBER = S2.TICKETNUMBER
    )  
        AND th.CURRENTSTATUS_ANALYST = 'Resolved'
        AND th.TICKETTIME < dateadd(day, -5, GETDATE())

    --Selecting values from table variable
    SELECT * from @TempTable
            
    DECLARE @ticket_number varchar(100)
    DECLARE cur CURSOR FOR SELECT ticketnum FROM @TempTable
    OPEN cur

    FETCH NEXT FROM cur INTO @ticket_number

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            insert into TicketHistory (CURRENTSTATUS_ANALYST,TICKETNUMBER,PREVIOUSSTATUS_ANALYST,TICKETTIME,FIELD,CREATEDBY)
              values('Closed', @ticket_number, 'Resolved', CURRENT_TIMESTAMP, 'Status', 'User.Auto')
            FETCH NEXT FROM cur INTO @ticket_number
        END

    CLOSE cur    
    DEALLOCATE cur
        
    END  

---

Related Topics