SQL error subquery returned more than 1 value .. when executing a SP?

[Ringers]

Hi All,

I am trying to automate this in a SSIS package. I can populate the user_groups table with the new groups for the new company. I just need to assign the menu access for new groups in the new company the same as company 1.

user_groups table is many rows. It has a row for each group in each company. The same group e.g 'approver' or 'sysadmin' has a different group ID for each company.

user_group_rights_new has many rows. It controls what menus each group id has. Hence each group_id is repeated with different menu item id.

I am trying to do a conditional insert. A new company is created e.g.'25' the company has new groups added , 'approver', 'sysadmin'. But they don't have any menu access. So that group_id doesn't exist in the user_group_rights_new. Now insert that group_id to with the same menu_items from the matching group 'approver', 'sysadmin' for company 1. Because the menu access doesn't change from company to company only the group_id number for that new group.

Current:

e.g.company_id 4 approver, admin, manager don't have any menu access yet.

Table User_groups

group_id-----Name----company_id

-21---approver------1

-5----admin---------1

-18---manager------1

-23---approver------2
-2----admin---------2
-130--approver-----4

-194--admin--------4

-18---manager-----4

Table user_group_rights_new

groupid------menu_item_id

-21-----------27

-21-----------26

-21-----------25

-21-----------24

-2-----------27

-2-----------26

-2-----------25

-2-----------24

Expected:

Table user_group_rights_new

groupid------menu_item_id

-21-----------27

-21-----------26

-21-----------25

-21-----------24

-21-----------27

-2-----------26

-2-----------25

-2-----------24

-130--------27

-130--------26

-130-------25

-130-------24

Now the approver group for company 4 group 130 'approver' has the same menu access the as the existing 'approver' group from company 1.

Create Procedure Buyer_User_Groups_Menu_Access_sp
@group_id INT = null,
@group_id2 INT = null,
@menu varchar(50) = null

AS

SET @group_id = (select id from user_groups)
SET @group_id2 = (select id from user_groups where user_groups.company_id = '1')
SET @menu = (select menu_item_id from user_group_rights_new where Group_id = @group_id2)

IF NOT Exists (select group_id from user_group_rights_new where Group_id = @group_id)
BEGIN
	INSERT INTO user_group_rights_new
			(Group_id,Menu_item_id)	
			SELECT
			group_id = @group_id,
			menu_item_id = @menu
END

But when I run execute the stored proc i get an error message.


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

 

[bborissov]

These queries

SET @group_id = (select id from user_groups)
SET @group_id2 = (select id from user_groups where user_groups.company_id = '1')
SET @menu = (select menu_item_id from user_group_rights_new where Group_id = @group_id2)

return more than one record. You should use WHERE.

You can't have more than OPNE value in a variable (except if it is TABLE variable).

Borislav Borissov
VFP9 SP2, SQL Server

 

[Ringers]

Thanks for the post borisalv, can you show me how I use a table variable I am still learning how to write stored procedures.

 

[djj55]

To continue with what Borislav said.
Other code to consider

SET @group_id = (select [b]TOP(1)[/b] id from user_groups [b]WHERE your criteria here[/b])
SET @group_id2 = (select [b]TOP(1)[/b] id from user_groups where user_groups.company_id = '1')
SET @menu = (select [b]TOP(1)[/b] menu_item_id from user_group_rights_new where Group_id = @group_id2)

or possibly:

SET @group_id = (select [b]DISTINCT[/b] id from user_groups [b]WHERE your criteria here[/b])
SET @group_id2 = (select [b]DISTINCT[/b] id from user_groups where user_groups.company_id = '1')
SET @menu = (select [b]DISTINCT[/b] menu_item_id from user_group_rights_new where Group_id = @group_id2)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

There is another way to do this. When you use the set keyword, you can only set this to a scalar. However, "select" doesn't have the same problem...

select @group_id = id from user_groups
select @group_id2 = id from user_groups where user_groups.company_id = '1'
select @menu = menu_item_id from user_group_rights_new where Group_id = @group_id2

There is a potential problem with this code, however. This problem is similar to what Boris was getting at concerning the where clause. If there are multiple values returned by the query, which one do you expect to get? Using my method or the "top 1" method both have the same problem. If there are multiple results, how do you know which one to use?

Consider this code:

Declare @Temp Table(Id Int, Color VarChar(20))

Insert Into @Temp Values(1, 'Red')
Insert Into @Temp Values(2, 'Blue')
Insert Into @Temp Values(3, 'Black')

Declare @ColorId Int

[blue]Select @ColorId = Id
From   @Temp
Where  Color Like 'Bl%'[/blue]

Select @ColorId

The query highlighted in blue matches 2 rows in the table, blue and black. Which id do you think is returned by the entire code block? If you guessed 2 because it is the first row that matches, you would be wrong. In this case, the value returned is 3. When the assignment is made, @ColorId is actually assigned twice. Once for ID = 2, and then again for ID = 3. Since ID = 3 is the last one, that is the one the remains.

Now consider this code:

Declare @Temp Table(Id Int, Color VarChar(20) [!]Primary Key[/!])

Insert Into @Temp Values(1, 'Red')
Insert Into @Temp Values(2, 'Blue')
Insert Into @Temp Values(3, 'Black')

Declare @ColorId Int

Select @ColorId = Id
From   @Temp
Where  Color Like 'Bl%'

Select @ColorId

Now what do you think the value will be? If you guessed 3 (based on the previous discussion, you would be wrong. This time, the code returns 2 because there is a primary key added to the table variable. The primary key causes a clustered index to be built on the table variable. Thus, the result is 2.

Bottom line is... you need to make sure you are getting the value you want, otherwise your results will be wrong.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ringers]

Lets see if i can make it clearer.

user_group_rights_new
Group_id Menu_item_id
-5 -27
-5 -26
-5 -25
-5 -24
-5 -23
-5 -22
-5 -21
-5 -20
-5 -14
-5 -13
-14 1
-14 2
-14 3
-14 4
-14 5
-14 7
-14 8
-14 9
-14 10
-14 11
-14 12
-14 13
-14 14
-14 15
-14 16
-14 17
-14 18
-14 19
-14 20
-14 21
-14 22
-14 24
-14 25
-17 -26
-17 -25
-17 -24
-17 -23
-17 -22
-17 -11
-17 -10
-17 -8
-17 -7

User_groups
ID Name company_id
-1 SystemAdministrators 1
-3 SystemAdministrators 2
-4 SystemAdministrators 3
-5 SystemAdmin 1
-6 SystemAdministrators 4
-7 SystemAdministrators 5
-8 SystemAdministrators 6
-9 SystemAdministrators 7
-10 SystemAdministrators 8
-11 SystemAdministrators 9
-12 SystemAdministrators 10
-13 SystemAdministrators 11
-14 SystemAdministrators 12
-15 SystemAdministrators 13
-16 SystemAdministrators 14
-17 AccountsPayable 1
-18 AccountsPayableManger 1
-19 ReadOnly 1
-20 Coder 1
-21 Approver 1
-23 Terminated Users 1
-24 AccountsPayable 4
-26 Local System Admin 1
-27 SystemAdministrators 15
-28 AccountsPayableManager 16
-29 AccountsPayable 16
-30 Approver 16
-31 Coder 16

group_id and id are the same.

So you can see that each company shares the same group names and they have the same menu items but different group id. So when a new company and user group is added to the user_groups table. I want to copy the same menu items for the matching group with the new group id\company. But I don't want to pass any variables it should just look for group_id's that don't exist.

Create Procedure Buyer_User_Groups_Menu_Access_sp

@group_id INT = null,
@menu int = null,
@name varchar(50) = null,
@usergroupsname varchar(50) = null,
@newgroupid INT = null

AS
declare @temp table (group_id int,menu_item_id int, name varchar(50) )
insert into @temp (group_id,menu_item_id, name)
			Select  r.Group_id,
			r.Menu_item_id,
			g.Name
			from user_group_rights r
			inner join user_groups g on r.Group_id = g.ID and g.company_id = '1'

SET @newgroupid = (select group_id from user_group_rights_new)

SET @group_id = (select id from user_groups)
SET @usergroupsname = (select name from user_groups)

SET @menu = (select menu_item_id from @temp)
SET @name = (select name from @temp) 

WHILE @newGroupid != @group_id
BEGIN
	INSERT INTO user_group_rights_new
			(Group_id,Menu_item_id)	
			SELECT
			group_id = @group_id,
			menu_item_id = @menu
			where @usergroupsname = @name
