User Defined function question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

User Defined function question

Author

Topic

paully_21
Starting Member

1 Post

Posted - 2003-11-29 : 11:44:15

I'd like to set up a UDF that accepts variable field and table names is it possible? Ideally, you could call the function before doinng any update statements and if the newfield value was different from the old value a log of the update could be generated in another statement that has yet to be built.

Thanks!

So far I've got

CREATE FUNCTION udf_Record_History
(
@InputTableName varchar(100),
@InputFieldName varchar(100),
@InputFieldValue varchar(255),
@IdentityFieldName varchar(20),
@IdentityFieldValue int,

)
RETURNS bit
AS
BEGIN
DECLARE @Return int

Set @Return = (Select Count(*) From @InputTableName WHERE @IdentityFieldName = @IdentityFieldValue AND @InputFieldName = '@InputFieldValue')

Return @Return
END

mr_mist
Grunnio

1870 Posts

Posted - 2003-12-01 : 07:43:37

To use variables as your table names you would have to use dynamic SQL. I don't think that you are allowed to use dynamic SQL within UDFs, though ICBW. I suggest you look up what is and is not allowed in the BOL.

-------
Moo. :)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-01 : 07:45:59

Another option if you are looking to perform table auditing is to check out Nigels Audit trigger:
http://www.nigelrivett.net/AuditTrailTrigger.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources