Click here to Skip to main content
15,867,290 members
Please Sign up or sign in to vote.
3.00/5 (3 votes)
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
Updated 18-Apr-12 23:48pm
v2

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

SQL
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
SQL
"select * from abc"
 
Share this answer
 
v2
SQL
You should use  !!!!

EXEC SP_EXECUTESQL @PivotTableSQL

TRY...........
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900