Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is use

Question

Post reply

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is use

daryl.mclean

SSC Enthusiast

Points: 153
More actions
August 25, 2010 at 10:11 pm

#226220

Hi
i am having issue where i try to update multiple(125) rows from values from a temp table in step 3 of this procedure can anyone help?
i think i may need to include a while do loop, but unsure
CREATE PROCEDURE [dbo].[AutoClockout_sp]
-- Parameters passed
@kioskid INT,
@MaxLoginHrs INT,
@LogOutToHrs INT,
@kiosktransactionid uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
begin transaction
-- STEP 1 - Retrive all null logouts greater than specified time
SELECT at.AttendanceID,NEWID()as kioskTransactionId, '550' as kioskid,
'0000000000000000' as CardserialNo,at.PersonId, '2' as TransactionTypeId,
at.LogInTs, DATEADD(HOUR, @LogOutToHrs, LogInTs) as TransactionTs, '0' as TransactionResultTypeId
into#stagenulllogouts2
FROMAttendance at
WHERELogInLocationId = (SELECT locationId FROM kiosk WHERE kioskid = @kioskid)
AND (
(LogOutTs is NULL AND DateDiff(hour, LogInTs, getdate()) > 16)
OR
(DateDiff(hour, LogInTs, LogOutTs) > 24))
-- STEP 2 - Update the kiosk Transaction Table
begin transaction
INSERT INTO [KioskTransaction]
Select KioskTransactionId,KioskId, CardSerialNo, PersonId, TransactionTypeId, TransactionTs, TransactionResultTypeId
from #stagenulllogouts2
--where PersonId = '58254'
-- STEP 3 Close Attendance Record
begin transaction
update Attendance
Set LogOutTs = (Select TransactionTs from #stagenulllogouts2),
LogOutKioskTransactionId = (Select KioskTransactionId from #stagenulllogouts2)
from AttendanceINNER JOIN
#stagenulllogouts2 ON Attendance.AttendanceId = #stagenulllogouts2.AttendanceId
--- Error is returned

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Subbu S Old Hand Points: 389 More actions · Answer 1

Can you send me the sample datas. And also please send me the script for creating the tables with sample data's.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 2

I will executes With Exists operaot insetead of using =?

The is error is throwing due to subquery subquery will not return multiple rows.but using exists we can return multiple rows.

if u post sample data i will write the query to execute.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

daryl.mclean SSC Enthusiast Points: 153 More actions · Answer 3

Hi

Sample data from ##stagenulllogouts2

AttendanceID kioskTransactionId kioskid CardserialNo PersonId TransactionTypeId LogInTs TransactionTs TransactionResultTypeId

------------------------------------ ------------------------------------ ------- ---------------- ----------- ----------------- ---------------------------------- ---------------------------------- -----------------------

E2FEAF1C-85F6-4B3F-89B9-F4EC28CA0C9F B12E7A4F-747E-497D-B854-FD1304421176 550 0000000000000000 113420 2 2010-07-24 06:03:00.0000000 +10:00 2010-07-24 22:03:00.0000000 +10:00 0

DC6FD47A-47FA-440E-A7E6-A937A3AD9DFB 0BEB801E-4F4A-4974-8736-4175CC089468 550 0000000000000000 70105 2 2010-07-24 06:19:27.0000000 +00:00 2010-07-24 22:19:27.0000000 +00:00 0

EFD38170-E2CD-4BA8-9994-3BFCA30DD0EE F45F87FB-4C29-4763-B183-AD70ABD930E9 550 0000000000000000 59499 2 2010-07-24 06:38:47.0000000 +00:00 2010-07-24 22:38:47.0000000 +00:00 0

C41BA077-CA66-4A3D-A22A-289DC6710B3C AECE319B-7928-4296-B283-E4449B50E9DA 550 0000000000000000 63898 2 2010-07-24 06:39:24.0000000 +00:00 2010-07-24 22:39:24.0000000 +00:00 0

Create table scripts

CREATE TABLE [dbo].[Attendance](

[AttendanceId] [uniqueidentifier] ROWGUIDCOL NOT NULL,

[LogInLocationId] [int] NOT NULL,

[KioskLocationId] [int] NOT NULL,

[PersonId] [int] NOT NULL,

[LogInKioskTransactionId] [uniqueidentifier] NULL,

[LogOutTs] [datetimeoffset](7) NULL,

[LogOutKioskTransactionId] [uniqueidentifier] NULL,

[TimeStamp] [timestamp] NOT NULL,

[LogInTs] [datetimeoffset](7) NOT NULL,

[ReportLogInTs] AS (CONVERT([datetime],[LogInTs],(0))),

[ReportLogOutTs] AS (CONVERT([datetime],[LogOutTs],(0))),

CONSTRAINT [PK_ATTENDANCE] PRIMARY KEY NONCLUSTERED

CREATE TABLE [dbo].[KioskTransaction](

[KioskTransactionId] [uniqueidentifier] NOT NULL,

[KioskId] [int] NOT NULL,

[CardSerialNo] [nvarchar](20) NOT NULL,

[PersonId] [int] NULL,

[TransactionTypeId] [int] NOT NULL,

[TransactionTs] [datetimeoffset](7) NOT NULL,

[TransactionResultTypeId] [int] NOT NULL,

CONSTRAINT [PK_KIOSKTRANSACTION] PRIMARY KEY NONCLUSTERED

Should i be using a cursor here?

Thanks