cancel
Showing results for 
Search instead for 
Did you mean: 

Is it possible to create a function that returns a table?

Former Member
7,088

What I actually want to do is create a filtered view of multiple tables. Filter parameters will be given to the function as parameter. Only scalar types can be selected in "create function" wizard. Does that mean it's not possible to create a function that returns a table, like in SQL Server? Do I have to go SP-way? (Sqlanywhere v12)

Accepted Solutions (0)

Answers (3)

Answers (3)

VolkerBarth
Contributor

Besides an STP (as Breck has suggested), you might "filter" a view based on the contents of a connection-specific variable (cf. CREATE VARIABLE), such as

create view MyView
as
select * from MyTable
where MyCol1 = @MyVar;

-- client 101
create variable int @MyVar = 101;

-- client 102
create variable int @MyVar = 102;

-- now when both use the view, they get different result sets.

The VAREXISTS function would be helpful to deal with missing variables...

Former Member
0 Kudos

I didn't know about this method, thank you!

Breck_Carter
Participant
0 Kudos

Just the other day, I was thinking (for the umpteenth time) how nice it would be if views had parameters, as in SELECT * FROM employee_view ( @employee_id )... d**n thing is turning up in my dreams now, can't get away from it 🙂

VolkerBarth
Contributor
0 Kudos

@Breck: Time to add something to this nice FAQ...?

VolkerBarth
Contributor
0 Kudos

FWIW: As @Breck has explained in detail in his actual blog article, such a CREATE VARIABLE-filtered view might not be useful for security-related filtering (i.e. not to prevent users from viewing undesired rows)...

Breck_Carter
Participant

A FROM clause in SQL Server can refer to a function that returns a table.

A FROM clause in SQL Anywhere can refer to a procedure that returns a result set.

...I'm not sure what makes them different other than some syntactic sugar in the CREATE statement.

What is it that you want to do, that you cannot do with a SQL Anywhere procedure that you can do with a SQL Server function?

Former Member
0 Kudos

Well, I'm used to that sugar:) Stored procedures are ok, and I've used them instead of table-returning functions before, but I wanted to learn if there is some other way in SQLA that I don't know.

Breck_Carter
Participant
0 Kudos

...but I still want to know, is there something a SQL Server function offers that a SQL Anywhere stored procedure doesn't? I like syntactic sugar, being fat and all 🙂

Former Member
0 Kudos

Not that I know of. I didn't mean to compare them by the way, my mistake if it sounded like that. It's just old habits die hard:)

VolkerBarth
Contributor
0 Kudos

IMVHO, the list of limitations for the MS SQL CREATE FUNCTION (when used as scalar function - which would be similar to a SQL Anyhwere stored function) is one of the reasons I strongly prefer SQL Anywhere...

Former Member
0 Kudos

functions in SA can ruturn resultset!

Breck_Carter
Participant

Please, show us how!

Former Member
0 Kudos

Take XML for return data type and process it with openxml(). But usual procedures is better