Click here to Skip to main content
15,879,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to get columns information from stored procedure while importing stored procedure as function import. But I am not getting columns information for complex stored procedure. It contains dynamic query. How can i solve this problem to get select result from stored procedure.

SQL
CREATE PROCEDURE [Directory].[usp_Person_BasicPersonSearch_New]                                                                                                  
(                  
@Flage varchar(20)=null,                                                                                    
@KeywordSearch varchar(50)=NULL,                                                                                   
@PersonName varchar(50)=NULL,                                                                                        
@LastName varchar(50)=NULL,                                                                                         
@PersonType int=NULL,                                                                                         
@Organization varchar(50)=NULL,                
--@PageNum int,                  
--@PageSize int,                                                                                           
@UserId varchar(50)=null,                              
@SearchCriteria varchar(10)=NULL                                                                                
)                                                                                                  
AS                                                                                                  
BEGIN                                                                                        
declare @tab table(value int)                                                                                                          
 declare @Alpha table(alphabets varchar(100))                                                                                           
 BEGIN TRY                                                                  
  SET NOCOUNT ON                                                        
DECLARE @Sql varchar(Max)                                                                                   
SET @Sql=''                               
                              
declare @PersonId int                              
                              
Select @PersonId=PersonId from Directory.Person where UserId=@UserId                              
                 
                                         
if(@SearchCriteria='Default')                              
Begin                              
declare @mainorgtab table (organizationid int)                            
insert into @mainorgtab                            
select distinct OrganizationId from Directory.PersonRoleOrganization where PersonId = @PersonId                        
AND (RoleUnassignDate>GETDATE() or RoleUnassignDate is null)                        
--                            
                            
declare @intFlag int                                  
 SET @intFlag = 1                                  
                                   
 while (@intFlag <=100)                                  
 begin                             
                            
