Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2004
    Posts
    12

    Red face Unanswered: user defined functions

    hi all,

    I made myself a user defined function, it works great, but is there a way to make it available to all databases?

    cheers,

    alex

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    The same way as any other stored procedure or view.

  3. #3
    Join Date
    Jan 2004
    Posts
    12
    Maybe i haven't provided enough information. I can call this function by using:

    select *, testdb.dbo.myfunction(1,2,3) as aa from test

    But the problem is that i use a table in this function that refers to a table in the database from where it is called from.

    example;
    I execute the query
    select *, testdb.dbo.myfunction(1,2,3) as aa from test
    from database 'testdb2'. the function does a select statement, but it searches the table in de database 'testdb' instead of 'testdb2'.

    I cant hard-code it because it should be available to all databases.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by znndrp
    Maybe i haven't provided enough information. I can call this function by using:

    select *, testdb.dbo.myfunction(1,2,3) as aa from test

    But the problem is that i use a table in this function that refers to a table in the database from where it is called from.

    example;
    I execute the query
    select *, testdb.dbo.myfunction(1,2,3) as aa from test
    from database 'testdb2'. the function does a select statement, but it searches the table in de database 'testdb' instead of 'testdb2'.

    I cant hard-code it because it should be available to all databases.
    I guess it works as it was designed by MS. All you can do is just use a stored procedure with parameters and exec inside.

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    did you try putting it in the Master database ?

  6. #6
    Join Date
    Jan 2004
    Posts
    12
    Yes i did.

    The core problem is actually that im trying to open a cursor with a variable sql-string. Which works ok, unless i try to specify a database. example:

    set @c_temp = 'declare cur_lib cursor for select text from lib where charindex('','' + convert(varchar,value) + '','','',' + @c_values + ','') > 0 and field like ''' + @c_field + ''''
    execute @c_temp

    This code works in my Query analyzer, but not in a function. It returns:

    Could not locate entry in sysdatabases for database 'declare cur_lib cursor for select text from tempdb'. No entry found with that name. Make sure that the name is entered correctly.

    :/

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    maybe you can execute < use tempdb> before

  8. #8
    Join Date
    Jan 2004
    Posts
    12
    Nope, a 'use database' statement is not allowed in a procedure or trigger

  9. #9
    Join Date
    Jan 2004
    Posts
    10
    refer to this artical on MSDN that describes the steps to make your own system udf.

    http://msdn.microsoft.com/library/de...ml/sql01l1.asp


    but then you would not be able to use the fuinction in declarative constraints (check / default etc.)

    but u can use it in normal select statements.
    Amit

  10. #10
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by amitjethva
    refer to this artical on MSDN that describes the steps to make your own system udf.

    http://msdn.microsoft.com/library/de...ml/sql01l1.asp


    but then you would not be able to use the fuinction in declarative constraints (check / default etc.)

    but u can use it in normal select statements.
    Amit
    I read that page, but i dont see a solution there, am i missing something?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Very cool articel, amitjethva.

    But I can see how it doesn't apply to znndrp. He wants the function to reference tables within the current database, and I suspect that the system function will only reference tables within master, or hardcoded database references.

    One possible solution is to build the guts of the function as dynamic SQL (I think that's allowed in functions...) and then pass the database name as a parameter.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    but you'll have to put that function in each database

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to put parentheses around @c_temp:

    exec (@c_temp)

  14. #14
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by blindman
    One possible solution is to build the guts of the function as dynamic SQL (I think that's allowed in functions...) and then pass the database name as a parameter.
    How can i do that?

  15. #15
    Join Date
    Jan 2004
    Posts
    12
    Originally posted by rdjabarov
    You need to put parentheses around @c_temp:

    exec (@c_temp)
    Nope, if i do that it gives me an error 'Invalid use of EXECUTE within a function'

Posting Permissions

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