Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: UDF and dynamic SQL

    Hi:

    Can anyone tell me if it is possible to use dynamic sql within a user defined function in MS SQL 2000?

    Thanks,
    Rui Ferreira

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No

    Code:
    USE Northwind
    GO
    
    CREATE FUNCTION myFunction99(@x int, @y int)
    Returns int
    AS
      BEGIN
    	DECLARE @sql varchar(8000)
    	SELECT @sql = 'SELECT ' + CONVERT(varchar(15),@x) + ' + ' + CONVERT(varchar(15),@y)
    	EXEC sp_execute @sql
    	RETURN 1
      END
    GO
    
    SELECT dbo.myFunction99(1,2)
    GO
    
    DROP FUNCTION myFunction99
    GO
    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
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Brett, that doesn't work.

    You can look into OPENQUERY, but not sure if it will work as expected.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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