March 13, 2008 at 6:56 am
Is it possible to create a temporary view from within a stored procedure for the life of the stored procedure. I do have a way of doing it now where I create the view and at the end it is removed but there is an issue with giving the user dbo authority in order to do this. Plus it phyiscally write and removes the view.
Creating Temp views allows for the more flexiblilty vs creating numerous permanent views.
Any help would be appreciated.
March 13, 2008 at 7:00 am
No... no way to create a temp view. Creating a TempTable to hold what the TempView would have contained is the next best thing and pretty darned fast to boot.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2008 at 7:10 am
Jeff Moden (3/13/2008)
No... no way to create a temp view. Creating a TempTable to hold what the TempView would have contained is the next best thing and pretty darned fast to boot.
Jeff, what if the view/temp table were SELECT * FROM MyTable, and MyTable contained 10,000,000 rows? If the query executed against that temp table were SELECT * FROM #View WHERE ID = 10000000, then that's an awful lot of lot of work to populate the temp table to be only selecting one row. I know that's an extreme example, but wouldn't Donald be better of looking at using a CTE if possible?
John
March 13, 2008 at 7:50 am
could you explain how a CTE would work. I failed to mention that this is for SQL 2000.
March 13, 2008 at 7:59 am
Ah. Please take care to post in the correct forum. There are no CTEs in SQL Server 2000. Would you be able to use a subquery instead? It's messier but it may be the best option.
John
March 13, 2008 at 8:03 am
What would a "temporary" view do that a query doesn't?
"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
March 13, 2008 at 8:16 am
I suspect you're after syntactic convenience which is why you want a view rather than repeating the same snippet of SQL in several places. Unfortunately, unless you use a temp table, you will probably need to repeat the code.
Also, I have to ask, if you're creating a view in the proc then presumably the view's code is dynamic so you would be using dynamic SQL. Is that right?
Finally, you could create the view in the user's schema rather than in the dbo schema....
March 13, 2008 at 8:52 am
My goal was to create a temp view instead of having to create many,many permanent views.This temp view would be be used in a query built from the same stored procedure. developing this in our test environment works great! but upon moving it to the Production environment it did not work due to the restriction on the database user. Access to the database from the program goes through only one user ID. I had to give it dbo access in order to create the temp view.
I appreciate all of the help but I think I am going to have to go back to the drawing board and come up with a different plan.
Thanks.
March 13, 2008 at 8:56 am
Based on the description I still don't understand what the temporary view was doing for the query in question.
"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
March 13, 2008 at 8:57 am
I suspect you haven't tested this with concurrent users/sessions... The "first" session will do great - and the other sessions will fall apart because the "first" view is there. Or - the last session will come by and start dropping views while the other sessions are trying to use them.
Go dynamic SQL if you have, or leverage temp tables. The view concept will bring you heartaches, IMO.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 9:38 am
John Mitchell (3/13/2008)
Jeff Moden (3/13/2008)
No... no way to create a temp view. Creating a TempTable to hold what the TempView would have contained is the next best thing and pretty darned fast to boot.Jeff, what if the view/temp table were SELECT * FROM MyTable, and MyTable contained 10,000,000 rows? If the query executed against that temp table were SELECT * FROM #View WHERE ID = 10000000, then that's an awful lot of lot of work to populate the temp table to be only selecting one row. I know that's an extreme example, but wouldn't Donald be better of looking at using a CTE if possible?
John
Obviously, there would have to be some constraints in play. It all "depends". Of course, if the table exists and you're only selecting one row, why do you need a view to begin with?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2008 at 9:43 am
Folks, just to be clear, you might be able to create a permanent view on the fly and drop it at the end of the proc, but there is no such thing as a "temporary view" with a leadin "#" sign similar to a temporary table. It just ain't happenin'...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2008 at 9:55 am
Jeff Moden (3/13/2008)
Of course, if the table exists and you're only selecting one row, why do you need a view to begin with?
As I mentioned, that's an extreme example. But in general I think it's better not to have to materialise the data in the view just in case you don't come close to using it all. That's why I suggested the CTE or subquery approach.
John
May 20, 2018 at 7:42 am
No create temporary view.
I tried temporary procedure.
May 20, 2018 at 11:43 am
jawahar11088 - Sunday, May 20, 2018 7:42 AMNo create temporary view.I tried temporary procedure.
And???
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply