Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2005
    Posts
    45

    Unanswered: User-Defined Func Syntax for Table return

    Hi all!

    I'm trying to use a UDF that returns a table, but I'm not sure of the syntax to invoke it. I've found examples in BOL and on-line like the following:

    SELECT * FROM dbo.fn_MyTableFunc( 123.09, 'MyID' )

    But I need the input parameter to be obtained from another table. For a very simplistic example, I've got 4 tables (and yes, I know that I can get the results I want for this example without using a UDF, but humor me):

    Code:
    CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50))
    CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50))
    CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50))
    CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int)
    GO
    
    INSERT INTO tUser VALUES (111, 'Me')
    
    INSERT INTO tAcctGroup VALUES (1, 'NY')
    INSERT INTO tAcct VALUES (11, 1, 'New York City')
    INSERT INTO tAcct VALUES (12, 1, 'Syracuse')
    
    INSERT INTO tAcctGroup VALUES (2, 'GA')
    INSERT INTO tAcct VALUES (21, 2, 'Atlanta')
    INSERT INTO tAcct VALUES (22, 2, 'Savannah')
    INSERT INTO tAcct VALUES (23, 2, 'Augusta')
    
    INSERT INTO tAcctGroup VALUES (3, 'TX')
    INSERT INTO tAcct VALUES (31, 3, 'Dallas')
    INSERT INTO tAcct VALUES (32, 3, 'Houston')
    INSERT INTO tAcct VALUES (33, 3, 'El Paso')
    INSERT INTO tAcct VALUES (34, 3, 'San Antonio')
    
    INSERT INTO tAcctGroup VALUES (4, 'CA')
    INSERT INTO tAcct VALUES (41, 4, 'Los Angeles')
    INSERT INTO tAcct VALUES (42, 4, 'San Francisco')
    
    INSERT INTO tMapUserToGroup VALUES (111,2)
    INSERT INTO tMapUserToGroup VALUES (111,4)
    GO
    
    CREATE FUNCTION dbo.ufnGetAcctList(@AcctGroupID int) RETURNS @tAcct table (AcctID int, AcctName varchar(50))
    AS
    BEGIN
    INSERT INTO @tAcct
    SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID = @AcctGroupID
    RETURN
    END
    GO
    I know that I can do:
    SELECT * FROM TestDB.dbo.ufnGetAcctList(4)

    But I want the equivalent of:
    SELECT AcctID, AcctName FROM tAcct
    WHERE AcctGroupID IN (SELECT AcctGroupID FROM tMapUserToGroup WHERE UserID = 111)

    Which uses tMapUserToGroup to obtain the AcctGroupID to pass into the function. The results would be:
    AcctID AcctName
    -----------------------------
    21 Atlanta
    22 Savannah
    23 Augusta
    41 Los Angeles
    42 San Francisco

    Any thoughts?
    Thanks in advance for your help.
    Cat

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Hey, when you have the leather, nails and strings ,then why don't you make a shoe by yourself? I mean you already gave the solution,just you need to copy and paste...nothing else

    Code:
    CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50))
    CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50))
    CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50))
    CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int)
    GO
    
    INSERT INTO tUser VALUES (111, 'Me')
    
    INSERT INTO tAcctGroup VALUES (1, 'NY')
    INSERT INTO tAcct VALUES (11, 1, 'New York City')
    INSERT INTO tAcct VALUES (12, 1, 'Syracuse')
    
    INSERT INTO tAcctGroup VALUES (2, 'GA')
    INSERT INTO tAcct VALUES (21, 2, 'Atlanta')
    INSERT INTO tAcct VALUES (22, 2, 'Savannah')
    INSERT INTO tAcct VALUES (23, 2, 'Augusta')
    
    INSERT INTO tAcctGroup VALUES (3, 'TX')
    INSERT INTO tAcct VALUES (31, 3, 'Dallas')
    INSERT INTO tAcct VALUES (32, 3, 'Houston')
    INSERT INTO tAcct VALUES (33, 3, 'El Paso')
    INSERT INTO tAcct VALUES (34, 3, 'San Antonio')
    
    INSERT INTO tAcctGroup VALUES (4, 'CA')
    INSERT INTO tAcct VALUES (41, 4, 'Los Angeles')
    INSERT INTO tAcct VALUES (42, 4, 'San Francisco')
    
    INSERT INTO tMapUserToGroup VALUES (111,2)
    INSERT INTO tMapUserToGroup VALUES (111,4)
    GO
    --- *** modified code
    CREATE FUNCTION dbo.ufnGetAcctListModified(@UserID int)
     RETURNS @tAcct table (AcctID int, AcctName varchar(50))
    AS
    BEGIN
    INSERT INTO @tAcct
    SELECT AcctID, AcctName FROM tAcct 
    WHERE AcctGroupID IN (SELECT AcctGroupID
     FROM tMapUserToGroup WHERE UserID = @UserID)
    RETURN
    END
    GO
    ----****************
    SELECT * FROM TestDB.dbo.ufnGetAcctListModified(111)
    
    Go
    Last edited by rudra; 10-30-06 at 20:08.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Jun 2005
    Posts
    45
    Hi,

    Thanks for the response, but as I stated, this is only a very simplistic example of what I need it to do. The actual function is much more complex than what I have listed, but the invocation would be the same. That's what I'm looking for. How do I call a UDF where the parameters would be a set of values obtained from another table?

    Any help would be greatly appreciated.
    Thanks in advance,
    Cat

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could use a cross-join:
    Code:
    select	*
    from	YourTable,
    	dbo.YourFunction(YourTable.Column1, YourTable.Column2)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2005
    Posts
    45
    Hi Blindman,

    I just tried:

    Code:
    SELECT * FROM tMapUserToGroup,
    	TestDB.dbo.ufnGetAcctList(tMapUserToGroup.AcctGroupID)
    WHERE tMapUserToGroup.UserID = 111
    and it gave me a syntax error: Incorrect syntax near '.'.

    Just to give everyone a little background, I'm trying to get rid of a cursor. Essentially, I have a cursor that loops through my equivalent of the tMapUserToGroup table and for each AcctGroupID for a user it gets the list of values from tAcct and stores them in a temporary table. The task to determine the "list of values from tAcct" is more complicated than what I've given above. I was hoping to use a UDF to get rid of the cursor, but I'm starting to think that this won't help me either.

    If anyone has any other ideas, let me know.
    Thanks,
    Cat

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How about, instead of having your function take an AcctGroupID parameter, instead have it return all the records for every AcctGroupID?
    Then you could JOIN your table to your function:
    Code:
    SELECT	*
    FROM	tMapUserToGroup
    	inner join TestDB.dbo.ufnGetAcctList() AcctLists on tMapUserToGroup.AcctGroupID = AcctLists.AcctGroupID
    WHERE	tMapUserToGroup.UserID = 111
    Otherwise, you may need to do this with a sproc, but you almost certainly do not need a cursor.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not much consolation but I believe you can do this in 2005.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Yes. The operator is CROSS APPLY in SQL 2005.

Posting Permissions

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