CODEWITHSUNDEEP
functionsql-server-2000
Willy Lazuardi
Willy Lazuardi

Reputation: 1816

CREATE FUNCTION SQL Server 2000

I'm creating a SQL Server function in SQL Server 2000. My syntax is about like this:

ALTER FUNCTION dbo.test()
RETURNS TABLE
AS
RETURN 
(
  DECLARE @A VARCHAR(100)
  DECLARE @B VARCHAR(100)
  SELECT @A='abc', @B='bca'
  SELECT A=@A, B=@B  
)

I'm trying to declare a variable and select it as a return value, but I'm getting this error message:

Msg 156, Level 15, State 1, Procedure test, Line 6
Incorrect syntax near the keyword 'DECLARE'.
Msg 170, Level 15, State 1, Procedure test, Line 10
Line 10: Incorrect syntax near ')'.

Seems it wont accept a variable declaration. What's the problem? Thanks in advance.

Upvotes: 1

Views: 2574

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

Your syntax is for an Inline Table Function. with multiple statements as you have you need a Multi-Statment Table Function.

CREATE FUNCTION dbo.test()
RETURNS @returntable TABLE 
(
    A VARCHAR(100),
    B VARCHAR(100)
)
AS
BEGIN
    DECLARE @A VARCHAR(100)
    DECLARE @B VARCHAR(100)
    SELECT @A='abc', @B='bca'
    INSERT INTO @returntable 
    VALUES(@A, @B)
    RETURN 
END

CREATE FUNCTION

Update:
If you want an Inline Table Function is has to be only one select statement.

CREATE FUNCTION dbo.test2()
RETURNS TABLE AS RETURN
(
    SELECT 'abc' AS A, 'bca' AS B
)

Upvotes: 3

Related Questions