Click here to Skip to main content
13,095,529 members (58,890 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
here below is the procedure
i want to get the data from proceuder in code behind but i just got only one integer value:


ALTER proc [dbo].[usp_tblProperty]
AS
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(propertyName as varchar(55)) + ']',
    '[' + cast(propertyName as varchar(55))+ ']'
  )
FROM tblproperty
 
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT *
  FROM (
    SELECT
     EntityObjectId,tblorganisationentity.OrganisationName,tblproperty.propertyname
	  as propertyname,value
    FROM tblpropertyvalue inner join tblProperty
    on tblpropertyvalue.propertyid = tblproperty.propertyid
    inner join tblorganisationentity 
    on tblorganisationentity.organisationId = tblpropertyvalue.entityobjectid
   
  ) AS PivotData
  PIVOT (
    max(value)
    FOR propertyname IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'
--Print @PivotTableSQL
EXECUTE(@PivotTableSQL)


please help out from this
how i get whole data from procedure

i got return type integer
Posted 18-Apr-12 23:21pm
sharmarun1.7K
Updated 18-Apr-12 23:48pm
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Its Solution is that you can create view of inner query.

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(propertyName as varchar(55)) + ']',
'[' + cast(propertyName as varchar(55))+ ']'
)
FROM tblproperty
print @PivotColumnHeaders
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
create view abc
as
SELECT *
FROM (
SELECT
EntityObjectId,tblorganisationentity.OrganisationName,tblproperty.propertyname as propertyname,value
FROM tblpropertyvalue inner join tblProperty
on tblpropertyvalue.propertyid = tblproperty.propertyid
inner join tblorganisationentity
on tblorganisationentity.organisationId = tblpropertyvalue.entityobjectid
 
) AS PivotData
PIVOT (
max(value)
FOR propertyname IN (
' + @PivotColumnHeaders + '
)
) AS PivotTable
'


Now you can access view as
"select * from abc"
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

You should use  !!!!
 
EXEC SP_EXECUTESQL @PivotTableSQL
 
TRY...........
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.170813.1 | Last Updated 15 May 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100