CREATE FUNCTION SQL Server 2000

Question

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.

Mikael Eriksson · Accepted Answer · 2012-04-20 11:26:39Z

3

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
)

edited Apr 20, 2012 at 11:26

answered Apr 20, 2012 at 11:00

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Willy Lazuardi Over a year ago

Thanks a lot @Mikael Eriksson. It's solved. But, should I declare the returned table (@returntable) first to get the result as what I want? Whereas I just want to select 2 variable (as 2 column) instead. Is there another pattern? Thanks in advance

Mikael Eriksson Over a year ago

I have updated the answer with an Inline version. That is the only alternative. If you need multiple statements in your function you have to declare the return table.

Willy Lazuardi Over a year ago

Thanks for your help Mike. I've just realized it :)

Collectives™ on Stack Overflow

CREATE FUNCTION SQL Server 2000

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related