Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20

    Question Unanswered: passing parameters to a function

    I have a function which takes 2 parameters and returns a value.
    How do i pass parameters to this function in a query like :

    select * from table1 t, dbo.fMyFunction(t.value, 0)

    The above query gives a syntax error.

    Any help appreciated !

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    What is the synatx error you're getting ?

    Also what is the function ? A script for it and the table would be useful

  3. #3
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20
    The error i get is in German
    "Zeile 1: Falsche Syntax in der Nähe von '.'."
    and means
    "Line 1: wrong syntax near '.'."

    the function is just a simple one like:

    CREATE FUNCTION fMyFunction
    (
    @pzn int,
    @d datetime
    )
    RETURNS @T TABLE(r1 int, r2 int)
    as
    begin
    -- ....
    insert into @T values(1, 2)
    end

    and the Table t hast some columns, one i use as a paramter to
    look in the function:

    select * from Table t, dbo.fMyFunction(t.pzn, '1.1.2003')

    if i pass a constant everything is ok, but i pass t.pzn it brings the
    above error message.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Remember that your function is returning a table, which you are cross-joining to the other table. Yet you are trying to pass a value from your other table to the function. So lets say that your other table has 1000 rows, and your function returns 1000 rows. You then have 1000 parameters being passed to a function that would return 1000 x 1000 records, cross-joined to the original table of 1000 records. The end result of your logic (even if it were syntactically possible...) would be 1000 x 1000 x 1000 or 1,000,000,000 records.

    That CAN'T be what you want...

    Please explain what you are trying to accomplish.

    blindman

  5. #5
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117
    Did you try:

    select *, dbo.fMyFunction(t.pzn, '1.1.2003') from Table t


    Originally posted by AticAtac
    The error i get is in German
    "Zeile 1: Falsche Syntax in der Nähe von '.'."
    and means
    "Line 1: wrong syntax near '.'."

    the function is just a simple one like:

    CREATE FUNCTION fMyFunction
    (
    @pzn int,
    @d datetime
    )
    RETURNS @T TABLE(r1 int, r2 int)
    as
    begin
    -- ....
    insert into @T values(1, 2)
    end

    and the Table t hast some columns, one i use as a paramter to
    look in the function:

    select * from Table t, dbo.fMyFunction(t.pzn, '1.1.2003')

    if i pass a constant everything is ok, but i pass t.pzn it brings the
    above error message.
    Steve

Posting Permissions

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