hi,
Its worked for me......
I dint get any error........
Execute the following code as it is....
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[Test_GetOrderTable] (@string varchar(max))
returns @tblOrder Table(id int identity(1,1),strings varchar(100))
AS
begin
declare @LocalVar varchar(100)
declare @pos int
set @pos=1
set @LocalVar=''
set @string = ltrim(rtrim(@string))
while (@pos <=len(@string))
begin
if (substring(@string, @pos,1)!=',')
begin
set @LocalVar =@LocalVar + substring(@string,@pos,1)
set @pos=@pos+1
end
else
begin
if @LocalVar <> ''
insert into @tblOrder values(ltrim(rtrim(@LocalVar)))
set @pos= @pos+ 1
set @LocalVar=''
end
end
if @LocalVar <> ''
insert into @tblOrder values(ltrim(rtrim(@LocalVar)))
return
end
GO
Run the following SP as it is....now test it...it should work.....
CREATE PROC sp_UserPerformance (@rid VACHAR(MAX),@fromdate datetime,@todate datetime,@locid bigint)
AS
BEGIN
Select UserName as 'User Name'
,isnull(( Select Sum(HC_REQ_TEAM.Position)
From HC_REQ_TEAM WITH(NOLOCK)
WHERE HC_REQ_TEAM.TeamUserID=HC_USERS.RID
AND HC_REQ_TEAM.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (16,15,13,14 ))
and HC_REQ_TEAM.ReqID in(Select ReqID
From HC_REQ_RESUME,HC_REQ_RESUME_STATUS WITH(NOLOCK)
WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)
and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID ) ),'') as Openings
From HC_USERS WITH(NOLOCK)
WHERE HC_USERS.RID in (SELECT Strings from Test_GetOrderTable(@rid))
AND HC_USERS.Locationid in (@locid)
Group By HC_USERS.RID,HC_USERS.UserName
END
Cheers!
Sasidhar Chowdary