|
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) |
|
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... I didn't know about this method, thank you!
(17 Feb '12, 10:53)
henginy
Replies hidden
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 :)
(17 Feb '12, 11:28)
Breck Carter
(17 Feb '12, 11:47)
Volker Barth
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)...
(21 Feb '12, 03:02)
Volker Barth
|
|
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? 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.
(17 Feb '12, 10:50)
henginy
Replies hidden
...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 :)
(17 Feb '12, 11:30)
Breck Carter
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:)
(17 Feb '12, 12:00)
henginy
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...
(18 Feb '12, 07:58)
Volker Barth
|
|
functions in SA can ruturn resultset! Replies hidden
Comment Text Removed
Take XML for return data type and process it with openxml(). But usual procedures is better
(21 Feb '12, 04:53)
AlexeyK77
|
