CODEWITHSUNDEEP
sqlclruser-defined-functions
ek_ny
ek_ny

Reputation: 10243

CLR Table Valued Function - Verify Steps to Create

I've created my first CLR table valued function. The steps I went through were:

  1. Create Library
  2. Run this command - EXEC dbo.sp_configure ‘clr enabled’,1 RECONFIGURE
  3. Copy the dll from step 1 to c: drive for convenience
  4. Create the assembly with dll- create assembly from 'c:\' WITH PERMISSION_SET = SAFE

  5. Create function -

    CREATE FUNCTION MyFunction(@input nvarchar(max)) RETURNS Table( -- columns ) AS

    EXTERNAL NAME [Assembly Name Here]. [Class Name Here] . [Static Function In Class Here]

I recall reading something where I had to also copy the dll into the binn directory below MSSQL.

My questions are:

Upvotes: 0

Views: 316

Answers (1)

a1ex07
a1ex07

Reputation: 37364

You don't need to copy dll; once the library is loaded, you don't need the external file.
Your steps look good to me, but you might want to add "Testing deployed function" to your steps.

Also, for SAFE permissions you can omit WITH PERMISSION_SET = SAFE.

Upvotes: 1

Related Questions