CODEWITHSUNDEEP
mysqlsql-servert-sql
Tomasz Kal
Tomasz Kal

Reputation: 129

T-SQL Subquery returned more than 1 value

IF (SELECT begin_date FROM reservation WHERE id=@id) <=@end_date
AND
(SELECT end_date FROM reservation WHERE id=@id) >= @begin_date
BEGIN
    PRINT 'already reserved'
    RETURN
END

This is IF statement in my stored procedure. I have multiple reservations and I want to check if they don't intersect with the one I want to add. The problem is how to compare multiple values returned by query with variable?

Upvotes: 1

Views: 42

Answers (2)

BICube
BICube

Reputation: 4681

If your logic for "already reserved" is correct based on your query, then this should do what you want 

IF EXISTS (SELECT *
           FROM reservation rsv1
           WHERE rsv1.id=@id
             AND rsv1.begin_date <=@end_date
             AND rsv1.end_date >= @begin_date)
BEGIN
PRINT 'already reserved'
END

Upvotes: 2

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

You should put MIN or MAX on begin_date and end_date. something like this :

IF (SELECT MIN(begin_date) FROM reservation WHERE id=@id) <=@end_date
AND
(SELECT MAX(end_date) FROM reservation WHERE id=@id) >= @begin_date
BEGIN
    PRINT 'already reserved'
    RETURN
END

Upvotes: 0

Related Questions