April 23, 2007 at 11:17 am
Guys,
We are trying implement sequence objects in sql server.
http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx
from the above blog we are using option 2 for simulating sequences.
If you look at option 2 we have to create table and stored procedure for each sequence.
Is there a way I can create a function and pass the <tablename> and it executes
respective stored procedure??
CREATE FUNCTION GETNEXTVAL(@TABLENAME VARCHAR(20))
RETURN INT
BEGIN
Exec GetNewSeqVal_<tablename>
END
Thanks
April 24, 2007 at 7:10 am
Maybe you missed this important aspect of SQL functions?
"User-defined functions cannot be used to perform actions that modify the database state."
There may be workarounds (I'm not talking), but you need to seriously consider whether you want to abuse your database this way. There are a lot of architectural reasons why side effects of functions were ruled out, the server assumes there will be no side effects, and you risk the integrity of your data if you are obstinate enough to find a way to create them.
I agree that it would be nice if you could write this kind of function, but use your creativity to figure out the best way to do it with stored procs instead.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply