Create a view help!

Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>

Create a view help!

Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>

Create a View Command in Query Analyzer

Hey...a simple question that I forgot...
May I know how to create a view under Query Analyzer...below is what i have
type, but command succesfully, but view not created...wonder why ?
CREATE VIEW dbo.VIEW1
AS
SELECT * FROM Table1If the command was executed successfull the view was created unless you
didnt turn on something like SET Parseonly etc. Did youdirectly Select from
the row after it was created ? "Select * from View1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>|||but I tried to go to Enterprise Manager, SQL Server, mydatabase, then under
the VIEWS there...
i click...the newly created View1 is not shown there, but I am able to
Select * from View1...
May I know how to make my View1 to be able to see from the enterprise
manager.
thank you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||The gui not always refreshs the right way, did you right click on the View
node and selected Refresh ? That should do it if you placed it in the right
database. Perhaps you issued the command in the wrong database ? Just proof
It via Select @.@.servername,DB_NAME().
If the view was created it should appear in the INFORMATION_SCHEMA:
Select * from INFORMATION_SCHEMA.Tables
Where TABLE_NAME LIKE 'View1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:OAKfHkPXFHA.2540@.tk2msftngp13.phx.gbl...
> but I tried to go to Enterprise Manager, SQL Server, mydatabase, then
> under the VIEWS there...
> i click...the newly created View1 is not shown there, but I am able to
> Select * from View1...
> May I know how to make my View1 to be able to see from the enterprise
> manager.
> thank you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||YES, Jens...I am done with that...
Just wonder how could I delete a view then ?
thanks you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I have a suggestion here.
Do you have dbo right to view all objects?
Since you have created it using dbo, I am not sure whether you are using dbo
right to view the VIEW1.
another thing is, you can check BOL on how to drop a view.
Syntax
DROP VIEW { view } [ ,...n ]
thanks.
Leo Leong|||Drop View [Viewname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:%23PT391PXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> YES, Jens...I am done with that...
> Just wonder how could I delete a view then ?
> thanks you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||I think you were connected to a different database in Query Analyzer than
you think you were (the one you were looking in in Enterprise Manager). You
can use the Object Search (F4) in Query Analyzer to search for a view called
View1 in all your databases.
Jacco Schalkwijk
SQL Server MVP
" A_PK" <pk999@.hotmail.com> wrote in message
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>sql

Create a view based on variables?

Hi All,

I would like to create a view based on a variable (a date).

CREATE VIEW testView (@.myDate)
AS

select * from testTable
WHERE testVar = @.myDate

--
This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it.

Thanks.I would say "No, it can't." Why can't you just create the view without the where clause and select from it where the date = @.myDate when you need it? You could create a function that returns a table and takes the date as an argument.

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

Create a View

I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields: id and child. So, my table currently looks like this:

id child1 child2 child3

1 sam bob chris

and i would like the view to display the information like this.....

id child

1 sam

1 bob

1 chris



Can anybody help me? Thanks in advance,

BobIf your DBMS permits a union view, try:

create veiw view1
as
Select id, child1 child
from table1
union
Select id, child2 child
from table1
union
Select id, child3 child
from table1;

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.