Creating View from stored procedure

Question

Post reply

Creating View from stored procedure

shamshudheen

SSCrazy

Points: 2390
More actions
April 15, 2008 at 7:19 am

#71834

Hi All,
Is there a way for creating a view from the stored procedure
Help is highly appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

do you want to create a view from within your stored procedure?

Or do you want to create a view that returns the same data as your stored procedure?

please expand your question a bit more

shamshudheen SSCrazy Points: 2390 More actions · Answer 2

i want to create a view inside my stored procedure.if i give create view inside my stored procedure.it does not accepting

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

this is generally not a good idea. if your procs are altering the schema of your database, you are building on a shaky foundation indeed.

create view fails because it wants to be the first statement of the batch. I think the only way this will work is if you create the view in a dynamic sql call.

better not to do it at all though.

---------------------------------------
elsasoft.org

steveb. SSC-Forever Points: 46733 More actions · Answer 4

You can not do it directly with the CREATE VIEW command, but it can be done by using dynamic SQL

declare @sqlcode varchar(max)

select @sqlcode = 'create view vw_MyView as select * from MyTable'

exec (@sql)

But i would not recommend doing this at all as it is not good practice and alters the structure of your database.

Perhaps using temp tables or table variables would help you achieve your goal?

shamshudheen SSCrazy Points: 2390 More actions · Answer 5

Yes You people has good stuff, you are right. I was checking myself that is there a way which i might not known

Thanks guys