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.
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,
@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.OrganizationIdfrom Directory.Organization a, @mainorgtab b
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
(RoleUnassignDate>GETDATE() or RoleUnassignDate is null)
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
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'
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.
Submit an article or tip