| Author |
Topic |
|
mmalaka
Starting Member
33 Posts |
 Posted - 2008-07-15 : 12:36:45
|
| Is it possible to create a view as a result of a stored procedure?I am trying to do this coz the stored procedure is the best option for high performance. Is this true? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-15 : 13:22:32
|
quote:
Originally posted by mmalaka
Is it possible to create a view as a result of a stored procedure?I am trying to do this coz the stored procedure is the best option for high performance. Is this true?
view is actually a virtual table. You can create views only from tables or other views. can i ask what's your intention behind asking this? |
 |
|
|
AK
Starting Member
27 Posts |
Posted - 2008-11-06 : 08:21:41
|
| HiCould you please expand upon why you should not create views from stored procedures.Thanks |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-11-06 : 10:14:47
|
| Stored procedures are good for performance due to them not recompling each time, but you can create the same perfromance from an indexed view |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 10:57:55
|
quote:
Originally posted by NeilG
Stored procedures are good for performance due to them not recompling each time, but you can create the same perfromance from an indexed view
But for a view to be indexed, it should follow a lot of guidelines. see belowhttp://www.sqlteam.com/article/indexed-views-in-sql-server-2000 |
|
|
|
Kobojunkie
Starting Member
8 Posts |
Posted - 2008-11-06 : 12:28:38
|
| It would help if we could see the code you need to use to generate your view. Yes, you can do this from a stored proc but before telling you to do just that, I would prefer to see what exactly you have and tweak that to see if it be the best choice. |
|
|
|
AK
Starting Member
27 Posts |
Posted - 2008-11-06 : 12:44:11
|
| This creates a view based on a few parameters and a check on whether a table exists:alter procedure csp_CreateView ( @client char(3), @ExtractDate varchar(10), @CampaignDate varchar(10)) asdeclare @sql varchar(8000)declare @ExtractTable varchar(50)declare @PcodeTable varchar(30)declare @ViewName varchar(30)declare @TableSuffix varchar(30)declare @TransTable varchar(30)set @TableSuffix=@client+'_XZ_'set @ExtractTable=@TableSuffix+@ExtractDateset @PcodeTable=@TableSuffix+@CampaignDate+'_PCODE'set @ViewName=@client+'_POPLN_XZ_'+@CampaignDateset @TransTable=@client+'_TRANS_XZ_'+@ExtractDate-- drop view if it already existsset @sql='if exists (select name from sysobjects where name='''+@ViewName+''' and type=''V'') drop view '+@ViewName--print @sqlexec(@sql)-- create campaign table if it does not existset @sql='if not exists (select name from sysobjects where name='''+@PcodeTable+''' and type=''U'') create table dbo.'+@PcodeTable+' (cis_id varchar(20) primary key, promotion_code varchar(50))'--print @sqlexec(@sql)-- create viewset @sql='declare @sql varchar(500)if not exists (select name from sysobjects where name='''+@TransTable+''' and type=''U'') set @sql=''create view '+@ViewName+' as select a.*, c.promotion_code from '+@ExtractTable+' a left join '+@PcodeTable+' c on a.cis_id=c.cis_id''else set @sql=''create view '+@ViewName+' as select a.*, b.*, c.promotion_code from '+@ExtractTable+' a left join '+@TransTable+' b on a.cis_id=b.TCustomerUrn left join '+@PcodeTable+' c on a.cis_id=c.cis_id''exec(@sql)'--print @sqlexec(@sql) |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-06 : 12:45:03
|
quote:
Originally posted by visakh16
[quote]Originally posted by mmalakaview is actually a virtual table. You can create views only from tables or other views. can i ask what's your intention behind asking this?
I think what mmalaka means is, creating new views by running a stored proc.Is that right mmalaka ? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
AK
Starting Member
27 Posts |
Posted - 2008-11-07 : 05:32:44
|
| Not sure I understand.Are you saying its not clear? - or it something you shouldnt do?Do you need to know the contents of the tables to follow the logic? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AK
Starting Member
27 Posts |
Posted - 2008-11-09 : 07:45:10
|
| Thanks for the reply. Unfortunately the first link did not work.Are you saying its not advisable to create views in stored procedures - or just not in this way? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
View from a Stored Procedure