marina B.


Hello,

I created a TVF that take as input a string and a delimiter (tipically a ',') and return a table.

Very briefly this my code:

<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillCharListRow", Name:="Split_CharList", TableDefinition:="Value nvarchar(255)")> _

Public Shared Function Split_CharList(ByVal strList As SqlString, ByVal delimiter As SqlString) As IEnumerable

Return strList.Value.Split(delimiter.Value.ToCharArray(0, 1))

End Function

Private Shared Sub FillCharListRow(ByVal obj As Object, <Out()> ByRef strList As String)

strList = CType(obj, String)

strList = strList.Trim

End Sub

This is what I see from the Managment studio, after I deploy the code:

ALTER FUNCTION [dbo].[Split_CharList](@strList [nvarchar](4000), @delimiter [nvarchar](4000))

RETURNS TABLE (

[Value] [nvarchar](255) NULL

) WITH EXECUTE AS CALLER

AS

It means that the string I give to the function has to be no longer than 4000.

There is the way to define a function that can accept a longer input

Thankx very much

Marina B.




Re: How to create a TVF with a string input parameter longer that 4000 character

Sergei Almazov


Use SqlChars instead of SqlString if you want to specify nvarchar(max) instead of nvarchar(4000)





Re: How to create a TVF with a string input parameter longer that 4000 character

nielsb

As Segei says, use in VS SqlChars instead of SqlString (or string). That will generate a create statement nvarchar(max). If you already have it deployed, take the alter script you posted and change the nvarchar(4000) to nvarchar(max) and run the script.

Niels





Re: How to create a TVF with a string input parameter longer that 4000 character

marina B.

Thank you very ..

it worked.

I have noticed that when the CLR dll is deployed from Visual Studio,VS creates automatically functions with nvarchar data types that have the max size allowed.

If I want to create a function with a parameter @Name is nvarchar(1000) instead of nvarchar(4000) I have to deploy the DLL by using the SQL Scripts e change the size within the CREATE FUNCTION...script.

If I have a string that for example is varchar which data type I have to use in the CLR code

Thank you

Marina B.