Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Dynamic SQL in UDF

    Does anyone know how to execute dynamic SQL in a user defined function? Here's a quick example of what I would like to do. I get an error that only functions and extended stored procedures may be called in a function. Is there any other way to execute dynamic SQL in a UDF?

    CREATE function dbo.test(@table char(40), @value char(40))
    RETURNS int
    AS
    BEGIN
    DECLARE @return char(3)
    DECLARE @sqlstring nvarchar(500)

    SET @sqlstring = 'Select count(*) From @table Where id = @value'

    Execute sp_executesql @sqlstring

    RETURN(@return)
    END

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How would you use this function?

    SELECT dbo.Test('a','b')

    ?

    Why not just do

    EXEC @rc = Test 'a','b'
    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
    Sep 2003
    Posts
    364
    I'm not sure I follow your reply. I want to call the UDF from a table constraint.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by peterlemonjello
    I'm not sure I follow your reply. I want to call the UDF from a table constraint.
    UDF's do not support dynamic sql. You would have to go with a stored procedure in order to gain that flexibility. I think they have that somewhere on msdn too..

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I can't find the msdn outline... here's a limitation rundown from informit that I found to be pretty comprehensive.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by peterlemonjello
    I'm not sure I follow your reply. I want to call the UDF from a table constraint.
    A CONSTRAINT? What would that do?

    what are you trying to restrict?
    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.

  7. #7
    Join Date
    Sep 2003
    Posts
    364
    I'm trying to restrict date ranges from overlapping in several tables. I was hoping to use dynamic sql in a udf so that the udf can be reused by multiple tables.

    Here's an example: A salesman can be licensed in a particular state to sell widgets. His licensed can be terminated and re-instated in a state. However, he can't hold two licenses in the same state at the same time. Our system must track each instance of a license the salesman has had in every state. Our developers didn't think validating overlapping start and end dates was important so no validation exists in the java code. Hence we have bad data with overlapping date ranges for a salesman in a state.
    **Table Structure (not syntactically correct):
    id int(pk identity)
    salesman_id int
    state_id int
    start_date datetime
    end_date datetime

    I would like the table constraint to call the udf which would return if there were any date ranges overlapping the inserted or updated data. If so prevent the insert or update. Oh yeah, this would prevent me from having to code a trigger on each table this logic will be used.

    Hope this helps!
    Last edited by peterlemonjello; 12-31-03 at 12:10.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not just create a unique index on saleman id, State?

    use an update trigger to move to current row to a history table where it's not unique?

    The create a view if the need to see all of the data.

    If someone tries to add another salesman that's already in the same state, they'll get an exception...

    What they probably should be doing is an update not an insert anyway

    MOO
    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.

  9. #9
    Join Date
    Sep 2003
    Posts
    364
    Yeah, that would work but I would have to do that for every table where this occurs. I was looking for an 'easier' solution that may end up being just as complex. I would prefer not to have to maintain seperate history tables nor triggers on each table but thanks for the ideas.

Posting Permissions

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