October 12, 2010 at 3:51 pm
Hi Guys, I am getting below error. Ca someone please help me with 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.
create procedure dbo.USP_USR_ADD_UPDATE_OMUNIT(
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SITEKEY nvarchar(20),
@PARTNERKEY nvarchar(20),
@SHORTNAME nvarchar(100),--not using, using only @UNITNAME
@STATUSCODE nvarchar(20),
@UNITNAME nvarchar(100),
@CAMPAIGNCODE nvarchar(20),--not dealt
@UNITTYPECODE nvarchar(20),
@COUNTRYCODEATTRIBUTE nvarchar(20),--not dealt
@PRIMARYOFFICE nvarchar(2),-- 0 or 1
@LOCALITY nvarchar(50),
@STREETNAME nvarchar(50),
@ADDRESS3 nvarchar(50),
@CITY nvarchar(20),
@COUNTY nvarchar(20),--state is passed
@POSTALCODE nvarchar(20),
@COUNTRYCODE nvarchar(20),
@DATEEFFECTIVE nvarchar(20),--not dealt
@DATEGOODUNTIL nvarchar(20),--not dealt
@LOCATIONTYPE nvarchar(20),
@SENDMAIL nvarchar(5),
@EMAILADDRESS nvarchar(50),
@TELEPHONE nvarchar(20),
@EXTENSION nvarchar(6),--not dealt
@FAX nvarchar(20),--not dealt
@FAXEXTENSION nvarchar(6),--not dealt
@UMPARENTUNITKEY nvarchar(20)
)
as
begin
set nocount on;
begin try
--check if the organization record is already added to BBEC
declare @ID uniqueidentifier = (select dbo.USR_UFN_GETCONSTITUENTID(@PARTNERKEY,@SITEKEY))
declare @CURRENTDATE datetime;
declare @KEYNAME nvarchar(100);
declare @KEYNAMEPREFIX nvarchar(20);
declare @ALTERNATELOOKUPIDTYPECODEID uniqueidentifier;
declare @PARENTCORPID uniqueidentifier;
declare @INDUSTRYTYPECODEID uniqueidentifier = null;
declare @ISINACTIVE bit;
declare @ISPRIMARY bit;
set @INDUSTRYTYPECODEID = dbo.USR_UFN_GETINDUSTRYTYPECODEID(@UNITTYPECODE)
if(@INDUSTRYTYPECODEID = null)
return 1;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @PARENTCORPID=dbo.USR_UFN_GETCONSTITUENTID(@UMPARENTUNITKEY,@SITEKEY)
set @CURRENTDATE = getdate();
exec dbo.USP_PARSE_ORGANIZATION_NAME @UNITNAME, @KEYNAME output, @KEYNAMEPREFIX output;
set @ISINACTIVE = dbo.USR_UFN_GETCONSTITUENTSTATUS(@STATUSCODE)
set @ISPRIMARY =
case when @PRIMARYOFFICE = '1' then 1
else 0
end
if(@ID is null)--if org does not exist in BBEC then add
begin
if @SITEKEY='2000000'
set @ALTERNATELOOKUPIDTYPECODEID=(select ID from ALTERNATELOOKUPIDTYPECODE where DESCRIPTION = 'US Petra Partner Key')
else if @SITEKEY='29000000'
set @ALTERNATELOOKUPIDTYPECODEID=(select ID from ALTERNATELOOKUPIDTYPECODE where DESCRIPTION = 'NL Petra Partner Key')
set @ID=newid()
insert into dbo.CONSTITUENT
(
ID,
ISORGANIZATION,
KEYNAME,
KEYNAMEPREFIX,
ISINACTIVE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
-1,
@KEYNAME,
@KEYNAMEPREFIX,
@ISINACTIVE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
insert into dbo.ORGANIZATIONDATA
(
ID,
INDUSTRYCODEID,
PARENTCORPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ISPRIMARY
)
values
(
@ID,
@INDUSTRYTYPECODEID,
@PARENTCORPID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ISPRIMARY
);
insert into dbo.ALTERNATELOOKUPID
(
ID,
CONSTITUENTID,
ALTERNATELOOKUPIDTYPECODEID,
ALTERNATELOOKUPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@ID,
@ALTERNATELOOKUPIDTYPECODEID,
@PARTNERKEY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
/*Start Primary Address */
if
(
(coalesce(@LOCALITY,'') <> '')
or (coalesce(@STREETNAME,'') <> '')
or (coalesce(@ADDRESS3,'') <> '')
or (coalesce(@CITY,'') <> '')
or (coalesce(@COUNTY,'') <> '')
or (coalesce(@POSTALCODE,'') <> '')
or (coalesce(@COUNTRYCODE,'') <> '')
or (coalesce(@LOCATIONTYPE,'') <> '')
or (coalesce(@SENDMAIL,'') <> '')
)
begin
declare @ADDRESSID uniqueidentifier = newid();
declare @ADDRESSBLOCK nvarchar(150) = dbo.USR_UFN_BUILDADDRESSBLOCK(@LOCALITY,@STREETNAME,@ADDRESS3);
declare @COUNTRYID uniqueidentifier = (select dbo.UFN_COUNTRY_GETID(@COUNTRYCODE,1));
declare @STATEID uniqueidentifier = (select dbo.UFN_STATE_GETID(@COUNTRYID,@COUNTY,0));
declare @DONOTMAIL bit;
set @DONOTMAIL =
case when @SENDMAIL = 'yes' then 0
when @SENDMAIL = 'no' then 1
else 0
end
declare @ADDRESSTYPECODEID uniqueidentifier = (select dbo.UFN_ADDRESSTYPECODE_GETID(@LOCATIONTYPE));
if @ADDRESSTYPECODEID = null
set @ADDRESSTYPECODEID = (select ID from ADDRESSTYPECODE where DESCRIPTION='Other')
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
ISPRIMARY,
DONOTMAIL,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ADDRESSID,
@ID,
@ADDRESSTYPECODEID,
1,
@DONOTMAIL,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTALCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/*End Primary Address */
/*Start Primary Phone */
if (coalesce(@TELEPHONE,'') <> '')
begin
insert into dbo.PHONE
(
ID,
CONSTITUENTID,
ISPRIMARY,
NUMBER,
COUNTRYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@ID,
1,
@TELEPHONE,
@COUNTRYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/*End Primary Phone */
/*Start Primary Email Address */
if ((coalesce(@EMAILADDRESS,'') <> ''))
begin
insert into dbo.[EMAILADDRESS]
(
ID,
[CONSTITUENTID],
[EMAILADDRESS],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
newid(),
@ID,
@EMAILADDRESS,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/*End Primary Email Address */
end--end of org add if does not exist in BBEC
else--if org exists in BBEC then update
begin
if(((coalesce(@KEYNAME,'') <> '') or
(coalesce(@KEYNAMEPREFIX,'') <> '') or
(coalesce(@STATUSCODE,'') <> '')) and
((@KEYNAME <> (select KEYNAME from CONSTITUENT where ID = @ID)) or
(@KEYNAMEPREFIX <> (select KEYNAMEPREFIX from CONSTITUENT where ID = @ID)) or
(@ISINACTIVE <> (select ISINACTIVE from CONSTITUENT where ID = @ID))))
begin
update dbo.CONSTITUENT
set KEYNAME = @KEYNAME,
KEYNAMEPREFIX = @KEYNAMEPREFIX,
ISINACTIVE = @ISINACTIVE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where CONSTITUENT.ID = @ID
end
if(((@INDUSTRYTYPECODEID is not null) or
(@PARENTCORPID is not null) or
(coalesce(@PRIMARYOFFICE,'') <> '')) and
((@INDUSTRYTYPECODEID <> (select INDUSTRYCODEID from ORGANIZATIONDATA where ID = @ID)) or
(@ISPRIMARY <> (select ISPRIMARY from ORGANIZATIONDATA where ID = @ID)) or
(@PARENTCORPID <> (select PARENTCORPID from ORGANIZATIONDATA where ID = @ID))))
begin
update dbo.ORGANIZATIONDATA
set INDUSTRYCODEID = @INDUSTRYTYPECODEID,
PARENTCORPID = @PARENTCORPID,
ISPRIMARY = @ISPRIMARY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ORGANIZATIONDATA.ID = @ID
end
/*Start Primary Address */
if
(
(coalesce(@LOCALITY,'') <> '')
or (coalesce(@STREETNAME,'') <> '')
or (coalesce(@ADDRESS3,'') <> '')
or (coalesce(@CITY,'') <> '')
or (coalesce(@COUNTY,'') <> '')
or (coalesce(@POSTALCODE,'') <> '')
or (coalesce(@COUNTRYCODE,'') <> '')
or (coalesce(@LOCATIONTYPE,'') <> '')
or (coalesce(@SENDMAIL,'') <> '')
)
begin
declare @ADDRESSBLOCK_UPD nvarchar(150) = dbo.USR_UFN_BUILDADDRESSBLOCK(@LOCALITY,@STREETNAME,@ADDRESS3);
declare @COUNTRYID_UPD uniqueidentifier = (select dbo.UFN_COUNTRY_GETID(@COUNTRYCODE,1));
declare @STATEID_UPD uniqueidentifier = (select dbo.UFN_STATE_GETID(@COUNTRYID,@COUNTY,0));
declare @DONOTMAIL_UPD bit;
set @DONOTMAIL_UPD =
case when @SENDMAIL = 'yes' then 0
when @SENDMAIL = 'no' then 1
else 0
end
declare @ADDRESSTYPECODEID_UPD uniqueidentifier = (select dbo.UFN_ADDRESSTYPECODE_GETID(@LOCATIONTYPE));
if @ADDRESSTYPECODEID_UPD = null
set @ADDRESSTYPECODEID_UPD = (select ID from ADDRESSTYPECODE where DESCRIPTION='Other')
--if Primary Address not there, then add else update
declare @addressid_upd uniqueidentifier = (select ID from ADDRESS where CONSTITUENTID=@ID and ISPRIMARY=1)
if(@addressid_upd is null)
begin
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,--see how it is mapped to LOCATIONTYPE or default it to OTHER
ISPRIMARY,
DONOTMAIL,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@ID,
@ADDRESSTYPECODEID,--see how it is mapped to @LOCATIONTYPE or default it to OTHER
1,
@DONOTMAIL_UPD,
@COUNTRYID_UPD,
@STATEID_UPD,
@ADDRESSBLOCK_UPD,
@CITY,
@POSTALCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else if((@ADDRESSTYPECODEID_UPD <> (select ADDRESSTYPECODEID from ADDRESS where ID = @addressid_upd)) or
(@DONOTMAIL_UPD <> (select DONOTMAIL from ADDRESS where ID = @addressid_upd)) or
(@COUNTRYID_UPD <> (select COUNTRYID from ADDRESS where ID = @addressid_upd)) or
(@STATEID_UPD <> (select STATEID from ADDRESS where ID = @addressid_upd)) or
(@ADDRESSBLOCK_UPD <> (select ADDRESSBLOCK from ADDRESS where ID = @addressid_upd)) or
(@CITY <> (select CITY from ADDRESS where ID = @addressid_upd)) or
(@POSTALCODE <> (select POSTCODE from ADDRESS where ID = @addressid_upd)))
begin
update dbo.ADDRESS
set ADDRESSTYPECODEID = @ADDRESSTYPECODEID_UPD,
DONOTMAIL = @DONOTMAIL_UPD,
COUNTRYID = @COUNTRYID_UPD,
STATEID = @STATEID_UPD,
ADDRESSBLOCK = @ADDRESSBLOCK_UPD,
CITY = @CITY,
POSTCODE = @POSTALCODE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ADDRESS.ID = @addressid_upd
end
end
/*End Primary Address */
/*Start Primary Phone */
if (coalesce(@TELEPHONE,'') <> '')
begin
--if Primary Phone not there, then add else update
declare @phoneid uniqueidentifier = (select ID from PHONE where CONSTITUENTID=@ID and ISPRIMARY=1)
if(@phoneid is null)
begin
insert into dbo.PHONE
(
ID,
CONSTITUENTID,
NUMBER,
COUNTRYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@ID,
@TELEPHONE,
@COUNTRYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else if((@TELEPHONE <> (select NUMBER from PHONE where ID = @phoneid)) or
(@COUNTRYID <> (select COUNTRYID from PHONE where ID = @phoneid)))
begin
update dbo.PHONE
set NUMBER = @TELEPHONE,
COUNTRYID = @COUNTRYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where PHONE.ID = @phoneid
end
end
/*End Primary Phone */
/*Start Primary Email Address */
if (coalesce(@EMAILADDRESS,'') <> '')
begin
--if Primary Email Address not there, then add else update
declare @emailaddressid uniqueidentifier = (select ID from EMAILADDRESS where CONSTITUENTID=@ID and ISPRIMARY=1)
if(@emailaddressid is null)
begin
insert into dbo.EMAILADDRESS
(
ID,
CONSTITUENTID,
EMAILADDRESS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@ID,
@EMAILADDRESS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else if(@EMAILADDRESS <> (select EMAILADDRESS from EMAILADDRESS where ID = @emailaddressid))
begin
update dbo.EMAILADDRESS
set EMAILADDRESS = @EMAILADDRESS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where EMAILADDRESS.ID = @emailaddressid
end
end
/*End Primary Email Address */
end--end of org update if it exists in BBEC
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end
October 12, 2010 at 4:12 pm
This is going to sound mean, but we can't help you with this one without having a copy of the database.
The error says exactly what it means, one of the multiple subqueries in the code structure is returning multiple rows, and you need singular for the comparison.
Look for the lines such as AND (SELECT field FROM table WHERE x = @x) = '' )
[Edit: Like this: if((@TELEPHONE <> (select NUMBER from PHONE where ID = @phoneid)) ]
You'll have to test each one of those with the necessary parameter settings and figure out which one is returning the multi-line data. The data is king here, it's not a coding issue, it's directly related to a data issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 12, 2010 at 4:53 pm
Thanks Craig. i am trying to debug into this code from long time.But i am unable to figure it out.Thanks for the info. I will try to debug line by line.
Sahasam..
October 13, 2010 at 9:48 am
When I run across a procedure like this, especially when it is generating an error, I start over. It is a great way to get a better handle on the process and make improvements along the way. I would suggest the following steps
1) Figure out exactly what the procedure is intended to do.
2) Break it down into logical components or steps.
3) Write each step and test it, implementing your improvements along the way. For example, fewer variables, smaller/appropriate data types, clearer statements. Annotate each step as you write it for future reference.
4) Look over the finished process and see if there are any places where you can consolidate, i.e. are there unnecessary steps?
When you're done you'll have a process that works consisting of one or more procedures. You'll know it well and will have it documented so you or anyone else can go in and see what it does. You'll likely have made it run better than before you got the error.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 13, 2010 at 11:16 am
CELKO (10/13/2010)
Sahasam (10/12/2010)
Thanks Craig. i am trying to debug into this code from long time.But i am unable to figure it out.You need to stop programming until you can get some real help. You need a complete re-design of the DDL and then you need to throw out all of your DML. I would also fire the person that did this to you.
I am extremely curious what part of "old code" you didn't understand, Celko. Not his proc and if it's that old, I doubt the person who wrote it would still be working for the company if Sahasam is trying to debug it without that person's help.
October 13, 2010 at 12:38 pm
Sahasam (10/12/2010)
Hi Guys, I am getting below error. Ca someone please help me with 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.
I have to agree with Stefan, this SP could do with reworking from the ground up (Celko is partly right too, but his offensive manner and his ad hominem attack on you are disgraceful, as is his inclusion of some wild personal prejudices that have nothing to do with the relational model or the SQL standard, and - like Brandie - I regard his post as unhelpful).
However, it should be possible to find where this particular error is coming from quite easily. The clue is in the error message - just find the subqueries that are semantically constrained to return a single value (by looking for "(select " following an assignment or comparison operator) and it has to be one of those that is going wrong. It turns out that there are 21 distinct such queries using tables and 3 using UDFs in the SP:-
select ID from ALTERNATELOOKUPIDTYPECODE where DESCRIPTION = 'US Petra Partner Key'
select ID from ALTERNATELOOKUPIDTYPECODE where DESCRIPTION = 'NL Petra Partner Key'
select ID from ADDRESSTYPECODE where DESCRIPTION='Other'
select KEYNAME from CONSTITUENT where ID = @ID
select KEYNAMEPREFIX from CONSTITUENT where ID = @ID
select ISINACTIVE from CONSTITUENT where ID = @ID
select INDUSTRYCODEID from ORGANIZATIONDATA where ID = @ID
select ISPRIMARY from ORGANIZATIONDATA where ID = @ID
select PARENTCORPID from ORGANIZATIONDATA where ID = @ID
select ID from ADDRESS where CONSTITUENTID=@ID and ISPRIMARY=1
select ADDRESSTYPECODEID from ADDRESS where ID = @addressid_upd
select DONOTMAIL from ADDRESS where ID = @addressid_upd
select COUNTRYID from ADDRESS where ID = @addressid_upd
select ADDRESSBLOCK from ADDRESS where ID = @addressid_upd
select CITY from ADDRESS where ID = @addressid_upd
select POSTCODE from ADDRESS where ID = @addressid_upd
select ID from PHONE where CONSTITUENTID=@ID and ISPRIMARY=1
select NUMBER from PHONE where ID = @phoneid
select COUNTRYID from PHONE where ID = @phoneid
select ID from EMAILADDRESS where CONSTITUENTID=@ID and ISPRIMARY=1
select EMAILADDRESS from EMAILADDRESS where ID = @emailaddressid
select dbo.UFN_COUNTRY_GETID(@COUNTRYCODE,1)
select dbo.UFN_STATE_GETID(@COUNTRYID,@COUNTY,0)
select dbo.UFN_ADDRESSTYPECODE_GETID(@LOCATIONTYPE)
but in fact they don't all have to be checked: for example the three queries
select ID from PHONE where CONSTITUENTID=@ID and ISPRIMARY=1
select NUMBER from PHONE where ID = @phoneid
select COUNTRYID from PHONE where ID = @phoneid
can all be checked by a single statement, because which column is selected is irrelevant - it's the number of rows hit that counts, so a query like select * from (select ID, count(*)) from phone group by ID) G will check the database to see whether any of these three queries can be causing the problem (if it returns any rows, the phone table has duplicate IDs) so all the tables where duplicate ID is the potential issue can be checked with just 5 queries. The other three table queries need slightly different checks (because the selection isn't on the ID column) as do the UDFs (just look at thoose to see they are valid scalar functions). If none of these throws up the answer, the SP must be inserting two of something with a new id when it creates a new id - and that should be spottable by examination. (Celko is right when he says the DDL needs fixing as well as the SP, since all these checks should have been handled automatically by integrity constraints to prevent the tables ever getting duplicate IDs (or DESCRIPTIONs, as the case may be).
When looking at code like this, remember (a) that conditions for an update generally belong in the where clause, not in an if statement; and (b) that where (a) doesn't eliminate some "if" statements a case statement is often better than a string of "if"s; and (c) where selection from a single table using a given conjunction of column equalities is required always to return at most one row this should be enforced by a UNIQUE constraint (or a PRIMARY KEY constraint). Changing the code and ddl to be like this may either eliminate or find the source of a problem like yours very quickly (eg adding a unique constraint may fail - and then you know which table has a data problem).
edit: typos
Tom
October 13, 2010 at 6:10 pm
CELKO (10/13/2010)
[font="Arial Black"]Your tables have singular names[/font], to show that you think they are files, not sets.....
Should have been:
UPDATE Foobar
SET ..
WHERE Foobar.id = @id
AND EXISTS
(SELECT *
FROM ..
WHERE ..);
or, better:
UPDATE Foobar
SET col_a = COALESCE (@in_col_a, col_a),
col_b = COALESCE (@in_col_b, col_b),
..
WHERE ..;
BWAA -HAAA!!! In that case, the bloody table name should be "Foobars"! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2010 at 6:36 pm
Brandie Tarvin (10/13/2010)
I am extremely curious what part of "old code" you didn't understand...
Heh... be careful, Brandie. It may not be the OP's code but the code isn't THAT old when it has SS2008 constructs in it like...
declare @emailaddressid uniqueidentifier = (select ID from EMAILADDRESS where CONSTITUENTID=@ID and ISPRIMARY=1)
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2010 at 6:42 pm
Sahasam (10/12/2010)
Thanks Craig. i am trying to debug into this code from long time.But i am unable to figure it out.Thanks for the info. I will try to debug line by line.Sahasam..
Nah... let the machine do it for you. Declare the input parameters and assign them the values that are causing the error. Run the code as a script in SSMS instead of as a sproc and it will at least point you to the section of code that is causing the error.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2010 at 3:22 pm
I agree with Jeff on this one - run it in steps as a script to find the area of the problem. That being said, there are only a few reasons why sub queries return more than 1 value (the values of more than 1 row).
1. There is something you don't understand about the tables or relationships
or there is something you overlooked about them.
2. There really should be only 1 row that satisfies the query and you have bad
or unexpected data in the table.
Running the queries to find where more will show you where the duplicates are.
As a previous post already gave you, the query:
SELECT ID, COUNT(*) FROM PHONE
GROUP BY ID HAVING COUNT(*) > 1
should be a good place to start.
Todd Fifield
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply