x

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

more ▼

asked Jun 18, 2010 at 10:32 PM in Default

avatar image

Wenjing
15 2 2 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jun 19, 2010 at 06:42 PM

avatar image

Magnus Ahlkvist
22k 20 41 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x93
x69
x19

asked: Jun 18, 2010 at 10:32 PM

Seen: 7343 times

Last Updated: Jun 18, 2010 at 10:32 PM

Copyright 2017 Redgate Software. Privacy Policy