View Creation Q

View Creation Q

Post by George Durz » Fri, 27 Feb 2004 04:06:46



I have a stored procedure that accepts no parameters, I would like to create
a view that displays the execution results of that stored procedure.

I tried avoiding writing a stored procedure at all, and simply putting all
the SQL in the view. However, the stored procedure uses temporary tables.

I also tried doing something like this :

create view ViewName
as Exec SPName

but that didn't work either (I was just coming up with ideas - don't know if
that's even possible)

Any suggestions?

The reason this has to be a view is that my client is gonna use Excel (dunno
how) to open the view, and whatever they're using to do that won't work on
sprocs.

 
 
 

View Creation Q

Post by Russell Field » Fri, 27 Feb 2004 05:56:14


George,

If you are on SQL 2000 you could rewrite this stored procedure to be a
multi-statement table valued user-defined function.  Then you could have all
the logic that does not fit into a view, but treat it as a view.

Select * from MyUserDefinedFunction()

If Excel cannot select from a UDF, then wrap that in a view.

Create View MyView
AS
Select * from MyUserDefinedFunction()

Russell Fields


Quote:> I have a stored procedure that accepts no parameters, I would like to
create
> a view that displays the execution results of that stored procedure.

> I tried avoiding writing a stored procedure at all, and simply putting all
> the SQL in the view. However, the stored procedure uses temporary tables.

> I also tried doing something like this :

> create view ViewName
> as Exec SPName

> but that didn't work either (I was just coming up with ideas - don't know
if
> that's even possible)

> Any suggestions?

> The reason this has to be a view is that my client is gonna use Excel
(dunno
> how) to open the view, and whatever they're using to do that won't work on
> sprocs.


 
 
 

View Creation Q

Post by George Durz » Fri, 27 Feb 2004 07:48:23


Thanks Russell, I'll try that out


> George,

> If you are on SQL 2000 you could rewrite this stored procedure to be a
> multi-statement table valued user-defined function.  Then you could have
all
> the logic that does not fit into a view, but treat it as a view.

> Select * from MyUserDefinedFunction()

> If Excel cannot select from a UDF, then wrap that in a view.

> Create View MyView
> AS
> Select * from MyUserDefinedFunction()

> Russell Fields



> > I have a stored procedure that accepts no parameters, I would like to
> create
> > a view that displays the execution results of that stored procedure.

> > I tried avoiding writing a stored procedure at all, and simply putting
all
> > the SQL in the view. However, the stored procedure uses temporary
tables.

> > I also tried doing something like this :

> > create view ViewName
> > as Exec SPName

> > but that didn't work either (I was just coming up with ideas - don't
know
> if
> > that's even possible)

> > Any suggestions?

> > The reason this has to be a view is that my client is gonna use Excel
> (dunno
> > how) to open the view, and whatever they're using to do that won't work
on
> > sprocs.

 
 
 

View Creation Q

Post by --CELKO » Fri, 27 Feb 2004 12:15:24


It is hard to debug code without seeing it, but here goes: replace the
temp tables with derived tables and create a VIEW.

The SQL Server temp table model is highly proprietary.  Temp tables
are usually a sign of bad coding -- they tend to hold steps in a
process instead of being a declarative solution to the problem.