Unsure if this question makes sense but I'm new to CLR/UDT and just finished following through this example here: https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/getting-started-with-clr-integration?view=sql-server-2017
What I want to achieve right now is pretty similar. I've tried adding in a string parameter without fail, but when adding a C# object is where the issue begins.
This is my C# Main:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloName(Person person, [param: SqlFacet(MaxSize=-1)]out string result)
{
SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
result = "Hello, " + person.firstName + " " + person.lastName;
}
And this is the C# Person class:
public class Person
{
public string firstName;
public string lastName;
public Person(string firstName, string lastName)
{
this.firstName = firstName;
this.lastName = lastName;
}
}
I'm able to successfully create the assembly, but it's the procedure where I get stuck. I want to do something like:
CREATE PROCEDURE helloname
(
@person (@firstname nchar(300), @lastname nchar(300))
@result nchar(300) OUTPUT
)
AS EXTERNAL NAME helloworld.HelloWorldProc.HelloName
But after some research apparently the best way to go about objects is through UDTs. I have followed another example and created both a Person table and a PersonType as below in T-SQL:
CREATE TABLE Person
(
FirstName nvarchar(50),
LastName nvarchar(50)
)
Go
CREATE TYPE PersonType AS TABLE
(
FirstName nvarchar(50),
LastName nvarchar(50)
)
Go
The error occurs here:
CREATE PROCEDURE helloname
(
@personType PersonType,
@result nchar(300) OUTPUT
)
AS EXTERNAL NAME helloworld.HelloWorldProc.HelloName
On attempted execution it says "CREATE PROCEDURE for "helloname" failed because T-SQL and CLR types for parameter "@personType" do not match."
How do I get "PersonType" to equal the C# Class "Person" for this to work properly? Let me know if I'm going about this the completely wrong way/if there's a simpler solution. Ideally I will be passing in a List with multiple variable types inside Person. Thanks in advance.
