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))
DECLARE @return char(3)
DECLARE @sqlstring nvarchar(500)
SET @sqlstring = 'Select count(*) From @table Where id = @value'
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)
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 11:10.
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.