Subquery returned more than 1 value. This is not permitted when the subquery follows

[kiran2nirvan]

hi please help in solving this

i am recieving this error"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

for the code



CREATE procedure [dbo].[ADI_spfrmGetLog Entries]
(@frmMasterID int,
@startDate datetime,
@endDate datetime,
@startPos int,
@rowCnt int,
@snapShotDate datetime,
@pagingByDate bit=0,
@ttlCnt int=0 OUTPUT
)
as
begin
SET NOCOUNT ON

--THIS IS USED FOR RESTRICTING NEW RECORDS FROM BEING INCLUDED
--DURING PAGING
if @snapShotDate is null set @snapShotDate=g etdate()

--TABLE TO STORE LOG ENTRIES
CREATE TABLE #TAB
(logRank int identity(1,1),
frmLogEntryID int,
frmRevID int,
dateCreated datetime)

INSERT INTO #TAB
SELECT
l.frmLogEntryID ,
l.frmRevID,
l.dateCreated
FROM
frmRevision r
inner JOIN frmLogEntry l
on r.frmRevID = l.frmRevID
and
(
(@startDate is not null and convert(varchar ,l.dateCreated, 101) >=@startDate)
or
(@startDate is null)
)
and
(
(@endDate is not null and convert(varchar ,l.dateCreated, 101) <= @endDate)
or
(@endDate is null)
)
and convert(varchar ,l.dateCreated, 101) <= @snapShotDate
WHERE r.frmMasterID = @frmMasterID
ORDER BY l.frmlogEntryID desc

--IF PAGING BY EACH DAY, NEED TO GET A LIST OF DISTINCT DATES.
if @pagingByDate =1
BEGIN
DECLARE @dateTab TABLE
(logRank int identity(1,1),
dateCreated datetime)

INSERT INTO @dateTab
SELECT
distinct convert(varchar ,dateCreated,10 1)
FROM #TAB
ORDER BY convert(varchar ,dateCreated,10 1) desc

SELECT
a.frmAttrDispla yName,
a.frmRevID,
l.frmLogEntryID ,
l.dateCreated,
frmAttrValue = CASE
WHEN a.frmAttrName like '%country%'
THEN
ISNULL((select country
from dbo.country
where abbreviation = ld.frmAttrValue ),ld.frmAttrVal ue)
WHEN a.frmAttrName like '%state%'
THEN
ISNULL((select full_name
from dbo.state
where state = ld.frmAttrValue and (StateCountry is NULL or StateCountry <> 'MX')), ld.frmAttrValue )
ELSE ld.frmAttrValue
END,
a.frmAttrID
FROM
#TAB as l
inner join @dateTab as dt
on l.dateCreated >=dt.dateCreate d
and l.dateCreated < dateadd(d,1,dt. dateCreated)
inner join frmAttribute a
on l.frmRevID = a.frmRevID
inner join frmLogEntryDeta il ld
on l.frmLogEntryID =ld.frmLogEntry ID
and a.frmAttrID = ld.frmAttrID
WHERE
dt.logRank = @startPos
Order by a.frmAttrID

if @startPos=1
BEGIN
/*SELECT @ttlCnt = MAX(logRank) from @dateTab*/
SELECT @ttlCnt = MAX(logRank) from #Tab
END

END
ELSE IF @rowCnt=0 and @pagingByDate = 0
BEGIN
SELECT
a.frmAttrDispla yName,
a.frmRevID,
l.frmLogEntryID ,
l.dateCreated,
frmAttrValue = CASE
WHEN a.frmAttrName like '%country%'
THEN
ISNULL((select country
from dbo.country
where abbreviation = ld.frmAttrValue ),ld.frmAttrVal ue)
WHEN a.frmAttrName like '%state%'
THEN
ISNULL((select full_name
from dbo.state
where state = ld.frmAttrValue and (StateCountry is NULL or StateCountry <> 'MX')), ld.frmAttrValue )
ELSE ld.frmAttrValue
END,
a.frmAttrID
FROM
#TAB as l
inner join frmAttribute a
on l.frmRevID = a.frmRevID
inner join frmLogEntryDeta il ld
on l.frmLogEntryID =ld.frmLogEntry ID
and a.frmAttrID = ld.frmAttrID
Order by a.frmAttrID
END
ELSE
BEGIN
DECLARE @endPos INT

SET @endPos = @startPos + @rowCnt
SELECT
a.frmAttrDispla yName,
a.frmRevID,
l.frmLogEntryID ,
l.dateCreated,
frmAttrValue = CASE
WHEN a.frmAttrName like '%country%'
THEN
ISNULL((select country
from dbo.country
where abbreviation = ld.frmAttrValue ),ld.frmAttrVal ue)
WHEN a.frmAttrName like '%state%'
THEN
ISNULL((select full_name
from dbo.state
where state = ld.frmAttrValue and (StateCountry is NULL or StateCountry <> 'MX')), ld.frmAttrValue )
ELSE ld.frmAttrValue
END,
a.frmAttrID
FROM
#TAB as l
inner join frmAttribute a
on l.frmRevID = a.frmRevID
inner join frmLogEntryDeta il ld
on l.frmLogEntryID =ld.frmLogEntry ID
and a.frmAttrID = ld.frmAttrID
WHERE
logRank >= @startPos
and logRank <@endPos
Order by a.frmAttrID

if @startPos=1
BEGIN
SELECT @ttlCnt = MAX(logRank) from #TAB
END
END
DROP TABLE #TAB
SET NOCOUNT OFF

end

GO