Как я могу вызвать sqlserver функцию из VB.net (или C#)? Есть ли некоторый синтаксис как хранимая процедура?

Вы бы просто назвали его так же, как обычную строку кода sql

Одна из особенностей функций - они могут возвращать разные типы данных. Я использую:

Friend Function execFunctionReturnsString(ByVal funcName As String, Optional ByVal params As Collection = Nothing) As String
              Dim cmd As SqlCommand
              Dim param As SqlParameter
              Dim sRet As String
              Dim sCmdText As String
              Dim iParam As Integer

    cmd = New SqlCommand
    sCmdText = "select dbo." & funcName & "("
    cmd.CommandType = CommandType.Text
    cmd.Connection = _sqlConn
    cmd.CommandTimeout = 0

    If Not params Is Nothing Then
        For iParam = 1 To params.Count
            param = params(iParam)
            sCmdText = sCmdText & param.Value
            If iParam < params.Count Then
                sCmdText = sCmdText & ","
            End If
        Next
    End If

    sCmdText = sCmdText & ")"

    cmd.CommandText = sCmdText
    'If _sqlConn.State <> ConnectionState.Open Then
    _sqlConn.Open()
    'End If
    sRet = cmd.ExecuteScalar() & ""     ' if null
    _sqlConn.Close()

    Return sRet
End Function

Friend Function execFunctionReturnsInt(ByVal funcName As String, Optional ByVal params As Collection = Nothing) As Integer
    Dim cmd As SqlCommand
    Dim param As SqlParameter
    Dim iRet As Integer
    Dim sCmdText As String
    Dim iParam As Integer

    cmd = New SqlCommand
    sCmdText = "select dbo." & funcName & "("
    cmd.CommandType = CommandType.Text
    cmd.Connection = _sqlConn
    cmd.CommandTimeout = 0

    If Not params Is Nothing Then
        For iParam = 1 To params.Count
            param = params(iParam)
            sCmdText = sCmdText & param.Value
            If iParam < params.Count Then
                sCmdText = sCmdText & ","
            End If
        Next
    End If

    sCmdText = sCmdText & ")"

    cmd.CommandText = sCmdText
    'If _sqlConn.State <> ConnectionState.Open Then
    _sqlConn.Open()
    'End If
    iRet = cmd.ExecuteScalar()
    _sqlConn.Close()

Return iRet
End Function

вот пример вызова:

params = New Collection
params.Add(SQLClientAccess.instance.sqlParam("@setID", DbType.String, 0, 
       _editListSetID))
valGrid.hidePKFields = SQLClientAccess.instance.execFunctionReturnsInt
      ("udf_hiddenCount", params)

и вот мой код sqlParam:

        Friend Function sqlParam(ByVal paramName As String, ByVal dBType As System.Data.DbType, ByVal iSize As Integer, ByVal sVal As String) As SqlParameter
              Dim param As SqlParameter

    param = New SqlParameter
    param.ParameterName = paramName
    param.DbType = dBType
    param.Size = iSize
    param.Value = sVal

    Return param
    End Function

HTH

Да, вы можете вызвать функцию напрямую, как показано ниже.

Dim dtaName As New SqlClient.SqlDataAdapter

dtaName.SelectCommand = New SqlClient.SqlCommand
With dtaName.SelectCommand
    .CommandTimeout = 60
    .Connection = prvcmpINC.cntINC
    .CommandType = CommandType.StoredProcedure
    .CommandText = "dbo.app_GetName"
    .Parameters.AddWithValue("@ParamToPassIn", parstrParamToPassIn)
    .Parameters.Add("@intResult", SqlDbType.Int)
    .Parameters("@intResult").Direction = ParameterDirection.ReturnValue
End With

dtaName.SelectCommand.ExecuteScalar()
intRuleNo = dtaName.SelectCommand.Parameters("@intResult").Value