CLR and Scalar-valued function

[ninjadeathmonkey]

I'm creating a function that calls a class I wrote in C# to encrypt and decrypt text.

I can get this code to create the function just fine:

CREATE FUNCTION DECRYPT_RSA
(
	@Expression NVARCHAR(max),
	@PrivateKey NVARCHAR(max)
)
RETURNS NVARCHAR(max)
AS
EXTERNAL NAME MANAGEDFUNCTIONS.[MyAssembly.SqlMethods.Cryptography].RSADecrypt
GO

The problem is when I wrap an IF/ELSE around the "EXTERNAL NAME..." it won't let me create it. What I would like to do is something like this:

CREATE FUNCTION DECRYPT_RSA
(
	@Expression NVARCHAR(max),
	@PrivateKey NVARCHAR(max)
)
RETURNS NVARCHAR(max)
AS
IF @Expression IS NOT NULL
BEGIN
	EXTERNAL NAME MANAGEDFUNCTIONS.[MyAssembly.SqlMethods.Cryptography].RSADecrypt
END
GO

Any suggestions?

Ron Wheeler
Tekdev Open Source Development

 

[ninjadeathmonkey]

By the way, this is the error message I get:

Msg 156, Level 15, State 1, Procedure DECRYPT_RSA, Line 8
Incorrect syntax near the keyword 'IF'.

Msg 156, Level 15, State 1, Procedure DECRYPT_RSA, Line 10
Incorrect syntax near the keyword 'EXTERNAL'.

Ron Wheeler
Tekdev Open Source Development

 

[ninjadeathmonkey]

Just in case anyone is interested, I figured it out (kinda). In order to check if the parameter is null (and return null), you have to add this:

CREATE FUNCTION DECRYPT_RSA
(
	@Expression NVARCHAR(max),
	@PrivateKey NVARCHAR(max)
)
RETURNS NVARCHAR(max)
[b][i]WITH RETURNS NULL ON NULL INPUT[/i][/b]  --Returns NULL if input is NULL
AS
EXTERNAL NAME MANAGEDFUNCTIONS.[MyAssembly.SqlMethods.Cryptography].RSADecrypt
GO

Ron Wheeler
Tekdev Open Source Development