How to call a Store proc inside a view

Can anyone tell me how to call a sp inside a view.I dont think you can do that
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
> Can anyone tell me how to call a sp inside a view.
>
>|||You will need to set-up a linked server (LOCALHOST), and then...
CREATE VIEW vw_Test
AS
SELECT *
FROM OPENQUERY(LOCALHOST, 'SET FMTONLY OFF; EXEC pubs..Your_SP')
However, the SP is actually executed twice, so there is a performance
cost. If the SP performs updates/inserts, these are also performed
twice which is probably not what you want.|||You are right.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Pradeep Kutty" wrote:

> I dont think you can do that
> "Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
> news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
>
>|||However, if your SP doesn't create temp tables, you can do this (only
called once):
CREATE VIEW vw_Test
AS
SELECT *
FROM OPENQUERY(LOCALHOST, 'EXEC pubs..Your_SP')|||Not easily. You are asking the wrong question really. Call your view
from a proc if you need to, not the other way around. Maybe you could
explain just why you want to do this.
David Portas
SQL Server MVP
--|||I have 10 tables with common fields(splitted 1 into 10 due to easy access of
data). I want to make a view of it by combining all the tables using union
all.
From that view I will make a self join and do my operations. I am ok with
all these tasks. But my worry is if a new table is being added tomorrow I
have to hard code the 11th table in my view. I also have a log of how many
tables are added in a common table. So I want the view to autmatically add
the new table to the view with the help of the log table , which I will be
able to do it in an SP.
"David Portas" wrote:

> Not easily. You are asking the wrong question really. Call your view
> from a proc if you need to, not the other way around. Maybe you could
> explain just why you want to do this.
> --
> David Portas
> SQL Server MVP
> --
>|||Surely, you would know when the table is being added. You could do the
ALTER VIEW at that time and nothing else is required.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:19BD2C7D-D08C-4A5B-ABBE-472651BBE8B2@.microsoft.com...
I have 10 tables with common fields(splitted 1 into 10 due to easy access of
data). I want to make a view of it by combining all the tables using union
all.
From that view I will make a self join and do my operations. I am ok with
all these tasks. But my worry is if a new table is being added tomorrow I
have to hard code the 11th table in my view. I also have a log of how many
tables are added in a common table. So I want the view to autmatically add
the new table to the view with the help of the log table , which I will be
able to do it in an SP.
"David Portas" wrote:

> Not easily. You are asking the wrong question really. Call your view
> from a proc if you need to, not the other way around. Maybe you could
> explain just why you want to do this.
> --
> David Portas
> SQL Server MVP
> --
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:543962

> I have 10 tables with common fields(splitted 1 into 10 due to easy access
of
> data).
Your problem makes one thing clear: splitting tables with common
columns makes data HARDER to access not easier. By doing this you force
youself either to make lots of messy UNIONs or (worse) to use dynamic
SQL everywhere.
Splitting data across multiple identical tables is commonly understood
to be a big design error. In SQL2000 it makes sense in certain fairly
limited circumstances in support of partitioned views. If you are stuck
with this design for now then look up Partitioned Views in Books
Online. Basically you can create the view once and then reference it
everywhere as if it were a single table. Don't reference the base
tables.
David Portas
SQL Server MVP
--|||loopback query (though not really recommended due to perf penalty) would
allow you to..
e.g.
exec sp_serveroption 'srv','data access','true'
go
if object_id('_v','v') is not null
drop view _v
go
create view _v
as
select *
from openquery(srv,'set fmtonly off; exec sp_lock')x
go
select * from _v
go
-oj
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
> Can anyone tell me how to call a sp inside a view.
>
>