Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: UDF within a query and a query as a parameter

    Hi,

    I have a situation where I need to use a UDF in a query like in the example below:

    select a.col1, b.col1, UDF (Select a.col1 where a.col4 = b.col5)
    FROM table1 a , table2 b
    where a.col2 = b.col2
    and a.col5 = b.col5...

    Here UDF is a part of the query and its input is drawn from a query which again deals with the same tables as the main query.

    Hope it is clear. Please suggest a way, if there is one, to acheive this functionality.

    Thanks in advance
    Raj

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    I believe I used to do this all the time. Unless I am misreading your question, you require a scalar user defined function (one that returns a single value as opposed to an array of values). You then treat this more or less like any system function, passing in parameters. So:

    Code:
    select 
      a.col1, 
      b.col1, 
      MyDB.dbo.myUDF (b.col5)
    FROM 
      table1 a , 
      table2 b
    where 
      a.col2 = b.col2
      and a.col5 = b.col5...
    And your UDF would look something like...

    Code:
    CREATE FUNCTION myUDF (@InPar varchar(50)) 
    RETURNS varchar(50)
    
    AS
    
    BEGIN
    
      SELECT a.col1 WHERE a.col4 = @InPar
      RETURN
    
    END
    Mind you, it's been a while since wrote a UDF, so you may need to check my syntax. The real question I have is whether this is the right approach to your problem; might a subquery be more what you are looking for?

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    I believe I used to do this all the time. Unless I am misreading your question, you require a scalar user defined function (one that returns a single value as opposed to an array of values). You then treat this more or less like any system function, passing in parameters. So:

    Code:
    select 
      a.col1, 
      b.col1, 
      MyDB.dbo.myUDF (b.col5)
    FROM 
      table1 a , 
      table2 b
    where 
      a.col2 = b.col2
      and a.col5 = b.col5...
    And your UDF would look something like...

    Code:
    CREATE FUNCTION myUDF (@InPar varchar(50)) 
    RETURNS varchar(50)
    
    AS
    
    BEGIN
    
      SELECT a.col1 WHERE a.col4 = @InPar
      RETURN
    
    END
    Mind you, it's been a while since wrote a UDF, so you may need to check my syntax. The real question I have is whether this is the right approach to your problem; might a subquery be more what you are looking for?

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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