stored procedure from view

Question

I have created a stored procedure this contains a dynamic pivot. 

 create Procedure [dbo].[Usp_GetPivotTable]
As
Declare @PivotCols Varchar(2000)    
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ country +'],' from t2 for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot    
exec('Select * From t2 as tmp pivot (MIN(country) for  country in ('+@PivotCols+')) as tbl')

Now, I want to create a view. When am creating the below view, am getting the error...

create view [dbo].[ViewTest]
as
select * from openquery(local, 'Usp_GetPivotTable')a

ERROR:

Msg 537, Level 16, State 2, Procedure Usp_GetPivotTable, Line 6
Invalid length parameter passed to the LEFT or SUBSTRING function.

Help required pls

Answer 1

do you get any result when you run the sp alone?

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post

Answer 2

do you get any result when you run the sp alone?

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post

Yes. I am getting result when I ran stored procedure. What is ur email id?

Answer 3

Yes. I am getting result when I ran stored procedure. What is ur email id?

Answer 4

It is working when I put this stored procedure 'sp_who' but when I put my own stored procedure 'Usp_GetPivotTable', am getting error - 

ERROR:

Msg 537, Level 16, State 2, Procedure Usp_GetPivotTable, Line 6
Invalid length parameter passed to the LEFT or SUBSTRING function.

Answer 5

You can't execute an SP from within a view. Under normal circumstances you'd be able to load the SP results into a temp table and use the temp table in place of the view... but... since the SP is using a dynamic pivot, you won't know what columns to define.

---

Jason Long