By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,932 Members | 1,468 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,932 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 6 '14 #1

✓ answered by Rabbit

So when you call your function, can't you just do this:
Expand|Select|Wrap|Line Numbers
  1. [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.
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION [dbo].[EncryptWrapper](@plainText [nvarchar](max)) 
  2. RETURNS [nvarchar](max)
  3. AS  
  4. RETURN [dbo].[EncryptString](@plainText, dbo.PW())
  5. GO 

Share this Question
Share on Google+
8 Replies


ck9663
Expert 2.5K+
P: 2,878
Have you tried creating the function in SQL Server?


~~ CK
May 6 '14 #2

Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 6 '14 #3

Rabbit
Expert Mod 10K+
P: 12,430
There shouldn't be a problem calling a function from within a function. Can you post the function code?
May 6 '14 #4

ck9663
Expert 2.5K+
P: 2,878
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
May 7 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,951
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
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION [dbo].[EncryptString](@plainText [nvarchar](max), @PW [nvarchar](max))
  2. RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
  3. AS 
  4. EXTERNAL NAME [VB_Encryption_Test].[VB_Encryption_Test.AESManagedProc].[Encrypt]
  5. GO
The code for the child function is
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION [dbo].[PW]()
  2. RETURNS nvarchar(max)
  3. AS
  4. BEGIN
  5.     DECLARE @PWD nvarchar(max)
  6.     DECLARE @ID smallint
  7.     SET @ID = 2   --Change for individual database
  8.     SELECT @PWD = PW
  9.     FROM tempdb.dbo.PasswordTable
  10.     WHERE PWID = @ID;
  11.     RETURN @PWD
  12. 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.
May 7 '14 #6

Rabbit
Expert Mod 10K+
P: 12,430
So when you call your function, can't you just do this:
Expand|Select|Wrap|Line Numbers
  1. [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.
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION [dbo].[EncryptWrapper](@plainText [nvarchar](max)) 
  2. RETURNS [nvarchar](max)
  3. AS  
  4. RETURN [dbo].[EncryptString](@plainText, dbo.PW())
  5. GO 
May 7 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 7 '14 #8

Seth Schrock
Expert 2.5K+
P: 2,951
That worked. Thanks Rabbit.
May 8 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.