Cfrgtkky

Unsure if this question makes sense but I'm new to CLR/UDT and just finished following through this example here:
https://docs.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.

I think you have misunderstood the concept of User-Defined Types. These are not the same thing as User-Defined Data Types (mapping a random name to an actual T-SQL datatype) or User-Defined Table Types (predefined table schemas used for creating table variables, typically used as Table-Valued Parameters).

Try this for a better intro: CLR User-Defined Types

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.

If the Stored Procedure was going to be a T-SQL stored proc, then the best way to do this would be to use a User-Defined Table Type / TVP (i.e. CREATE TYPE PersonType AS TABLE...). But since SQLCLR does not accept TVP's, you can do either:

• construct the list of complex "objects" as an XML document, which is easily parsed in .NET. Use SqlXml as the .NET input parameter type.


• create a Local Temporary Table (i.e. starting with a single #), populate it, and then read from it in the SQLCLR stored procedure using Context Connection = true as the connection string. I'm not sure under what conditions this option would be better / easier than simply passing in an XML document, but it is still an option.

Other notes:

1. You don't need the param: part of the SqlFacet attribute.


2. Use SqlString as the incoming datatype instead of string. Get the .NET string via ParamName.Value property. Check if NULL via ParamName.IsNull property.


3. Parameters cannot be expressed as combinations of parameters. Meaning, @person (@firstname nchar(300), @lastname nchar(300)) is not valid syntax under any condition.

For more info on working with SQLCLR in general, please visit: SQLCLR Info