CODEWITHSUNDEEP
c#sqlsql-serverclruser-defined-functions
Rich
Rich

Reputation: 121

Writing a SQLServer User Defined Function in C#

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.

Upvotes: 2

Views: 3089

Answers (3)

RonSanderson
RonSanderson

Reputation: 51

"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.)

Upvotes: -1

Sebastian Piu
Sebastian Piu

Reputation: 8008

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

Upvotes: 4

user359040
user359040

Reputation:

Have you tried:

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

?

Upvotes: 0

Related Questions