?
Solved

CREATE VIEW FROM RESULTS OF STORED PROCEDURE

Posted on 2003-03-18
10
Medium Priority
?
524 Views
Last Modified: 2008-01-16
IS IT POSSIBLE (IF SO WHAT IS THE SYNTAX) TO CREATE A VIEW ON THE RESULTS OF A STORED PROCEDURE IN SQL SERVER 2000?
0
Comment
Question by:PUMASOFT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 9

Accepted Solution

by:
xenon_je earned 120 total points
ID: 8158034
The answer is NO. At least this is how I know it. You can create a view only based on a select statement.
xenon
0
 

Assisted Solution

by:BigBruce
BigBruce earned 90 total points
ID: 8158082
-- This code will create a function and then build a procedure and view based on that function.  
-- You can use almost all the same functions in a UDF as you can in a Proc but there are a few that you can't
-- You can find these in the help files.  And of course SQL will tell you what you can and can't do.
-- if you need to use one of them you may have to pass the value in.

Drop function TestFunc
go
Drop Proc TestProc
go
Drop view TestView
go

Create Function TestFunc() RETURNS TABLE
as
return Select 1 FstCol, 2 SecCol, 3 ThrCol
go

Create Proc TestProc as
Select * from TestFunc()
go

Create View TestView as
Select * from TestFunc()
go

TestProc
go
Select * from TestView
0
 
LVL 2

Author Comment

by:PUMASOFT
ID: 8158143
Thanks for your input.

I have already looked at the possibility of functions, however the sp uses temporary tables and according to the help, functions cannot use temporary tables.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Expert Comment

by:BigBruce
ID: 8158222
I just tired and sure enough it don't work.  So what are you trying to do?  There may be another way to get the results that you are looking for.

0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 90 total points
ID: 8158367
There is the syntax:

create view vw_test
as
select * from openquery (YourSERVER,'exec master..sp_lock')
0
 
LVL 2

Author Comment

by:PUMASOFT
ID: 8159238
Bit of a long story, but in summary,

We have transferred a very legacy ISAM database to SQL - without looking at the database design or datbase access (I dont need to expand on that one). SQL Server is doing its best to pretend it is ISAM. This has resulted in very poor performance through our cobol app (what a surprise).

The SP is an attempt to convert a COBOL program to an SP and then make the values available through CRYSTAL reports, in an easy format that the users can use without training.

I advocate that the users use crystal to look up to the stored procedure (not a view) but this is deemed to be too difficult for them.

My personal viewpoint is the we either need to redesign some elements of the database/application and/or retrain users.

I also think that the view from a sp (passing parameters) is impossible in a simple method that users can use - but I have to prove it.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8159271
Then you do not need view.
Just use SP with parameters...for CR.
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 8159285
If you use crystal reports, than you can set as the datasource for the report a stored procedure. Doesn't this help you?
xenon
0
 

Expert Comment

by:BigBruce
ID: 8161275
I haven't spent much time in Crystal, but can't you create stock reports that are already attached to the stored proceudres then programicly prompt the user for the vars?

That would seem to solve the problem.  You don't have to do anything with the stored procedures you just tweak the UI a bit.  

If nothing else you could write a VB "Report" engien that you would run your reports through.  depending on the reports you could dynamicly generate a paramiter entry screen and prompt the users for the information.

But, (shrug) I would do something like that.
0
 

Expert Comment

by:CleanupPing
ID: 9275770
PUMASOFT:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question