Functions returing a table cannot declare variables? 1

[Luzian]

In Microsoft SQL Server Management Studio Express:

Msg 102, Level 15, State 1, Procedure fn_GetMessages, Line 2
Incorrect syntax near 'RETURNS'.
Msg 178, Level 15, State 1, Procedure fn_GetMessages, Line 6
A RETURN statement with a return value cannot be used in this context.

In visual studio 2005:

Incorrect syntax near the keyword 'DECLARE'.

A RETURN statement with a return value cannot be used in this context.

In either case, it seems the "DECLARE" statement causes the error. Removing that line of code proves it. It seems I can't do anything within a function returning a table, but return the table.

Here's the minimal query:

CREATE FUNCTION dbo.fn_GetRoles
(@parameter int)
RETURNS TABLE
AS
[red]DECLARE @some_variable int;[/red]

RETURN
(
    SELECT *
    FROM [Roles]
)

 

[gmmastros]

When creating a function that returns a table, you need to indicate what the structure of that table is. You also need to use Begin/End to define the body of the function.

CREATE FUNCTION dbo.fn_GetRoles
(@parameter int)
RETURNS [!]@output[/!] TABLE[!](Field1 Int, Field2 VarChar(20))[/!]
AS
Begin
  DECLARE @some_variable int;

  Insert Into @Output(Field1, Field2)
  Select Field1, Field2
  From   [Roles]

  RETURN
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Luzian]

The syntax below in red doesn't work. Have you tried it?

CREATE FUNCTION dbo.fn_GetRoles
(@parameter int)
RETURNS @output TABLE(Field1 Int, Field2 VarChar(20))
AS
BEGIN
	DECLARE @some_variable int;

	[red]INSERT INTO @Output(Field1, Field2)
	SELECT Field1, Field2
	FROM [Roles][/red]

	RETURN
END

 

[gmmastros]

Does your [Roles] table have Field1 and Field1 columns? I used generic names because you didn't indicate what the field names are. You will need to replace Field1 and Field2 to your actual column names in the [roles] table. Also, in the RETURNS line, you'll have to specify the data types of the columns you want returned.

Trust me. I tried this. I tested this. It works. Once you specify the actual field names (with their corresponding data types, you should be able to use the function). If you want further assistance, run the following query and paste the results here.

Select Column_Name, Data_Type, Character_Maximum_Length 
from Information_Schema.Columns 
Where table_name = 'Roles'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Luzian]

My fields are correct. I double checked my syntax, the RETURN statement I had was incorrect. Your syntax ir correct, sorry about that. Thanks

 

[gmmastros]

You're welcome. I'm glad you got this working.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom