SQL Server Performance

Function in SQL 2000

Discussion in 'General DBA Questions' started by johnson_76, Oct 15, 2009.

  1. johnson_76 New Member

    Hi Experts :)
    I am failed to apply this function in SQL 2000:
    USE [db1]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION [dbo].[@LMK_2009]
    (
    -- Add the parameters for the function here
    @Val nvarchar(MAX),
    @PadChar nvarchar(1),
    @Length int
    )
    RETURNS nvarchar(MAX)
    AS
    BEGIN

    -- Return the result of the function
    RETURN replicate(@PadChar,@Length-len(@Val)) + @Val

    END

    the error message is must declare the varaable '@padchar'. Pls give advice. Thx in advance for your help.
    Regards
    Johns
  2. Adriaan New Member

    First of all, in SQL 2000 you can't use nvarchar(max) because 4000 is the maximum size - the (MAX) option was introduced only in SQL 2005.
    In SQL 2000 you will get the following error message before the one that you mention: "Incorrect syntax near 'MAX'."
    You'll also have to change the declaration of the return value. With NVARCHAR(4000) the function will be created successfully.
    Eventhough there is no error, many here would object to the object name containing a character that is neither alphabetical nor numeric. SQL Server accepts the @ at the start of the name, but you will run into problems if you ever forget to put [] around that UDF name when calling it in T-SQL.
  3. Madhivanan Moderator

    1 Why do you need a seperate function for this as you can directly use with respective columns in the SELECT statement
    2 It seems formatting issue. If you want to show the data in front end application, do formation there
  4. johnson_76 New Member

    Dear Madhivanan
    Thx for your reply. I use it because the report need the function. The report is using SP and function. So, there is a field named sortgcod in the SP that call the function:
    set sortgcod = [DBO].[@LMK_2009] (cast(ID as nvarchar(20)),'0',10)
    In the beginning of the SP, the field included in the creation of the table:
    Code:
    create table #COAStruct (ID int identity(1,1),AcctCode nvarchar(20),AcctName nvarchar(200),FormatCode nvarchar(20),
        levels int, SortingCode nvarchar(100),FatherNum nvarchar(20))
    
    could you give example of no. 2 your reply ?
    Pls give advice
    Rgd,

  5. johnson_76 New Member

    Dear Adriaan,
    Thx a lot for your answer. I have edited it and successfully correct after check using when check syntax button. Here are the function:
    USE [db1]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE proc [dbo].[@LMK_2009]
    (
    -- Add the parameters for the function here
    @Val nvarchar(4000),
    @PadChar nvarchar(1),
    @Length int
    )
    RETURNS nvarchar(4000)
    AS
    BEGIN

    -- Return the result of the function
    RETURN replicate(@PadChar,@Length-len(@Val)) + @Val

    END

    However, there is an error message when clicking OK button. There error message is :
    "stored procedure definition must include name and text (for standard stredprocedure) or libraryname (for extended storedprocedure) "
    Anyway, what do you mean in the last paragraph of your reply ?
    could you please elaborate more detail ?
  6. Adriaan New Member

    You suddenly use CREATE PROC instead of CREATE FUNCTION --- that's why you're getting funny errors.
    Please use only characters A-Z and 0-9 for object names, and perhaps the _ character.
    Object names should always start with a letter, never a number.
    The only exceptions to these rules are @ for table variables and # or ## for temporary tables.
    Why? If you ever forget to put delimiters around object names in a script, well - you will get funny error messages. You have been warned.[H]
  7. johnson_76 New Member

    Oops.. sorry for the mistaken function I paste. I have replace the create proc to create function but still not work. The object name is replaced. here is the new one:
    USE [DB1]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    CREATE FUNCTION [dbo].[@ATB_UDF_XMLF]
    (
    -- Add the parameters for the function here
    @Val nvarchar(4000),
    @PadChar nvarchar(1),
    @Length int
    )
    RETURNS nvarchar(4000)
    AS
    BEGIN

    -- Return the result of the function
    RETURN replicate(@PadChar,@Length-len(@Val)) + @Val

    END

    if the above function is correct, I will try to apply again.


    Thanks and Rgd,
  8. Adriaan New Member

    Apart from the @ character in the function name, which is not recommended but doesn't break the script either, the script should run without error.
    Unless of course there is already a function under that name, in which case you should use ALTER instead of CREATE.
    Why don't you test it?
  9. johnson_76 New Member

    Hi All,
    The problem is resolved. Thanks for the advice.
    Rgd,

Share This Page