Hello Friends,

I'm trying to use an output parameter in a function. But it seems the syntax
I used is incorrect. May I know the correct syntax for using an output
parameter in a function.

Sample Function I used:

create function dbo.OutputFunction (@var int output)
returns int
as
begin
     set @x = 100
return 100
end

The error message which I got is:

Error 181: Cannot use the OUTPUT option in a DECLARE statement.
Must declare the variable '@x'.
A RETURN statement with a return value cannot be used in this context.

Thanks.
Praveen.

You cannot have output parameters in a function (although, admittedly, I
haven't played with them much so I could be mistaken).  A function
either returns a single value (a scalar function) or a set of rows (a
table-valued function).  Any parameters you pass into a function are
input only.

From your example, it looks to me like you're trying to do this:

    create function dbo.OutputFunction()
    returns int
    as
    begin
        return 100
    end

    declare @x int
    select @x = dbo.OutputFunction()
    select @x


This should assign the value of 100 to the variable @x using the scalar
function dbo.OutputFunction().  If you really want to pass parameters
into a piece of code and have that code change them you'll need to
implement a stored procedure, like this:

    create proc dbo.OutputProc (@p int output)
    as
        set @p = 100
    go

    declare @MyVar int
    exec dbo.OutputProc @MyVar out
    select @MyVar


Hope this helps.

Show quoteHide quote

Sorry, I didn't test the code and left off a batch terminator at the end
of the create function statement.  Just put a 'GO' at the end of the
create function statement and my example code will work fine:

    create function dbo.OutputFunction()
    returns int
    as
    begin
        return 100
    end
    *go*

    declare @x int
    select @x = dbo.OutputFunction()
    select @x

Show quoteHide quote

Thanks Mike.

I just used that code to show the syntax I used. Ofcourse in that example we needn't use output parameter.

Thank you very much for your help, Mike.
  Sorry, I didn't test the code and left off a batch terminator at the end of the create function statement.  Just put a 'GO' at the end of the create function statement and my example code will work fine:


    create function dbo.OutputFunction()
    returns int
    as
    begin
        return 100
    end
    go

    declare @x int
    select @x = dbo.OutputFunction()
    select @x

  --
  mike hodgson
  http://sqlnerd.blogspot.com



  Mike Hodgson wrote:
    You cannot have output parameters in a function (although, admittedly, I haven't played with them much so I could be mistaken).  A function either returns a single value (a scalar function) or a set of rows (a table-valued function).  Any parameters you pass into a function are input only.

    From your example, it looks to me like you're trying to do this:


      create function dbo.OutputFunction()
      returns int
      as
      begin
          return 100
      end

      declare @x int
      select @x = dbo.OutputFunction()
      select @x


    This should assign the value of 100 to the variable @x using the scalar function dbo.OutputFunction().  If you really want to pass parameters into a piece of code and have that code change them you'll need to implement a stored procedure, like this:


      create proc dbo.OutputProc (@p int output)
      as
          set @p = 100
      go

      declare @MyVar int
      exec dbo.OutputProc @MyVar out
      select @MyVar


    Hope this helps.

    --
    mike hodgson
    http://sqlnerd.blogspot.com



    praveen wrote:
Hello Friends,

I'm trying to use an output parameter in a function. But it seems the syntax
I used is incorrect. May I know the correct syntax for using an output
parameter in a function.

Sample Function I used:

create function dbo.OutputFunction (@var int output)
returns int
as
begin
     set @x = 100
return 100
end

The error message which I got is:

Error 181: Cannot use the OUTPUT option in a DECLARE statement.
Must declare the variable '@x'.
A RETURN statement with a return value cannot be used in this context.

Thanks.
Praveen.

Hi Praveen,

can't work because a scalar function can not use an OUTPUT-Parm.

Try this - hope this helps:

alter function dbo.OutputFunction (@var int) returns int
as
begin
    DECLARE @x int
    set @x = 100
    return 100
end
go

SELECT dbo.OutputFunction(1)

praveen (bvprav***@gmail.com) writes:
This point in time has not yet occurred. Check the date and time on
your computer, includin the time-zone setting.

No, you may not, because there isn't any.

Wrong Execution Plan
Stored procedure problem
DB Redesign - use junction/xref table?
How to prevent from deleting record?
Indexed Views & Constraints