Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    27

    Wink Unanswered: Local Variables in User Defined Functions

    I'm having a problem declaring variables in UDFs. Are they allowed? Can someone send me some syntax to see what I am doing wrong?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.

  3. #3
    Join Date
    Nov 2002
    Posts
    27
    I am using a table function with the following syntax:

    create function dbo.TestFunction (@InputVariable int)
    returns table
    with encryption
    as

    declare @LocalVariable smalldatetime

    select @LocalVariable = ABCDate from ABCTable where rowid = @InputVariable

    return(
    select * from XYZTable where XYZDate < @LocalVariable
    )

    Any thoughts?

    Originally posted by rdjabarov
    You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.

  5. #5
    Join Date
    Nov 2002
    Posts
    27
    gtocha...thanks

    Originally posted by rdjabarov
    It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •