How to call a UDF from another UDF that is connected to an assembly

[Seth Schrock]

I'm probably making this harder than it has to be, but here is what I'm trying to do. I have created some functions in vb.net and then imported those functions as an assembly. I then created a UDF to be able to call those functions in the assembly. These functions require two arguments. The first I can pass to it as I call it. The second one is the value returned by a second UDF. What I'm currently doing is inside my query, I call the first function, give it the first argument (a field value) and then call the second UDF for the second argument. I would like to build the second UDF into the first so that when I call the first UDF, I only have to pass it the one argument and it would pull the second value on its own. This would just make it so that I have less typing when I need to call the function.

[ck9663]

Have you tried creating the function in SQL Server?


~~ CK

[Seth Schrock]

The .net function is not something that can be done in SQL Server as far as I know, if that is what you are talking about. The other two functions are in SQL Server.

[Rabbit]

There shouldn't be a problem calling a function from within a function. Can you post the function code?

[ck9663]

If not the code of the assembly, try giving us what your function is trying to do. We might be able to reconstruct it in sql server.


~~ CK

[Seth Schrock]

So if I have a nested function, then I will have what I call the parent function (the one called in the queries) and the child function (the one that is called by the parent function). The code for the parent function is

CREATE FUNCTION [dbo].[EncryptString](@plainText [nvarchar](max), @PW [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [VB_Encryption_Test].[VB_Encryption_Test.AESManagedProc].[Encrypt]
GO

The code for the child function is

CREATE FUNCTION [dbo].[PW]()
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @PWD nvarchar(max)
	DECLARE @ID smallint
	SET @ID = 2   --Change for individual database
	SELECT @PWD = PW
	FROM tempdb.dbo.PasswordTable
	WHERE PWID = @ID;
	RETURN @PWD
END;

So instead of the EncryptString function requiring the PW argument, I just want the function to automatically call the PW function.

@CK The code that the assembly links to encrypts and decrypts the data. I know that SQL Server has the ability to encrypt and decrypt data, but my problem is that I am linking to the data from MS Access and I can't open a key from Access nor can a function change the state of the database. So to make the data easily modified through Access, I import a view instead of the actual table. The view decrypts the data automatically. Then I have Insert and Update triggers to encrypt the data that I change and then pass the encrypted data on to the table. If you have another solution I would love to hear it, but I have searched for months trying to figure out a way to encrypt/decrypt data in SQL Server in a manner that allows for easy integration with Access.

[Rabbit]

So when you call your function, can't you just do this:

[dbo].[EncryptString]('test string', dbo.PW())

Or if you don't want to pass the dbo.PW(), create a wrapper that will call it for you.

CREATE FUNCTION [dbo].[EncryptWrapper](@plainText [nvarchar](max)) 
RETURNS [nvarchar](max)
AS  
RETURN [dbo].[EncryptString](@plainText, dbo.PW())
GO

[Seth Schrock]

The top line is what I currently do. But since I have to type it for each field that I want encrypted, I was looking to reduce the amount of typing I would have to do. The wrapper looks like just thing that I'm looking for. I'll give that a try and let you know if I have any issues.

[Seth Schrock]

That worked. Thanks Rabbit.