Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2009-01-27 : 04:58:16
|
| Hi,One of our systems has a view that is defined as follows:SELECT [Member_id] FROM TABLE1 WHERE CentreId IN (SELECT [id] from TABLE2 WHERE [id] BETWEEN MinCentreId() AND MaxCentreId() )MinCentreId() and dbo.MaxCentreId() are functions. With the above however, these functions are being calculated for every row in the search, and so the query is taking to long to fully execute.DECLARE @minId INTDECLARE @maxId INTSET @minId = MinCentreId()SET @maxId = MaxCentreId()SELECT [Member_id] FROM TABLE1 WHERE Centreid IN (SELECT [id] FROM TABLE2 WHERE [id] BETWEEN @minId AND @maxId)This query returns the results instantly as the values are calculated at first. However DECLARE statements cannot be used in views. Any ideas on alternatives?ThanksDrewDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
heavymind
Posting Yak Master
115 Posts |
Posted - 2009-01-27 : 06:09:04
|
| You can't declare variables within a view. Possible solutions are :1 change view to be a table-valued function or stored procedure.2 change the vuew to not use functions if possible - insert functions logic as subqueries etc.. |
 |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2009-01-27 : 06:14:41
|
| Yeh, I might recommend the vendor uses a proc.ThanksDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-27 : 09:14:21
|
| i would suggest using UDFs as it improves usability. UDFs can be easily used in select statement in select list, joins, where etc... |
|
|
|
|
|
|
DECLAREing variables in Views