How to Execute a Stored Procedure with in a View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Execute a Stored Procedure with in a View

Author

Topic

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 00:35:24

Hi Folks!!!
I've a StoreProcedure Named DynamicPIVOT and i wanted Execute this stored Procedure within View..(SQL Server 2005) Can any one please help me to solve this issue..

Vidhu

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-13 : 00:38:51

Inside of VIEW objects you can use just a SELECT statement or CTE.

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 00:46:16

yup...
but.. i think der r also sum methods 2 exec d stored procedure..
Can u plz xplain ur concept in brief...

Vidhu

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 02:30:37

Text-speak is not permitted on this forum.

Please do not double-post. I have deleted your duplicate posts.

You cannot execute a Stored Procedure in a view. You may be able to use a stored procedure with OPENQUERY or OPENROWSET or something of that nature.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-13 : 02:34:49

Why do u want to execute the procedure inside the view...?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 05:03:27

quote:
Originally posted by vidhya.smarty

yup...
but.. i think der r also sum methods 2 exec d stored procedure..
Can u plz xplain ur concept in brief...

Vidhu

This is not a chatting

Please use readable ENGLISH

You can't make use of a view to execute a procedure
Why do you want not to use a procedure?

Madhivanan

Failing to plan is Planning to fail

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 05:56:41

To know why i want use view.. Please go through the Converting Rows into Columns.

Vidhu

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 06:00:18

Here's a link to that post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144506

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 06:06:12

@kristen..
thanks for the posted link

Vidhu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 06:43:48

quote:
Originally posted by vidhya.smarty

To know why i want use view.. Please go through the Converting Rows into Columns.

Vidhu

Ok. You should execute the dynamic query directly and move the result to a table


declare 
	@select varchar(2000),
	@PivotCol varchar(100), 
	@Summaries varchar(100)

declare @pivot varchar(max), @sql varchar(max)

select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
create table #pivot_columns  (pivot_column varchar(100))
Select @sql='select distinct pivot_col from ('+@select+') as t'
insert into #pivot_columns
exec(@sql)
--print @select
select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
	select * into pivot_table from 
	(
		'+@select+'
	) as t 
	pivot  
	(
		'+@Summaries+' for pivot_col in ('+@pivot+')
	) as p
'	
exec(@sql)

Now data are available at the table pivot_table

Madhivanan

Failing to plan is Planning to fail

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 08:37:17

the place where u have mentioned the pivot_table...
it should be temprovary table then 1li itss working...

Vidhu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 08:49:27

quote:
Originally posted by vidhya.smarty

the place where u have mentioned the pivot_table...
it should be temprovary table then 1li itss working...

Vidhu

So, finally you got it working?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources