| Author |
Topic |
|
allend2010
Starting Member
28 Posts |
 Posted - 2003-03-13 : 08:58:12
|
| Hello:I have a simple question: Is it possible to dynamically create a view from within a stored procedure? I tried and I get an error.CREATE PROCEDURE sp_something AS ...--And then somewhere else in the proc,CREATE VIEW ViewSomeData AS SELECT * FROM DataTableGO...GOI get an error when I try to create the procedure. Any help is apprecieated.Thanks,Al |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-13 : 09:03:08
|
| use EXEC?EXEC('CREATE VIEW ViewSomeData AS SELECT * FROM Test')I think that would work. Probably other ways too, I'm sure others will have better ideas.- Jeff |
 |
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-03-13 : 09:22:25
|
| Can I ask why do you want to dynamically create a view?Bambola. |
|
|
|
allend2010
Starting Member
28 Posts |
Posted - 2003-03-13 : 09:43:34
|
| Well, in this case I can create the views as a one time procedure but this particular process incur a lot of changes (adding fields, constraints, etc...) I figured in this manner when ever I have to make changes I could just do it from within the stored proc instead of having to change the stored proc and the views. In otherwords, I wanted to embed everything in one place. But I suppose just creating the views separately isn't so bad. Thanks. |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-03-13 : 11:32:27
|
| I was asking because a view (unless it is an indexed view) is like a select statement that is being executed every time you are trying to access the view. If you give us more details, maybe we can help more.Bambola. |
|
|
|
SqlStar
Posting Yak Master
121 Posts |
Posted - 2003-03-14 : 09:28:12
|
| Hi,If u create view thru SP, whenever u execute the specified SP, that will try to create one more view with same name and it will raise a error.How to avoid this? Why u create dynamic view thru SP? My suggestion, it may be avoid.IT Knowledge is power |
|
|
|
SqlStar
Posting Yak Master
121 Posts |
Posted - 2003-03-14 : 09:28:13
|
| Hi,If u create view thru SP, whenever u execute the specified SP, that will try to create one more view with same name and it will raise a error.How to avoid this? Why u create dynamic view thru SP? My suggestion, it may be avoid.IT Knowledge is power |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-03-14 : 11:20:41
|
quote:
Hi,If u create view thru SP, whenever u execute the specified SP, that will try to create one more view with same name and it will raise a error.How to avoid this? Why u create dynamic view thru SP? My suggestion, it may be avoid.IT Knowledge is power
SqlStar - This is not really the problem. He can drop the view before creating it. allend2010 - what I was trying to understand if this create view involves paramaters and that was the reason to create it dynamically. If this is case then you can find better solution. Bambola. |
|
|
|
SqlStar
Posting Yak Master
121 Posts |
Posted - 2003-03-15 : 00:25:15
|
quote:
SqlStar - This is not really the problem. He can drop the view before creating it.
Sorry. I totally forgot that.Thanks. I will find any other solution for that.IT Knowledge is power |
|
|
|
|
Creating Views