ansaurus

Question

Answer 1

A:

Your header syntax for inline table valued functions

...
RETURNS TABLE 
AS
RETURN 
(
    SELECT .. FROM something WHERE ...
)
GO --end of function

You have a multi-valued table function.

...
RETURNS @result TABLE
(
CompanyName VARCHAR(300),
[rl_Index] INT,
[rl_Addr1] VARCHAR(MAX),
[rl_Addr2] VARCHAR(MAX),
[rl_DisplayName] VARCHAR(MAX)
);
AS 
BEGIN
...
Do this

Do that

Data into @result 

END --of function
GO

See MSDN for examples

gbn 2010-09-28 17:24:49

It now says Select statements included within a function cannot return data to a client. IS this not correct having this at the end SELECT * FROM @tmp1 UNION SELECT * FROM @tmp2 ORDER BY [rl_DisplayName] RETURN END

2010-09-28 17:57:03

of course. My answer says "Data into @result" so does the MSDN examples...

gbn 2010-09-28 18:09:07

Answer 2

+2 A:

You're trying to use the syntax for a inline table-valued function when you actually have a multi-statement table-valued function. See the full description here.

You're using this:

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

BUT, you really want this:

--Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

Joe Stefanelli 2010-09-28 17:27:01

It now says Select statements included within a function cannot return data to a client.IS this not correct having this at the end SELECT * FROM @tmp1UNIONSELECT * FROM @tmp2ORDER BY [rl_DisplayName]RETURNEND

2010-09-28 17:43:32

You want to `INSERT INTO @return_variable SELECT * FROM @tmp1 UNION SELECT * FROM @tmp2 ORDER BY [rl_DisplayName] RETURN END`. It is the `@return_variable` that is used to pass your result set back to the caller.

Joe Stefanelli 2010-09-28 18:11:07

when I alter now it says Cannot perform alter on 'dbo.udf_ReportingLevelStructure2' because it is an incompatible object type.

2010-09-28 18:13:53

Please see OP for new code

2010-09-28 18:16:56

No idea why you'd get that message. Try to DROP and CREATE from scratch instead.

Joe Stefanelli 2010-09-28 18:17:23

@Joe Stefanelli: the type field in sys.object changes from IF to TF = new object...

gbn 2010-09-28 20:19:01

@gbn: Thanks for explaining that.

Joe Stefanelli 2010-09-28 20:34:48

ansaurus

tags:

views:

answers:

SQL what is wrong with the syntax here?

related questions