SQL Case wih Multiple Return Values

Ask a question

Monday, February 04, 2013 9:41 AM

0

Please find the below Query which has case with multiple return value. I’m getting the following error: “Msg 512, Level 16, State 1, Line 4

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”. I need a solution for this without using dynamic query.

1. The below Query is working fine but only for the Top 1 Record:

DECLARE @USERID NVARCHAR(MAX)

SET @USERID='1' //Paramter may be integer value or NULL.

SELECT * FROM USERDETAILS

WHERE REF_USER_ID

      IN (CASE @USERID

            WHEN '1' THEN (SELECT TOP(1) REF_USER_ID FROM USERDETAILS where Ref_Gender_ID in (1) )

            ELSE REF_USER_ID END)

2. When I remove “TOP (1)” from sub-query, it returns more than one value and at that time I am getting the following error: “Msg 512, Level 16, State 1, Line 4

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

DECLARE @USERID NVARCHAR(MAX)

SET @USERID='1'

SELECT * FROM USERDETAILS

WHERE REF_USER_ID

      IN (CASE @USERID

            WHEN '1' THEN (SELECT REF_USER_ID FROM USERDETAILS where Ref_Gender_ID in (1) )

            ELSE REF_USER_ID END)
- Reply
- Quote

All Replies

Monday, February 04, 2013 9:44 AM

0
UserDetails Table Schema:

GO

/****** Object: Table [dbo].[UserDetails]    Script Date: 02/04/2013 13:04:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[UserDetails](

      [Ref_User_ID] [int] IDENTITY(1,1) NOT NULL,

      [User_Salutation] [nvarchar](max) NULL,

      [User_Code] [nvarchar](max) NULL,

      [User_FirstName] [nvarchar](max) NULL,

      [User_MiddleName] [nvarchar](max) NULL,

      [User_SurName] [nvarchar](max) NULL,

      [User_Password] [nvarchar](max) NULL,

      [User_Password_Setdate] [datetime] NULL,

      [Ref_Grade_ID] [int] NULL,

      [Ref_Gender_ID] [int] NULL,

      [Ref_CostCenter_ID] [int] NULL,

      [User_Type] [nvarchar](max) NULL,

      [Ref_Desig_ID] [int] NULL,

      [User_Email] [nvarchar](max) NULL,

      [User_AlternateEmail] [nvarchar](max) NULL,

      [Ref_City_ID] [int] NULL,

      [Ref_Dept_ID] [int] NULL,

      [Ref_UserType_ID] [int] NOT NULL,

      [Ref_TMSRole_ID] [int] NULL,

      [Screen_Name] [nvarchar](max) NULL,

      [User_Date_Of_Joining] [datetime] NULL,

      [Ref_Authority_ID] [int] NULL,

      [User_Id_Reporting_Authority] [int] NULL,

      [User_Birthdate] [datetime] NULL,

      [User_Photo] [nvarchar](max) NULL,

      [User_Active] [int] NULL,

      [User_LoginLock] [int] NULL,

      [User_PhoneNumber] [nvarchar](50) NULL,

      [Ref_UserGroup_ID] [int] NULL,

      [User_Address1] [nvarchar](max) NULL,

      [User_CourseGroup_IDs] [nvarchar](max) NULL,

      [User_Address2] [nvarchar](max) NULL,

      [User_Address3] [nvarchar](max) NULL,

      [User_MobileNumber] [nvarchar](50) NULL,

      [CREATED_BY] [nvarchar](max) NULL,

      [CREATED_DATETIME] [datetime] NOT NULL,

      [LAST_UPDATED_BY] [nvarchar](max) NULL,

      [LAST_UPDATED_DATETIME] [datetime] NULL,

      [ACTIVE_FLAG] [bit] NULL,

      [Ref_Domain_ID] [int] NOT NULL,

      [SecurityQuestion] [nvarchar](max) NULL,

      [SecurityQuestionAnswer] [nvarchar](max) NULL,

CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED

(

      [Ref_User_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[UserDetails] ON

INSERT [dbo].[UserDetails] ([Ref_User_ID], [User_Salutation], [User_Code], [User_FirstName], [User_MiddleName], [User_SurName], [User_Password], [User_Password_Setdate], [Ref_Grade_ID], [Ref_Gender_ID], [Ref_CostCenter_ID], [User_Type], [Ref_Desig_ID], [User_Email], [User_AlternateEmail], [Ref_City_ID], [Ref_Dept_ID], [Ref_UserType_ID], [Ref_TMSRole_ID], [Screen_Name], [User_Date_Of_Joining], [Ref_Authority_ID], [User_Id_Reporting_Authority], [User_Birthdate], [User_Photo], [User_Active], [User_LoginLock], [User_PhoneNumber], [Ref_UserGroup_ID], [User_Address1], [User_CourseGroup_IDs], [User_Address2], [User_Address3], [User_MobileNumber], [CREATED_BY], [CREATED_DATETIME], [LAST_UPDATED_BY], [LAST_UPDATED_DATETIME], [ACTIVE_FLAG], [Ref_Domain_ID], [SecurityQuestion], [SecurityQuestionAnswer]) VALUES (137, N'1', N'test', N'Zobble', N'', N'Solution', N'0', CAST(0x0000A14E0140FF19 AS DateTime), 0, 1, 0, N'Admin', 19, N'harshita@gmail.com', N'', 1, 72, 1, 1, N'', CAST(0x00009F5B00000000 AS DateTime), 152, 146, CAST(0x00007F9600000000 AS DateTime), NULL, 1, NULL, N'', NULL, N'Dadar', NULL, NULL, NULL, N'9826667472', N'Admin Admin', CAST(0x0000A14E0140FF19 AS DateTime), N'Admin Admin', CAST(0x0000A14E0140FF19 AS DateTime), 1, 1, N'', N'')

INSERT [dbo].[UserDetails] ([Ref_User_ID], [User_Salutation], [User_Code], [User_FirstName], [User_MiddleName], [User_SurName], [User_Password], [User_Password_Setdate], [Ref_Grade_ID], [Ref_Gender_ID], [Ref_CostCenter_ID], [User_Type], [Ref_Desig_ID], [User_Email], [User_AlternateEmail], [Ref_City_ID], [Ref_Dept_ID], [Ref_UserType_ID], [Ref_TMSRole_ID], [Screen_Name], [User_Date_Of_Joining], [Ref_Authority_ID], [User_Id_Reporting_Authority], [User_Birthdate], [User_Photo], [User_Active], [User_LoginLock], [User_PhoneNumber], [Ref_UserGroup_ID], [User_Address1], [User_CourseGroup_IDs], [User_Address2], [User_Address3], [User_MobileNumber], [CREATED_BY], [CREATED_DATETIME], [LAST_UPDATED_BY], [LAST_UPDATED_DATETIME], [ACTIVE_FLAG], [Ref_Domain_ID], [SecurityQuestion], [SecurityQuestionAnswer]) VALUES (141, N'3', N'Amol', N'Anmol', N'', N'Mandowara', N'100', CAST(0x0000A14E01328C8C AS DateTime), 0, 2, 0, N'Admin', 0, N'amol@gmail.com', N'', 1, 36, 1, 2, N'Amol', CAST(0x0000A00D00000000 AS DateTime), 152, 146, CAST(0x000080BE00000000 AS DateTime), NULL, 1, NULL, N'', NULL, N'Neemuch', NULL, NULL, NULL, N'5295461654', N'Admin Admin', CAST(0x0000A14E01328C8C AS DateTime), N'Admin Admin', CAST(0x0000A14E01328C8C AS DateTime), 1, 2, N'', N'')

INSERT [dbo].[UserDetails] ([Ref_User_ID], [User_Salutation], [User_Code], [User_FirstName], [User_MiddleName], [User_SurName], [User_Password], [User_Password_Setdate], [Ref_Grade_ID], [Ref_Gender_ID], [Ref_CostCenter_ID], [User_Type], [Ref_Desig_ID], [User_Email], [User_AlternateEmail], [Ref_City_ID], [Ref_Dept_ID], [Ref_UserType_ID], [Ref_TMSRole_ID], [Screen_Name], [User_Date_Of_Joining], [Ref_Authority_ID], [User_Id_Reporting_Authority], [User_Birthdate], [User_Photo], [User_Active], [User_LoginLock], [User_PhoneNumber], [Ref_UserGroup_ID], [User_Address1], [User_CourseGroup_IDs], [User_Address2], [User_Address3], [User_MobileNumber], [CREATED_BY], [CREATED_DATETIME], [LAST_UPDATED_BY], [LAST_UPDATED_DATETIME], [ACTIVE_FLAG], [Ref_Domain_ID], [SecurityQuestion], [SecurityQuestionAnswer]) VALUES (145, N'1', N'E101', N'Sunil', N'', N'Mengane', N'2', CAST(0x0000A14E012FF696 AS DateTime), 0, 1, 0, N'Admin', 0, N'sunil.mengane@zobble.com', N'', 1, 72, 1, 3, N'', CAST(0x00009FF800000000 AS DateTime), 152, 146, CAST(0x000073BE00000000 AS DateTime), NULL, 1, 0, N'', NULL, N'Jogeshwari', NULL, NULL, NULL, N'1111111111', N'Admin Admin', CAST(0x0000A14E012FF696 AS DateTime), N'Admin Admin', CAST(0x0000A14E012FF696 AS DateTime), 1, 3, N'', N'')

INSERT [dbo].[UserDetails] ([Ref_User_ID], [User_Salutation], [User_Code], [User_FirstName], [User_MiddleName], [User_SurName], [User_Password], [User_Password_Setdate], [Ref_Grade_ID], [Ref_Gender_ID], [Ref_CostCenter_ID], [User_Type], [Ref_Desig_ID], [User_Email], [User_AlternateEmail], [Ref_City_ID], [Ref_Dept_ID], [Ref_UserType_ID], [Ref_TMSRole_ID], [Screen_Name], [User_Date_Of_Joining], [Ref_Authority_ID], [User_Id_Reporting_Authority], [User_Birthdate], [User_Photo], [User_Active], [User_LoginLock], [User_PhoneNumber], [Ref_UserGroup_ID], [User_Address1], [User_CourseGroup_IDs], [User_Address2], [User_Address3], [User_MobileNumber], [CREATED_BY], [CREATED_DATETIME], [LAST_UPDATED_BY], [LAST_UPDATED_DATETIME], [ACTIVE_FLAG], [Ref_Domain_ID], [SecurityQuestion], [SecurityQuestionAnswer]) VALUES (146, N'3', N'mahesh', N'Admin', N'', N'Admin', N'1', CAST(0x0000A10900C5D430 AS DateTime), 4, 1, 42, N'Admin', 0, N'mahesh.s@zobble.com', N'', 1, 169, 1, 4, N'', CAST(0x0000A00D00F429F1 AS DateTime), 152, 146, NULL, NULL, 1, 0, N'', 245, N'malad', N'', NULL, NULL, N'1234567890', N'User', CAST(0x0000A10900C5D430 AS DateTime), N'User', CAST(0x0000A10900C5D430 AS DateTime), 1, 4, NULL, NULL)

INSERT [dbo].[UserDetails] ([Ref_User_ID], [User_Salutation], [User_Code], [User_FirstName], [User_MiddleName], [User_SurName], [User_Password], [User_Password_Setdate], [Ref_Grade_ID], [Ref_Gender_ID], [Ref_CostCenter_ID], [User_Type], [Ref_Desig_ID], [User_Email], [User_AlternateEmail], [Ref_City_ID], [Ref_Dept_ID], [Ref_UserType_ID], [Ref_TMSRole_ID], [Screen_Name], [User_Date_Of_Joining], [Ref_Authority_ID], [User_Id_Reporting_Authority], [User_Birthdate], [User_Photo], [User_Active], [User_LoginLock], [User_PhoneNumber], [Ref_UserGroup_ID], [User_Address1], [User_CourseGroup_IDs], [User_Address2], [User_Address3], [User_MobileNumber], [CREATED_BY], [CREATED_DATETIME], [LAST_UPDATED_BY], [LAST_UPDATED_DATETIME], [ACTIVE_FLAG], [Ref_Domain_ID], [SecurityQuestion], [SecurityQuestionAnswer]) VALUES (147, N'1', N'ArunAnant', N'Hindu', N'', N'Gyaan', N'Arun@123', CAST(0x0000A14E012FC518 AS DateTime), 0, 1, 0, N'Admin', 0, N'arun.anant@incvalue.com', N'', 1, 227, 3, 5, N'', CAST(0x0000A02900000000 AS DateTime), 152, 146, CAST(0x0000734900000000 AS DateTime), NULL, 1, 0, N'', NULL, N'Mumbai', NULL, NULL, NULL, N'9876543210', N'Admin Admin', CAST(0x0000A14E012FC518 AS DateTime), N'Admin Admin', CAST(0x0000A14E012FC518 AS DateTime), 1, 5, N'', N'')

INSERT [dbo].[UserDetails] ([Ref_User_ID], [User_Salutation], [User_Code], [User_FirstName], [User_MiddleName], [User_SurName], [User_Password], [User_Password_Setdate], [Ref_Grade_ID], [Ref_Gender_ID], [Ref_CostCenter_ID], [User_Type], [Ref_Desig_ID], [User_Email], [User_AlternateEmail], [Ref_City_ID], [Ref_Dept_ID], [Ref_UserType_ID], [Ref_TMSRole_ID], [Screen_Name], [User_Date_Of_Joining], [Ref_Authority_ID], [User_Id_Reporting_Authority], [User_Birthdate], [User_Photo], [User_Active], [User_LoginLock], [User_PhoneNumber], [Ref_UserGroup_ID], [User_Address1], [User_CourseGroup_IDs], [User_Address2], [User_Address3], [User_MobileNumber], [CREATED_BY], [CREATED_DATETIME], [LAST_UPDATED_BY], [LAST_UPDATED_DATETIME], [ACTIVE_FLAG], [Ref_Domain_ID], [SecurityQuestion], [SecurityQuestionAnswer]) VALUES (246, N'2', N'ahmer', N'gfhfh', N'fghgfhgf', N'gfhgf', N'jhjkj', CAST(0x0000A15800CC9D83 AS DateTime), 0, 1, 0, N'Admin', 173, N'ghf@dffff.com', N'', 1, 72, 2, NULL, N'', CAST(0x0000A15800CC9D83 AS DateTime), 152, NULL, NULL, NULL, 0, 0, N'', NULL, N'tryrytr', NULL, NULL, NULL, N'', N'Anmol Mandowara', CAST(0x0000A15800CC9D83 AS DateTime), N'Anmol Mandowara', CAST(0x0000A15800CC9D83 AS DateTime), 1, 1, N'', N'')

INSERT [dbo].[UserDetails] ([Ref_User_ID], [User_Salutation], [User_Code], [User_FirstName], [User_MiddleName], [User_SurName], [User_Password], [User_Password_Setdate], [Ref_Grade_ID], [Ref_Gender_ID], [Ref_CostCenter_ID], [User_Type], [Ref_Desig_ID], [User_Email], [User_AlternateEmail], [Ref_City_ID], [Ref_Dept_ID], [Ref_UserType_ID], [Ref_TMSRole_ID], [Screen_Name], [User_Date_Of_Joining], [Ref_Authority_ID], [User_Id_Reporting_Authority], [User_Birthdate], [User_Photo], [User_Active], [User_LoginLock], [User_PhoneNumber], [Ref_UserGroup_ID], [User_Address1], [User_CourseGroup_IDs], [User_Address2], [User_Address3], [User_MobileNumber], [CREATED_BY], [CREATED_DATETIME], [LAST_UPDATED_BY], [LAST_UPDATED_DATETIME], [ACTIVE_FLAG], [Ref_Domain_ID], [SecurityQuestion], [SecurityQuestionAnswer]) VALUES (247, N'3', N'YU123', N'xxx', N'', N'xxx', N'1', CAST(0x0000A159011F8502 AS DateTime), 0, 1, 0, N'User', 0, N'hfghgh@gmail.com', N'', 1, 0, 1, NULL, N'', CAST(0x0000A159011F8502 AS DateTime), NULL, NULL, NULL, NULL, 1, 0, N'', NULL, N'tt', NULL, NULL, NULL, N'', N'Admin Admin', CAST(0x0000A159011F8502 AS DateTime), N'Admin Admin', CAST(0x0000A159011F8502 AS DateTime), 1, 1, N'', N'')

SET IDENTITY_INSERT [dbo].[UserDetails] OFF

/****** Object: Default [DF_UserDetails_Ref_Dept_ID]    Script Date: 02/04/2013 13:04:03 ******/
- Edited by Mahesh Sellamuthu Monday, February 04, 2013 9:45 AM
- Reply
- Quote
Monday, February 04, 2013 9:49 AM

0

Try

DECLARE @USERID NVARCHAR(MAX)

SET @USERID='1'

SELECT * FROM USERDETAILS

WHERE @USERID='1'
and REF_USER_ID

IN (SELECT REF_USER_ID FROM USERDETAILS where Ref_Gender_ID in (1));
Many Thanks & Best Regards, Hua Min
- Reply
- Quote
Monday, February 04, 2013 9:49 AM

0
Check this

DECLARE @USERID NVARCHAR(MAX)

SET @USERID='1'
SELECT * FROM [#UserDetails] T1

WHERE T1.REF_USER_ID

IN (CASE @USERID

WHEN '1' THEN (SELECT REF_USER_ID FROM [#UserDetails] T2 where T2.Ref_Gender_ID in (1) And T2.REF_USER_ID = T1.REF_USER_ID )

ELSE T1.REF_USER_ID END)

Please have look on the comment
- Edited by SanthoshH Monday, February 04, 2013 9:50 AM
- Reply
- Quote

Monday, February 04, 2013 9:50 AM

Moderator

What is @@version?

I changed it over to Production.Product and it works in SQL Server 2012.

DECLARE @USERID NVARCHAR(MAX)
 
SET @USERID='1' --Paramter may be integer value or NULL.
 

SELECT * FROM Production.Product

WHERE ProductID 

      IN (CASE @USERID 

            WHEN '1' THEN (SELECT TOP(1) ProductID FROM Production.Product where Color in ('Blue') )
 
            ELSE ProductID END)
/*
ProductID	Name	ProductNumber
711	Sport-100 Helmet, Blue	HL-U509-B
*/

Kalman Toth SQL 2008 GRAND SLAM
Paperback / Kindle: SQL Server 2012 Pro

Monday, February 04, 2013 9:57 AM

0
CASE is an expression so it has to evalute to a single value (which i think is relevant from the ASSERT operator when you see the execution plan).

Suggested reading : http://blogs.msdn.com/b/craigfr/archive/2006/08/23/715306.aspx

May be you can try like this :

DECLARE @USERID NVARCHAR(MAX)

SET @USERID='1'

SELECT * FROM USERDETAILS

WHERE REF_USER_ID

IN (SELECT REF_USER_ID FROM USERDETAILS where Ref_Gender_ID in (1) AND @UserID = 1) OR (1=1 AND @userID <> 1)

Thanks and regards, Rishabh K
- Edited by Rishabh K Monday, February 04, 2013 10:01 AM
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, February 05, 2013 2:16 AM
- Marked As Answer by Iric WenModerator Monday, February 18, 2013 2:12 AM
- Reply
- Quote
Monday, February 04, 2013 10:07 AM

0

Please provide ur desired output.
- Reply
- Quote
Monday, February 04, 2013 11:24 AM

0
Try
```
DECLARE @USERID NVARCHAR(MAX)
SET @USERID='1' //Paramter may be integer value or NULL.
SELECT * FROM USERDETAILS ud1
WHERE @USERID ='1' and Ref_Gender_ID in (1)  
or @USERID is null
or @USERID !='1'
```
Serg

P.S. Noted [Ref_User_ID] is primary key of [USERDETAILS] Why join a table with itself by primary key?

WHERE REF_USER_ID IN (SELECT REF_USER_ID FROM USERDETAILS where Ref_Gender_ID in (1) )

is just

WHERE Ref_Gender_ID in (1)

See corrected query above.
- Edited by SergNL Monday, February 04, 2013 11:39 AM primary key
- Reply
- Quote
Tuesday, February 05, 2013 8:28 AM

0

... OR (1=1 AND @userID <> 1)

@Rishabh,

according to the initial post, parameter @userID can be NULL .
Serg
- Reply
- Quote
Tuesday, February 05, 2013 8:42 AM

0

... OR (1=1 AND @userID <> 1)

@Rishabh,

according to the initial post, parameter @userID can be NULL .

Serg

Correct I didn't noticed that. Also the primary key point is very valid , the one you pointed out....
Thanks and regards, Rishabh K
- Reply
- Quote

SQL Case wih Multiple Return Values

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?