none
InputOutput parameter

    Question

  • I need to know how to pass a output parameter which is also used as an input parameter. However, the input is generated in the query itself.

    Thanks All.

    Thursday, October 21, 2010 10:16 AM

Answers

  • The following script demonstrates that an OUTPUT parameter can be used for input as well.

    Warning on DOWNSIDE: very confusing to read

    -- Demo: OUTPUT parameter can be used for INPUT as well
    USE tempdb;
    GO
    CREATE PROC sprocInputOutputComboParm @Number int output
    AS
    BEGIN
    	SET @Number = @Number * @Number
    END
    GO
    
    DECLARE @InOutINT INT = 16
    EXEC sprocInputOutputComboParm @InOutINT output
    SELECT @InOutINT
    -- 256
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Wednesday, October 27, 2010 8:50 AM
    Tuesday, October 26, 2010 11:29 AM
    Moderator

All replies

  • I'm not sure that I understand. What do you mean with "the input is generated in the query itself"? Output parameters appears with stored procedure, and they are always input/output. But you cannot call stored procedures in queries.

    Could you clarify with an example of what you are looking for?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, October 21, 2010 10:28 AM
  • Any Help!!
    Thursday, October 21, 2010 10:29 AM
  • CREATE PROCEDURE [dbo].[Cube] 
    @n AS BIGINT OUTPUT 
    AS 
    SET @n = @n * @n * @n
    GO
    
    DECLARE @n BIGINT
    SET @n = 3
    EXECUTE [dbo].[Cube] @n OUTPUT
    SELECT @n
    
    do you mean like this?
    Jon
    Thursday, October 21, 2010 10:29 AM
  • BOL says

    CREATE PROCEDURE get_sales_for_title
    @title varchar(80),  -- This is the input parameter.
    @ytd_sales int OUTPUT -- This is the output parameter.
    AS 
    
    -- Get the sales for the specified title and 
    -- assign it to the output parameter.
    SELECT @ytd_sales = ytd_sales
    FROM titles
    WHERE title = @title
    
    RETURN
    GO 
    

    The following program executes the stored procedure with a value for the input parameter and saves the output value of the stored procedure in the @ytd_sales_for_title variable local to the calling program.

    -- Declare the variable to receive the output value of the procedure.
    DECLARE @ytd_sales_for_title int
    
    -- Execute the procedure with a title_id value
    -- and save the output value in a variable.
    
    EXECUTE get_sales_for_title
    "Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT 
    
    -- Display the value returned by the procedure.
    PRINT 'Sales for "Sushi, Anyone?": ' +  convert(varchar(6),@ytd_sales_for_title)
    GO
    
    Sales for "Sushi, Anyone?": 4095
    

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 21, 2010 10:30 AM
    Answerer
  • hi cutiepie25,

    hope this will help you,

    ---

    use tempdb
    go
    drop proc proc1
    go
    create proc proc1(@p1 varchar(10) output)
    as
    select @p1+'input & output'
    go
    declare    @return_value int,
            @p1 varchar(10)

    select    @p1 = N'tharindu'

    exec    @return_value = [dbo].[proc1]
            @p1 = @p1 output

    ---

    Thanks

    Tharindu Dhaneenja


    Tharindu Dhaneenja (http://spaces.msn.com/dhaneenja)
    Thursday, October 21, 2010 10:32 AM
  • The following script demonstrates that an OUTPUT parameter can be used for input as well.

    Warning on DOWNSIDE: very confusing to read

    -- Demo: OUTPUT parameter can be used for INPUT as well
    USE tempdb;
    GO
    CREATE PROC sprocInputOutputComboParm @Number int output
    AS
    BEGIN
    	SET @Number = @Number * @Number
    END
    GO
    
    DECLARE @InOutINT INT = 16
    EXEC sprocInputOutputComboParm @InOutINT output
    SELECT @InOutINT
    -- 256
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Wednesday, October 27, 2010 8:50 AM
    Tuesday, October 26, 2010 11:29 AM
    Moderator