Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: User defined functions

    Greetings

    I want to create a function that you can pass the table name as a parameter and use it in a select query. Example:

    -----------
    CREATE FUNCTION tn_getmax (@tablefield char, @tablename char)
    RETURNS int

    AS
    BEGIN
    RETURN (SELECT MAX(@tablefield) FROM @tablename)
    END
    -----------

    is this possible? the example above does not work.

    regards,
    -blackpearl

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya know...I thought it was a neat academic thing to give a shot at...

    but it's a bad idea...why would you need to do this...

    and if you want to continue...this was my (failed) attempt

    Code:
    USE Northwind
    GO
    
     CREATE TABLE myTable99(Col1 varchar(800))
    GO
     
     CREATE FUNCTION tn_getmax (@tablefield sysname, @tablename sysname)
     RETURNS varchar(8000)
     AS
     	BEGIN
     	DECLARE @x varchar(8000), @y varchar(8000)
     	DELETE FROM  myTable99
     	SELECT @x = 'INSERT INTO myTable99(Col1) SELECT MAX('+@tablefield+') FROM '+@tablename
     	SELECT @y = Col1 FROM myTable99
     	RETURN @y
     END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Posts
    39
    my failed attempt

    CREATE FUNCTION tn_getmax (@tablefield char, @tablename char)

    RETURNS int

    AS

    BEGIN


    RETURN (EXEC('SELECT MAX(' + @tablefield + ') FROM ' + @tablename)

    END

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    It is a limitation of udfs - only functions and extended stored procedures can be executed in a function.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I was trying using openquery ... but you can't have a variable parameter to openquery
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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