Can a View Call a Stored Procedure? 2

[checkai]

Can a View Call a Stored Procedure?

that's my only question!!

DLC

 

[tlbroadbent]

No, a view cannot reference a stored procedure. However, in SQL Server 2000, you can create user defined functions and reference a function in a view.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[checkai]

Can I put the entire contents of a SP into a function?

If yes, how do I create the function/call it by the view?

THanks,

DLC

 

[tlbroadbent]

There are certain rules for creating functions and not all stored procedures can be converted to functions. The basic syntax for creating a function can be found in SQL BOL.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_7r1l.asp

Does the stored procedure return a single scalar value or a result set?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLSister]

I'm just curious here, why not just call the stored procedure instead of going through the added step of making it a view? Maybe if you told us more about why you are doing this we could suggest a better way to accomplish what you want.

 

[checkai]

We have a new system that uses queries to send out "email Alerts"

This system only recognizes TABLES and VIEWS. Doesn't recognize the SP's...so I want to use a VIEW...

The result set consists of NAME,DESCRIPTIVE STRING,SUM(HOURS)

however I have used a cursor in the SP

Dustin

 

[SQLSister]

AH now I see. OK post your stored procedure and if we can convert it from a cursor to a set-based statement, you can use that to create the view.

 

[checkai]

I have gotten it figured out now...with help from others...thanks a bunch!

Dustin