Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CREATE VIEW FROM RESULTS OF STORED PROCEDURE

Posted on 2003-03-18
10
Medium Priority
?
542 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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
Suggested Courses

572 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