END

This is sample data for the temp table i have loaded with the correct values, i want to use for the new user_group entry.

Temp
Group_id Menu_item_id Name
-17 -26 AccountsPayable
-17 -25 AccountsPayable
-17 -24 AccountsPayable
-17 -23 AccountsPayable
-17 -22 AccountsPayable
-17 -11 AccountsPayable
-17 -10 AccountsPayable
-17 -8 AccountsPayable
-18 -26 AccountsPayableManger
-18 -25 AccountsPayableManger
-18 -24 AccountsPayableManger
-18 -23 AccountsPayableManger
-18 -22 AccountsPayableManger
-18 -21 AccountsPayableManger
-21 -26 Approver
-21 -25 Approver
-21 -23 Approver
-21 -22 Approver
-21 -20 Approver
-21 -11 Approver
-21 -9 Approver
-20 -26 Coder
-20 -25 Coder
-20 -22 Coder
-20 -20 Coder
-20 -14 Coder
-20 -13 Coder
-20 -27 Coder
-20 -9 Coder

but I get an error when executing SP.

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

Tried using a table variable and a while loop to get around this, but am missing something.

 

[Ringers]

Some DML stuff.

CREATE TABLE [dbo].[user_group_rights_new]

(
[Group_id] [int] NOT NULL,
[Menu_item_id] [int] NOT NULL
)
INSERT INTO [user_group_rights_new]
([Group_id]
,[Menu_item_id])
VALUES

(-5,-27),
(-5,-26),
(-5,-25),
(-5,-24),
(-5,-23),
(-5,-22),
(-5,-21),
(-5,-20),
(-5,-14),
(-5,-13),
(-14,1),
(-14,2),
(-14,3),
(-14,4),
(-14,5),
(-14,7),
(-14,8),
(-14,9),
(-14,10),
(-14,11),
(-14,12),
(-14,13),
(-14,14),
(-14,15),
(-14,16),
(-14,17),
(-14,18),
(-14,19),
(-14,20),
(-14,21),
(-14,22),
(-14,24),
(-14,25),
(-17,-26),
(-17,-25),
(-17,-24),
(-17,-23),
(-17,-22),
(-17,-11),
(-17,-10),
(-17,-8),
(-17,-7),
(-17,-6),
(-17,-5),
(-17,-4),
(-17,-2),
(-17,1),
(-17,5),
(-17,7),
(-17,8),
(-17,9),
(-17,10),
(-17,11),
(-17,12),
(-17,13),
(-18,27),
(-18,30),
(-18,31),
(-18,34),
(-18,35),
(-18,36),
(-18,37),
(-18,38),
(-18,39),
(-18,40),
(-18,44),
(-18,50)

CREATE TABLE [dbo].[user_groups](
[ID] [int] IDENTITY(-1,-1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[company_id] [int] NULL,
CONSTRAINT [PK_groups] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [user_groups]
([Name]
,[company_id])
VALUES



(-1,SystemAdministrators,1),
(-3,SystemAdministrators,2),
(-4,SystemAdministrators,3),
(-5,SystemAdmin,1),
(-6,SystemAdministrators,4),
(-7,SystemAdministrators,5),
(-8,SystemAdministrators,6),
(-9,SystemAdministrators,7),
(-10,SystemAdministrators,8),
(-11,SystemAdministrators,9),
(-12,SystemAdministrators,10),
(-13,SystemAdministrators,11),
(-14,SystemAdministrators,12),
(-15,SystemAdministrators,13),
(-16,SystemAdministrators,14),
(-17,AccountsPayable,1),
(-18,AccountsPayableManger,1),
(-19,ReadOnly,1),
(-20,Coder,1),
(-21,Approver,1),
(-23,Terminated Users ,1),
(-24,AccountsPayable,4),
(-26,Local System Admin,1),
(-27,SystemAdministrators,15),
(-28,AccountsPayableManager,16),
(-29,AccountsPayable,16),
(-30,Approver,16)
Sample Data from user_groups_rights_new for company 1, could be inserted as well as the is the data i want to copy.

Group_id,Menu_item_id,Name
(-5,-27,SystemAdmin)
(-5,(-26,SystemAdmin)
(-5,-25,SystemAdmin)
(-5,-24,SystemAdmin)
(-5,-23,SystemAdmin)
(-5,-22,SystemAdmin)
(-5,(-21,SystemAdmin)
(-5,(-20,SystemAdmin)
(-5,-14,SystemAdmin)
(-5,-13,SystemAdmin)
(-5,-11,SystemAdmin)
(-5,-10,SystemAdmin)
(-5,-9,SystemAdmin)
(-5,-8,SystemAdmin)
(-5,-7,SystemAdmin)
(-5,-6,SystemAdmin)
(-5,(-5,SystemAdmin)
(-5,-4,SystemAdmin)
(-5,-3,SystemAdmin)
(-5,-2,SystemAdmin)
(-5,-1,SystemAdmin)
(-5,1,SystemAdmin)
(-5,2,SystemAdmin)
(-5,3,SystemAdmin)
(-5,4,SystemAdmin)
(-5,5,SystemAdmin)
(-5,7,SystemAdmin)
(-5,8,SystemAdmin)
(-5,9,SystemAdmin)
(-5,10,SystemAdmin)
(-5,11,SystemAdmin)
(-5,12,SystemAdmin)
(-5,13,SystemAdmin)
(-5,14,SystemAdmin)
(-5,15,SystemAdmin)
(-5,16,SystemAdmin)
(-5,17,SystemAdmin)
(-5,18,SystemAdmin)
(-5,21,SystemAdmin)
(-5,22,SystemAdmin)
(-5,26,SystemAdmin)
(-5,27,SystemAdmin)
(-5,28,SystemAdmin)
(-5,30,SystemAdmin)
(-5,31,SystemAdmin)
(-5,34,SystemAdmin)
(-5,35,SystemAdmin)
(-5,36,SystemAdmin)
(-5,37,SystemAdmin)
(-5,38,SystemAdmin)
(-5,39,SystemAdmin)
(-5,40,SystemAdmin)
(-5,41,SystemAdmin)
(-5,42,SystemAdmin)
(-5,43,SystemAdmin)
(-5,44,SystemAdmin)
(-5,45,SystemAdmin)
(-5,46,SystemAdmin)
(-5,47,SystemAdmin)
(-5,48,SystemAdmin)
(-5,49,SystemAdmin)
(-5,50,SystemAdmin)
(-17,(-26,AccountsPayable)
(-17,-25,AccountsPayable)
(-17,-24,AccountsPayable)
(-17,-23,AccountsPayable)
(-17,-22,AccountsPayable)
(-17,-11,AccountsPayable)
(-17,-10,AccountsPayable)
(-17,-8,AccountsPayable)
(-17,-7,AccountsPayable)
(-17,-6,AccountsPayable)
(-17,(-5,AccountsPayable)
(-17,-4,AccountsPayable)
(-17,-2,AccountsPayable)
(-17,1,AccountsPayable)
(-17,5,AccountsPayable)
(-17,7,AccountsPayable)
(-17,8,AccountsPayable)
(-17,9,AccountsPayable)
(-17,10,AccountsPayable)
(-17,11,AccountsPayable)
(-17,12,AccountsPayable)
(-17,13,AccountsPayable)
(-17,16,AccountsPayable)
(-17,22,AccountsPayable)
(-17,27,AccountsPayable)
(-17,30,AccountsPayable)
(-17,31,AccountsPayable)
(-17,34,AccountsPayable)
(-17,35,AccountsPayable)
(-17,36,AccountsPayable)
(-17,37,AccountsPayable)
(-17,38,AccountsPayable)
(-17,39,AccountsPayable)
(-17,40,AccountsPayable)
(-17,44,AccountsPayable)
(-17,50,AccountsPayable)
(-18,(-26,AccountsPayable)Manger)
(-18,-25,AccountsPayable)Manger)
(-18,-24,AccountsPayable)Manger)
(-18,-23,AccountsPayable)Manger)
(-18,-22,AccountsPayable)Manger)
(-18,(-21,AccountsPayable)Manger)
(-18,-11,AccountsPayable)Manger)
(-18,-10,AccountsPayable)Manger)
(-18,-8,AccountsPayable)Manger)
(-18,-7,AccountsPayable)Manger)
(-18,-6,AccountsPayable)Manger)
(-18,(-5,AccountsPayable)Manger)
(-18,-4,AccountsPayable)Manger)
(-18,-2,AccountsPayable)Manger)
(-18,1,AccountsPayable)Manger)
(-18,5,AccountsPayable)Manger)
(-18,7,AccountsPayable)Manger)
(-18,8,AccountsPayable)Manger)
(-18,9,AccountsPayable)Manger)
(-18,10,AccountsPayable)Manger)
(-18,11,AccountsPayable)Manger)
(-18,12,AccountsPayable)Manger)
(-18,13,AccountsPayable)Manger)
(-18,16,AccountsPayable)Manger)
(-18,22,AccountsPayable)Manger)
(-18,27,AccountsPayable)Manger)
(-18,30,AccountsPayable)Manger)
(-18,31,AccountsPayable)Manger)
(-18,34,AccountsPayable)Manger)
(-18,35,AccountsPayable)Manger)
(-18,36,AccountsPayable)Manger)
(-18,37,AccountsPayable)Manger)
(-18,38,AccountsPayable)Manger)
(-18,39,AccountsPayable)Manger)
(-18,40,AccountsPayable)Manger)
(-18,44,AccountsPayable)Manger)
(-18,50,AccountsPayable)Manger)
(-21,(-26,Approver)
(-21,-25,Approver)
(-21,-23,Approver)
(-21,-22,Approver)
(-21,(-20,Approver)
(-26,-27,Local System Admin)
(-26,-25,Local System Admin)
(-21,-11,Approver)
(-21,-9,Approver)
(-21,-8,Approver)
(-21,-7,Approver)
(-21,-6,Approver)
(-21,-3,Approver)
(-21,-2,Approver)
(-21,-1,Approver)
(-21,1,Approver)
(-21,5,Approver)
(-21,7,Approver)
(-21,8,Approver)
(-21,9,Approver)
(-21,10,Approver)
(-21,11,Approver)
(-21,12,Approver)
(-21,16,Approver)
(-21,22,Approver)
(-21,27,Approver)
(-21,30,Approver)
(-21,31,Approver)
(-21,34,Approver)
(-21,35,Approver)
(-21,36,Approver)
(-21,37,Approver)
(-21,38,Approver)
(-21,39,Approver)
(-21,40,Approver)
(-21,44,Approver)
(-21,50,Approver)
(-26,-24,Local System Admin)
(-26,-23,Local System Admin)
(-26,-22,Local System Admin)
(-26,(-21,Local System Admin)
(-26,(-20,Local System Admin)
(-26,-14,Local System Admin)
(-26,-13,Local System Admin)
(-26,-11,Local System Admin)
(-26,-10,Local System Admin)
(-26,-9,Local System Admin)
(-26,-8,Local System Admin)
(-26,-7,Local System Admin)
(-26,-6,Local System Admin)
(-26,(-5,Local System Admin)
(-26,-4,Local System Admin)
(-26,-3,Local System Admin)
(-26,-2,Local System Admin)
(-26,-1,Local System Admin)
(-26,1,Local System Admin)
(-26,2,Local System Admin)
(-26,3,Local System Admin)
(-26,5,Local System Admin)
(-26,7,Local System Admin)
(-26,8,Local System Admin)
(-26,9,Local System Admin)
(-26,10,Local System Admin))
(-23,1,Terminated Users )
(-23,7,Terminated Users)
((-20,-26,Coder)
((-20,-25,Coder)
((-20,-22,Coder)
(-20,(-20,Coder)
(-20,-14,Coder)
(-20,-13,Coder)
(-20,27,Coder)
(-20,-9,Coder)
(-20,-8,Coder)
(-20,-7,Coder)
(-26,11,Local System Admin))
(-20,-2,Coder)
(-20,-1,Coder)
(-20,1,Coder)
(-20,5,Coder)
(-20,7,Coder)
(-20,8,Coder)
(-20,9,Coder)
(-20,10,Coder)
(-20,11,Coder)
(-20,12,Coder)
(-20,13,Coder)
(-20,16,Coder)
(-20,22,Coder)
(-20,30,Coder)
(-20,31,Coder)
(-20,34,Coder)
(-20,35,Coder)
(-20,36,Coder)
(-20,37,Coder)
(-20,38,Coder)
(-20,39,Coder)
(-20,40,Coder)
(-20,44,Coder)
(-20,50,Coder)
(-19,-26,ReadOnly)
(-26,12,Local System Admin))