insert into @mainorgtab                
select a.OrganizationId/*,b.organizationid,c.PersonId */from Directory.Organization a, @mainorgtab b /*,Directory.PersonRoleOrganization c*/                            
where a.ParentOrganizationId = b.organizationid  and a.OperatingStatus = 'active'                             
and (OrganizationCloseDate is null or (OrganizationCloseDate > GETDATE()))                            
and a.OrganizationTypeId not in (select OrganizationTypeId from Directory.OrganizationType where OrganizationTypeName = 'class')                            
and Not exists (select 1 from @mainorgtab where organizationid = a.OrganizationId)                            
                            
 if @@ROWCOUNT = 0                                   
 break                                  
                            
 set @intFlag = @intFlag + 1                                  
                                   
 end                             
                            
 ;                            
 select PersonId,FirstName,LastName,PersonTypeName, OrganizationName into #test from (                            
 select distinct p.PersonId,p.FirstName,p.LastName,pt.PersonTypeName, o.OrganizationName                             
 from directory.PersonRoleOrganization pro                          
 inner join Directory.person p on pro.PersonId = p.PersonId                          
 inner join Directory.PersonType pt on pt.PersonTypeId = p.PersonTypeId                          
 inner join Directory.Organization o on pro.OrganizationId = o.OrganizationId           
 inner join Directory.Status s on s.StatusId = p.StatusId and  s.StatusName in('Active','Inactive')                   
 inner join @mainorgtab m on m.organizationid = o.OrganizationId                          
 where                             
              
 --(LTRIM(P.FirstName) LIKE @PersonPagination or  @PersonPagination IS NULL)  AND                         
  (RoleUnassignDate>GETDATE() or RoleUnassignDate is null)--480070                            
 and o.OrganizationTypeId not in (select OrganizationTypeId from Directory.OrganizationType where OrganizationTypeName = 'class')                       
 and (o.OrganizationCloseDate is null or (o.OrganizationCloseDate > GETDATE()))                            
 AND (P.IsCase=0 OR P.IsCase IS NULL)                             
 )a                            
                      
 select * from (                            
 select ROW_NUMBER() over (order by firstname) rownum,personid,FirstName,LastName,PersonTypeName,                            
 STUFF((select ', '+organizationname from #test c1 where c1.PersonId = #test.PersonId for xml path('')),1,1,'') Organization                            
 from #test group by personid,FirstName,LastName,PersonTypeName                            
 )a                         
 --where rownum between (@PageNum - 1) * @PageSize+ 1 AND @PageNum * @PageSize       
 order by 1                 
                         
                    
 drop table #test                              
                      
                              
End                              
Else                                 
Begin                                      
                         
   if(@Flage='Search')                                                                                      
   BEGIN                                                                   
 SET @Sql='                                                                                        
 SELECT PersonId,FirstName,LastName,LTRIM(RTRIM(PersonTypeName))[PersonTypeName],LTRIM(RTRIM(Organization)) [Organization]                                                                                      
 FROM (                                                                                        
 SELECT t.PersonId,t.FirstName,t.LastName,t.PersonTypeName,t.Organization                                                                                                
 FROM                                                                                           
 (                                                                                                
 SELECT distinct P.PersonId,                                             
 P.FirstName[FirstName]                                            
 ,P.LastName[LastName]                                                                                         
 ,stuff(                                                                           
 (                                                                                          
 select '', ''+ LPT.PersonTypeName                                                                         
 from (select distinct PersonId,PersonTypeId from  Directory.PersonInformationDomain)  LPID                                              
 inner join Directory.PersonType LPT on LPID.PersonTypeId=LPT.PersonTypeId                                                   
 where LPID.PersonId = P.PersonId for XML path('''')                                                                                          
 ),1,1,'''') as PersonTypeName                                                                 
                                                                                       
 ,stuff(                                                                                          
 (                                                                                          
 select '', ''+ LO.OrganizationName                                                                                           
 from (select distinct PersonId,OrganizationId,RoleUnassignDate from  Directory.PersonRoleOrganization)  LPID                                                                                
 inner join Directory.Organization LO on LPID.OrganizationId=LO.OrganizationId                                                                     
 AND (CONVERT(date,LPID.RoleUnassignDate) > convert(date, getdate()) or LPID.RoleUnassignDate is null)                                                                      
 where LPID.PersonId = P.PersonId for XML path('''')                                                                                          
 ),1,1,'''') as Organization                                                                                          
                                                                                               
 FROM Directory.Person P                                                                 
 LEFT OUTER JOIN Directory.PersonInformationDomain PID ON PID.PersonId=P.PersonId                                                                          
 LEFT OUTER JOIN Directory.PersonRoleOrganization PRO ON PRO.PersonId=P.PersonId                                           
 AND (CONVERT(date,PRO.RoleUnassignDate) > convert(date, getdate()) or PRO.RoleUnassignDate is null)                                                                                                             
 LEFT JOIN Directory.PersonType PT ON PID.PersonTypeId=PT.PersonTypeId                                                                                              
 LEFT JOIN Directory.Organization O ON O.OrganizationId=PRO.OrganizationId                                      
 INNER JOIN Directory.Status S ON P.StatusId=S.StatusId and  S.StatusName in(''Active'',''Inactive'')                                                                                              
 WHERE 1=1 AND (P.IsCase=0 OR P.IsCase IS NULL)'                                              
                                                      
 IF(@PersonName IS NOT NULL)                                                          
  SET @Sql=@Sql + ' AND LTRIM(P.FirstName) LIKE '''+@PersonName+''''                                                     
                                                                                     
 IF (@LastName IS NOT NULL)                                                                                                
  SET @Sql=@Sql + ' AND LTRIM(P.LastName) LIKE '''+@LastName+''''                                                                                      
                                                                                     
 IF(@PersonType IS NOT NULL)                                                                                         
  SET @Sql=@Sql + ' AND PID.PersonTypeId='+ CAST(@PersonType as Varchar)+''                                                                                           
                                                                                     
 IF(@Organization IS NOT NULL)                                                                                         
  SET @Sql=@Sql + ' AND LTRIM(O.OrganizationName) like '''+@Organization+''''                                                    
                                           
                       
 SET @Sql= @Sql +')t )tt'                                                   
 --WHERE  tt.RowNumber BETWEEN ('+ CAST(@PageNum as VARCHAR) +'- 1) * '+ CAST(@PageSize as VARCHAR)+' + 1 AND '+ CAST(@PageNum as VARCHAR)+' * '+CAST(@PageSize as VARCHAR)+''                  
 PRINT @Sql                                                                              
 EXEC (@Sql)              
                                                                          
   END                                                   
                                                                                   
Else if(@Flage='KeyWordSearch')                                                                                    
   Begin                                                                                            
 SET @Sql='SELECT PersonId,FirstName,LastName,LTRIM(RTRIM(PersonTypeName))[PersonTypeName],LTRIM(RTRIM(Organization)) [Organization]                  
 FROM (                                                               
 SELECT t.PersonId,t.FirstName,t.LastName,t.PersonTypeName,t.Organization                                                                                                              
 FROM (                                                                                                
 SELECT distinct P.PersonId,                                             
  P.FirstName[FirstName]         
,P.LastName[LastName]                                                                                          
                                                                                          
 ,stuff(                                                                      
 (                                                                    
 select '', ''+ LPT.PersonTypeName                                          
 from (select distinct PersonId,PersonTypeId from  Directory.PersonInformationDomain)  LPID                                                                                          
 inner join Directory.PersonType LPT on LPID.PersonTypeId=LPT.PersonTypeId                                                         
 where LPID.PersonId = P.PersonId for XML path('''')                 
 ),1,1,'''') as PersonTypeName                                                                                            
                                     
 ,stuff(                                                                                          
 (           
 select '', ''+ LO.OrganizationName                                                    
 from (select distinct PersonId,OrganizationId,RoleUnassignDate from  Directory.PersonRoleOrganization)  LPID                                                                                          
 inner join Directory.Organization LO on LPID.OrganizationId=LO.OrganizationId                                                                     
 AND (CONVERT(date,LPID.RoleUnassignDate) > convert(date, getdate()) or LPID.RoleUnassignDate is null)                                                                                          
 where LPID.PersonId = P.PersonId for XML path('''')                                                                                          
 ),1,1,'''') as Organization                                                                  
                                                                                               
 FROM Directory.Person P                                                                                               
 LEFT OUTER JOIN Directory.PersonInformationDomain PID ON PID.PersonId=P.PersonId                                      
 LEFT OUTER JOIN Directory.PersonRoleOrganization PRO ON PRO.PersonId=P.PersonId                                         
 AND (CONVERT(date,PRO.RoleUnassignDate) > convert(date, getdate()) or PRO.RoleUnassignDate is null)                                                                                            
 LEFT JOIN Directory.PersonType PT ON PID.PersonTypeId=PT.PersonTypeId                                                                                              
 LEFT JOIN Directory.Organization O ON O.OrganizationId=PRO.OrganizationId                                                                           
 INNER JOIN Directory.Status S ON P.StatusId=S.StatusId AND s.StatusName in(''Active'',''Inactive'')                                                                                                   
 WHERE 1!=1 AND (P.IsCase=0 OR P.IsCase IS NULL)  '                                               
                                                 
  IF(@KeywordSearch IS NOT NULL)                                                                             
  SET @Sql=@Sql                                             
  + ' OR LTRIM(P.FirstName) LIKE '''+@KeywordSearch+'''' + ' OR LTRIM(P.MiddleName) LIKE '''+@KeywordSearch+''''+ ' OR LTRIM(P.PreferredFirstName) LIKE '''+@KeywordSearch+''''           
  + ' OR P.UserId LIKE '''+@KeywordSearch+'''' + ' OR LTRIM(P.LastName) LIKE '''+@KeywordSearch+'''' + ' OR LTRIM(PT.PersonTypeName) LIKE '''+@KeywordSearch+''''                                            
  + ' OR LTRIM(O.OrganizationName) LIKE '''+@KeywordSearch+''''                                            
                                              
 SET @Sql= @Sql +')t )tt'                                                      
                                                                               
 EXEC (@Sql)                                                                   
  Print (@Sql)                                                                  
              
   end                                
   End                               
                                                                
 END TRY                                                                                      
 BEGIN CATCH                                                                                                  
                                                                                        
 END CATCH      
 SET NOCOUNT OFF                                                                                          
END   


Anybody help me.
Posted
Comments
ZurdoDev 5-Aug-13 12:22pm    
This is not clear. Perhaps you can explain better or with some sort of diagram for what you are hoping.

1 solution

You won't be able to get column information from the stored procedure because of the dynamic SQL. What you can do is store your results of the dynamic SQL in a temp or variable table and make your select from that table as the last statement.

If the issue is due to columns dynamically being added to the SELECT statement in the dynamic SQL, then there is nothing you will be able to do.
 
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