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)

asked 17 Feb '12, 10:16

henginy's gravatar image

henginy
406151827
accept rate: 0%

edited 15 Mar '13, 21:25

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269


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...

permanent link

answered 17 Feb '12, 10:33

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 17 Feb '12, 10:34

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

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

(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?

permanent link

answered 17 Feb '12, 10:25

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%

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!

permanent link

answered 17 Feb '12, 10:28

AlexeyK77's gravatar image

AlexeyK77
70761224
accept rate: 8%

1

Please, show us how!

(17 Feb '12, 12:00) Breck Carter
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×104
×26
×25
×13

question asked: 17 Feb '12, 10:16

question was seen: 2,680 times

last updated: 15 Mar '13, 21:25