Question about views

Question

Question about views

WoFe

Mr or Mrs. 500

Points: 552
More actions
May 3, 2007 at 7:35 am

#195558

Hey guys i have a question about views, Please read below.
i am wanting to go with a view also now that i have really looked at it but i have all my calculations done in the User Functions. I tried putting them into views and it takes the views over 2 minutes to load. With using the functions I cut it down to less than a second to load with filtering. Is there a way to build a view that will get the parameter of my function with a value that is aready part of the views data.
Example.....
SELECT
             a1.TrackingNo, a1.OrderNo, a1.Location, a2.OrderStatus
FROM
             tblTracking a1 LEFT JOIN ON fxOrder(@TrackingNo) a2 ON
             a1.OrderNo = a2.OrderNo
Can I make this a View????

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

If I understand the question... No. You can't pass parameters to a view.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

WoFe Mr or Mrs. 500 Points: 552 More actions · Answer 2

Thanks for the Replay, i know you can't pass them into a view but is the anyway to change the @TrackingNo to a1.TrackingNo or DECLARE a variable and set it = a1.trackingno and have it pass in the function?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

No, but you can write a function that returns a table. This function can act as a parameterized view. I think there is an example in BOL, but don't quote me on that.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

I don't think you can. At least, I don't seem to be able to in the tests I've run so far. However, can you, instead, return a larger set of data from the function and then simply add the extra value to the join criteria.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Grant,

I've done it in SQL 2000 and SQL 2005. If you want, we can take this topic offline a bit and see what issues you were having.

Thanks

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

Sorry, I was answering the other question, not your statement. You're correct. That, in fact, is probably what he needs to do. I apologize for any confusion.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I think I was born confused, but of course with 2 teenager girls at home, that could account for it also.

WoFe Mr or Mrs. 500 Points: 552 More actions · Answer 8

thanks for the replies... it looks as if i am going to use a stored procedure that does all the joins and stuff. I was wanted to populate a table with the view on the web but it will not work like i wanted... so i am using a stored procedure instead.

thanks for your help everyone,

WoFe