on 2012 Feb 17 10:16 AM
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)
Request clarification before answering.
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)...
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
functions in SA can ruturn resultset!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please, show us how!
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.