Writing a SQLServer User Defined Function in C#

Question

I'm struggling to find much information on this subject so I'm wondering if any of you can enlighten me.

I basically have a mini application written in C# which accepts a string, performs a few manipulations to it and then returns the result. I now wish to create this function in SQLServer so that I can easily run it on records in my tables.

I know how to create and run SQLServer UDFs written in C# (ie. register the .dll in SQLServer before creating a function for it) but I don't seem to be able to get any output from my functions. For example if I have the following C# function...

using System;
using Microsoft.SqlServer.Server;

public static class MyClrUdfClass
{
    [SqlFunction]
    public static string HelloWorld(string myString)
    {
        return (myString);
    }
}

And I run it in SQLServer like this...

USE [AdventureWorksDW2008R2]
GO
EXEC [dbo].HelloWorld'Hello World, this is my string!'
GO

The only output I get in SQLServer when I run the function is "Command completed successfully". So where is myString? I'm expecting that some SQL is needed to actually print the result but I'm not sure.

This probably has a very simple solution but as I mentioned earlier I'm really struggling to find any helpful information out there.

just guessing but did you try to do call : SELECT [dbo].HelloWorld 'Hello World, this is my string! — Sebastian Piu
– Sebastian Piu, Commented Dec 15, 2011 at 10:57
yeah I did try but I get the - The multi-part identifier "dbo.HelloWorld" could not be bound - error. — Rich
– Rich, Commented Dec 15, 2011 at 11:38

Sebastian Piu · Accepted Answer · 2011-12-15 11:48:27Z

4

You are missing the parenthesis:

SELECT [dbo].HelloWorld ('Hello World, this is my string!')

or try this way:

declare @a varchar(max)
exec @a = [dbo].HelloWorld 'Hello World, this is my string!' 
print @a

answered Dec 15, 2011 at 11:48

Sebastian Piu

8,0581 gold badge35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rich Over a year ago

Yes that's it. I was missing the parenthesis. Very stupid of me. Thanks guys.

user359040 · Accepted Answer · 2011-12-15 11:00:35Z

0

Have you tried:

USE [AdventureWorksDW2008R2]
GO
select [dbo].HelloWorld('Hello World, this is my string!')
GO

?

answered Dec 15, 2011 at 11:00

user359040

1 Comment

Rich Over a year ago

Yes but I get the error The multi-part identifier "dbo.HelloWorld" could not be bound.

RonSanderson · Accepted Answer · 2015-11-10 16:52:58Z

"You are missing the parenthesis"

I think this is misleading. The example shows both parenthesized and not-parenthesized ways to call the function.

The real point is that the output was never assigned to anything and was therefore discarded, as shown in this example.

EXEC [dbo].HelloWorld 'Hello World, this is my string!'

By adding the word SELECT in front of the function call, or assigning it to a variable ( as in "exec @a =" ), the returned value from the function call will be assigned to a local variable and becomes available for use by the calling code. (In the case of the SELECT variation, the local variable is created behind the scenes and then used internally by the query editor so the result can be displayed.)

The function call was really working all along, but the code snippet had not done anything to capture the output from it.

(Sorry that I could not post this as a comment under Sebastian's answer, but I don't have enough reputation points to do that.)

Collectives™ on Stack Overflow

Writing a SQLServer User Defined Function in C#

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related