question

Wenjing avatar image
Wenjing asked

How to pass a parameter from a view to table value function?

Hello, I have an application using ODBC to access the data in SQL Server database. It only accesses tables or views. The application will use select * from view where cl1='xxxx'. the xxxx changes everytime it excutes the SQL statement. The data it retrieves contains two tables. One table contains depth from and depth to and another table contains depth. The depth can be within depth from and depth to. We have to return the depth without overlapping. To solve this problem I created a table value function which will break first table depth range with depth in second table. For example, the two table results like this:- Table 1 Table 2 name depth_from depth to string name depth string2 n1 33 58 S1 n1 33 H1 n1 69 90 S2 n1 42 H2 n1 47 H3 The final results to be returned should be like this:- name depth from depth to string n1 33 33 H1 n1 33 42 S1 n1 42 42 H2 n1 42 47 S! n1 47 47 H3 n1 47 58 S1 n1 69 90 S2 The function worked well with returning full data set which has more than 50000 records. But the application only extracts one name at a time, and have more than 5000 names. I would like to know how to pass name from view to the function so the function only extracts one name at a time. Your help is much appreciated

parametersviewtable-valued
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

You need to find a way of joining a table and a function depending on that table. Using a normal join won't work, SQL Server will tell you "the multi-part identifier could not be bound". But using CROSS APPLY instead of a JOIN might solve that for you.

If you craeate the function using "name" as a parameter, I think this view definition would work:

CREATE VIEW v AS
SELECT T1.name AS T1NAME, f.* FROM
[Table 1] T1
CROSS APPLY
dbo.fnMyFunction(T1.name)

And then call the view like:

SELECT * FROM v where T1NAME = 'N1'

I've tested it with a much simpler scenario, just one table, and a function doing "select *" from that table. But the idea is the same, so if my example doesn't work, I still think you should be looking at using CROSS APPLY to be able to simulate a join between the table and the function depending on a column in the table.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.