Function in SQL 2000

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

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.

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,

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 ?

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]

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,

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?

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