Dynamic Views
-
I have the need to dynamically insert a WHERE clause into a VIEW before executing it. I can accomplish this by inserting a /*COMMENT*/ in the location of the VIEW where the WHERE Clause will go. Next I grab the View contents as a string and parse it, insert the WHERE string and EXECUTE it.
ATTEMPT 1 : Use this query in my SP to grab the info :
SELECT @SQL = VIEW_DEFINITION
FROM MyDBName.INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = @VIEWName
however if the VIEW is over 4000chars then it doesnt work because of the contraint of the field in Master DB.
ATTEMPT 2 : Use this query :
EXEC sp_helptext @VIEWName
This returns a recordset of one row per line of the VIEW. But I need to parse through it and I cant using a CURSOR because EXEC are not allowed in CURSOR SELECT statement.
Any ideas or other solutions
-
Why do you need to insert a where clause into the view? Define the view without it and insert the where clause into your query of the view.
Steve Jones
-
Cant do that because the WHERE must be done before the GROUP BYs that the VIEWS contain(granualarity of data issue).
I know it sounds like a job for a SP but it would take too long to explain why this method was chosen.
-
Personally I'd write multiple views to cover the possibilities. How often would they change? If it's not that often (you have to judge that), I'd then just hit the right view.
Might seem like more work, but probably would run better.
Steve Jones
-
Is the 'where' clause generally going to be an equality join on a particular column? If so you could have a table into which you write the search value for the view, and include a join to that table in the view; something like:
select <cols>
from <table...join....table>
left join SearchArg S on S.spid = @@spid
and <table>.<column> = S.value
(don't know if the SPID bit is necessary - for concurrent users - and if so, not sure if this will query the @@spid function for every row, or 'remember' the value. I think it should do the latter.)
If you did this, you wouldn't need to use dynamic SQL.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Muliple View are not a feasible option. The WHERE cause is being built dynamically on a web page and the number of columns possible are over 20.
Go back to my original request :
I cant use a CURSOR because EXEC are not allowed in CURSOR SELECT statement.
is this true? do I have any other means to Cursor through the results. If so problem solved.
Edited by - ccarns on 06/19/2003 12:27:35 PM
-
Oh I see. OK - from memory, you can use:
declare cr_ViewText
cursor fast_forward
for
select c.text from syscomments c
join sysobjects o
on c.id = o.id
where o.name = @ViewName
order by c.colid asc
That will give you your cursor.
I think it might include the 'CREATE VIEW' part, not just the SQL. so you'll have to strip that out.
But if all this is going on in SQL server, you're still going to run up against length problems if you try to build up the view definition in a single variable. You'll need to have plenty of variables to fall back on.
Luckily,
exec(@sql1 + @sql2 + @sql3 + @sql4 + @sql5)
works fine; each variable is really a separate parameter to the EXEC function, even though the delimiters are spelt the same as the concatenation operator.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
That's in SQL 7 - SQL 8 might be different. we haven't upgraded...yet.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thanks stax68 that is exactly what I am after.
Thanks for all the help for all those who posted.
Craig
-
In my opinion this is a design issue. I would change from View to a Stored Procedure with the parameters necessary on your Where Clause.
IF it has to be used as a view you could use a Table function an use it as a parameterized view but this is only on ss2k
* Noel -
If using the cursor becomes too slow, you could always create a view on the fly passing your where clause and then run your query off of that view.
If there is an issue with accessing the view via multiple people, you could dynamically name the view using the date or something.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply