Generalized UDF''s for column access?
-
We have tables that change frequently. With SOX etc... we have to tighten things down and add encryption. The idea of having a UDF that takes paramaters is more appealing than a view with instead of triggers. The reason is that the tables change frequently. The idea is to have flexible UDF's. For example, pass in the column and and the value for the select or update or inser ot what not. The reason is our apps really deal with colum level access. Lots of them. In the trillions annually. Get a specific field, update a specific field etc... Views with instead of triggers are not great for this type of access. Unless I am missing something..... Here is an example og what I am after with a select for simplicity. It runs in pubs.....
----------------------------------------------------------------------------------
use pubs
go
CREATE FUNCTION fn_SalesByStore (@Column varchar(30),@Value varchar(30))
RETURNS VARCHAR (100)
AS
begin
DECLARE @query AS VARCHAR (1000)
DECLARE @val as VARCHAR (100)
SELECT @query = 'select ' + @Column + ' from pubs.dbo.sales where ' + @Column + ' = ''' + @Value + ''''
exec @query = @val OUTPUT
return @val
end
Go
select dbo.fn_SalesByStore('stor_id','6380')
Go
drop FUNCTION fn_SalesByStore
go
----------------------------------------------------------------------------------
It seems to have trouble with the exec statement. I have tried in-line values UDF's but the lack of the begin and end is problematic.
The idea is that this would not have to be changed when the table changes. The applications know the changes. I understand that the logic could be on the client side but I hate the idea of putting the encryption on that end. Running sub queries and range etc... makes it hard to decrypt. Having all the logic in the DB makes the most sense and is easiest to manage. Assuming 2000 is flexible enough to allow this. We were able to do this sort of thing in 2005 though the CLS but ran into memory issues and leaks. I will wait for SP1 at minimum.
If anybody has an idea on how to do this, ( pass in column and value) pass it on to me. I understand selects are a no brainer, a plain old view can accomidate it, it is the other three operations that are the issue. I am sure is is simple and I am thinking about it incorrectly. Thankyou.
-
The syntax I posted for
exec @query = @val OUTPUT
It is not what I ran it is just what I typed here. Still I would not rule out a syntax error. I can't check till Monday. Thanks.
-
Dynamic SQL is illegal inside a function.
Functions cannot modify data, so INSERT, UPDATE, and DELETE are illegal (except on local TABLE variables). EXEC (@string) is also illegal because otherwise there would be no good way to check for illegal commands.
-
Err yep your right.
CREATE FUNCTION fn_SalesByStore (@ReturnColumn varchar(30), @ColumnWhere varchar(30), @ColumnValue varchar(30))
RETURNS VARCHAR (100)
AS
begin
DECLARE @query AS NVARCHAR (256)
DECLARE @val as NVARCHAR (100)
--SELECT @query = 'select ' + @Column + ' from pubs.dbo.sales where ' + @Column + ' = ''' + @Value + ''''
SELECT @query = 'select @val = ' + @ReturnColumn + ' from pubs.dbo.sales where ' + @ColumnWhere + ' = ''' + @ColumnValue + ''''
--exec @query = @val OUTPUT
exec sp_executesql @query, N'@val varchar(50) output', @val output
return @val
end
Go
select dbo.fn_SalesByStore('title_id','stor_id','6380')
Go
drop FUNCTION fn_SalesByStore
go
= "Only functions and extended stored procedures can be executed from within a function."
Thanks for the reminder.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply