Post reply

Msg 6567 - Error in CREATE PROCEDURE ... EXTERNAL NAME

  • March 28, 2008 at 5:21 am

    #204824

    Dear all,

    I want to use C# to write a simple UDF :

    (1)input a character, then

    (2)to have a simple check, then

    (3)might replace it with another character.

    Here is the source, it could compiled successfully :

    using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Data;

    using Microsoft.SqlServer.Server;

    using System.Data.SqlTypes;

    public class T1

    {

    public static string common_ReplaceChar(string inputString)

    {

    string outputString = "";

    if (string.IsNullOrEmpty(inputString))

    {

    return "";

    }

    outputString = inputString.Replace("&", "&");

    outputString = outputString.Replace("<", "<");

    outputString = outputString.Replace(">", ">");

    outputString = outputString.Replace("\"", """);

    return outputString;

    }

    }

    But I don't knwo why I got this error :

    CREATE PROCEDURE dbo.CLR_Common_ReplaceChar

    AS

    EXTERNAL NAME commonreplace.T1.common_ReplaceChar

    Error :

    Msg 6567, Level 16, State 2, Procedure CLR_Common_ReplaceChar, Line 1

    CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that

    return either SqlInt32, System.Int32, void.

    Is my C# program still OK ?

    If no, how should I correct it ?

  • March 28, 2008 at 5:27 am

    #795088

    You are trying to create a procedure (CREATE PROCEDURE dbo.CLR_Common_ReplaceChar)

    You cannot do this for methods that return other than ints. I assume you want to create a UDF, so you should use:

    CREATE FUNCTION dbo.CLR_Common_ReplaceChar (@str nvarchar(4000))

    RETURNS nvarchar(4000)

    AS

    EXTERNAL NAME commonreplace.T1.common_ReplaceChar

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • March 28, 2008 at 5:42 am

    #795094

    Thanks a lot.

  • March 28, 2008 at 6:29 am

    #795116

    Are you doing this to learn CLR? If not, you can create exactly the same proc in T-SQL using the REPLACE command

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 28, 2008 at 6:35 am

    #795120

    Yes, in fact, we are using T-SQL REPLACE UDF, however, I want to know if I use CLR, will it be faster ?

    I think as our existing UDF only do string manipulation, it might be faster if we use CLR to handle it.

    Am I correct ?

  • March 28, 2008 at 6:54 am

    #795132

    For something this simple, CLR will probably be comparable or slower, but do test for yourself. Where CLR shines is for really complex regex functions

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply