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)
ENDHow would you use this function?

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

?

Why not just do

EXEC @.rc = Test 'a','b'|||I'm not sure I follow your reply. I want to call the UDF from a table constraint.|||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..|||I can't find the msdn outline... here's a limitation rundown from informit (http://www.informit.com/isapi/product_id~{0D83BA18-CDB2-4D74-9C2A-AA44581B27B9}/element_id~{43287535-9508-43D6-BB36-4852A7A9F91B}/st~{340C91CD-6221-4982-8F32-4A0A9A8CF080}/session_id~{65960459-451E-4EF6-9D3B-7E7CF0E4CB0B}/content/articlex.asp) that I found to be pretty comprehensive.|||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?|||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!|||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|||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.