September 8, 2009 at 3:58 am
hi all,
please help. Im trying to write a SProc that receives optional parameter to retrieve data from a particular table, and ive been able to do that using CASe in my where clause after first declaring my parameters as null and integer(since all the parameters are either int or bit).My problem right now is some of the parameters contains list of values, and i want my where clause to also to use this to limit the selection but i keep getting an error that multiple rows are returned cos my subquery in the where clause returns a table that i will like to AND with other parameters.Below is my code [Sad] The BK_TAGSMS_ParameterSplit is a function that helps me put the comma delimited values in my parameter into list.)
@Profession int=0,
@LocalityId int=0,
@City int=0,
@Agerange int=0,
@education int=0,
@Religion int=0,
@Sex int=0,
@UsertypeId int=0,
@isCareer bit=NULL,
@IsFashion bit=NULL,
@isElectronics bit=NULL,
@isPhone bit=NULL,
@isAutos bit=NULL,
@isLaptop bit=NULL,
@isEvent bit=NULL,
@isRelationship bit=NULL,
@isTravel bit=NULL,
@isBook bit=NULL,
@isFootball bit=NULL,
@isMusic bit=NULL,
@isFinancial bit=Null,
@isHealth bit=NULL,
@isParenting bit=NULL,
@isNightLife bit=NULL,
@isPets bit=NULL,
@isSports bit=NULL,
@isOffice bit=NULL,
@isJewelry bit=NULL,
@referredBy int=NULL,
@isFood bit=NULL,
@isHouse bit=NULL,
@requested int=0,
@nonrequested int=0,
@locationId int=0,
@isTv bit=NULL,
@IsCelebrity bit=NULL,
@IsMarried bit=NUll,
@IsMovie bit=NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Select firstname,gsm,lastrecieveddate from BK_TagSMS_Access where (
( locationId= CASE when @locationId <> 0 then @locationId
else locationId
end)
AND
( Profession= CASE when @Profession <>0 then
-- (select Profession from BK_TagSMS_Access
----select value from BK_TagSMS_ParameterSplit(@Profession,',')))
@Profession
else Profession end)
AND
( LocalityId= CASE when @LocalityId <> 0 then
@LocalityId else LocalityId end)
AND
( City= CASE when @City <> 0 then
@City else City end)
AND
( Agerange= CASE when @Agerange <> 0 then
@Agerange else Agerange end)
AND
( Education= CASE when @education <> 0 then
@education else Education end)
AND
( Religion= CASE when @Religion <> 0 then
@religion else Religion end)
AND
( Sex= CASE when @Sex <> 0 then
@Sex else Sex end)
AND
( UsertypeId= CASE when @UsertypeId <> 0 then
@UsertypeId else UsertypeId end)
AND
( IsFashion= CASE when @isFashion IS NOT NULL then
@isFashion else isFashion end)
AND
( IsElectronics= CASE when @isElectronics IS NOT NULL then
@isElectronics else isElectronics end)
AND
( IsAutos= CASE when @isAutos IS NOT NULL then
@isAutos else isAutos end)
AND
( IsLaptop= CASE when @isLaptop IS NOT NULL then
@isLaptop else isLaptop end)
AND
( IsCareer= CASE when @isCareer IS NOT NULL then
@isCareer else isCareer end)
AND
( IsEvent= CASE when @isEvent IS NOT NULL then
@isEvent else isEvent end)
AND
( IsRelationship= CASE when @isRelationship IS NOT NULL then
@isRelationship else isRelationship end)
AND
( IsTravel= CASE when @isTravel IS NOT NULL then
@isTravel else isTravel end)
AND
( IsBook= CASE when @isBook IS NOT NULL then
@isBook else isBook end)
AND
( IsFootball= CASE when @isFootball IS NOT NULL then
@isFootball else isFootball end)
AND
( IsMusic= CASE when @isMusic IS NOT NULL then
@isMusic else isMusic end)
AND
( IsFinancial= CASE when @isFinancial IS NOT NULL then
@isFinancial else isFinancial end)
AND
( IsHealth= CASE when @isHealth IS NOT NULL then
@isHealth else isHealth end)
AND
( IsParenting= CASE when @isParenting IS NOT NULL then
@isParenting else isParenting end)
AND
( IsNightlife= CASE when @isNightlife IS NOT NULL then
@isNightlife else isNightlife end)
AND
( IsPets= CASE when @isPets IS NOT NULL then
@isPets else isPets end)
AND
( IsSports= CASE when @isSports IS NOT NULL then
@isSports else isSports end)
AND
( IsOffice= CASE when @isOffice IS NOT NULL then
@isOffice else isOffice end)
AND
( Isjewelry= CASE when @isJewelry IS NOT NULL then
@isJewelry else isJewelry end)
AND
( referredBy= CASE when @referredBy <> 0 then
@referredBy else referredBy end)
AND
( requested= CASE when @requested <> 0 then
@requested else requested end)
AND
( nonrequested= CASE when @nonrequested <> 0 then
@nonrequested else nonrequested end)
AND
( IsMarried= CASE when @IsMarried IS NOT NULL then
@IsMarried else IsMarried end)
AND
( IsMovie= CASE when @IsMovie IS NOT NULL then
@IsMovie else IsMovie end)
AND
( Isphone= CASE when @Isphone IS NOT NULL then
@Isphone else Isphone end)
AND
( IsMovie= CASE when @IsMovie IS NOT NULL then
@IsMovie else IsMovie end)
AND
( IsTv= CASE when @IsTV IS NOT NULL then
@IsTV else IsTV end)
AND
( IsCelebrity= CASE when @IsCelebrity IS NOT NULL then
@IsCelebrity else IsCelebrity end)
AND
( IsMarried= CASE when @IsMarried IS NOT NULL then
@IsMarried else IsMarried end)
)
END
please any urgent help will be appreciated.Thanks
September 8, 2009 at 5:24 am
hi,
actually if you put some example data then it will be more useful to tell you, but don't know why you write such query i think your query structure is not good properly used all tables with appropriate join use only columns those are needed , now your probelm can happen because if you declare parameter
for example @p int
select @P=id from tabl 1 now that time what will happen there whatever 1st thing come will put there.
write proper where condition and in which you expect more then one row can come take that into one paramter with all values with comma separated
------------------------- EXAMPLE---------------
DECLARE @p_NO VARCHAR(2000)
DECLARE @COMPANY VARCHAR(100)
SELECT @Company = [Company_Legalentity] ,
@p_no = COALESCE(@p_no+',','')+CAST(NO AS VARCHAR)
FROM TAB1
WHERE id = @id
--------------------------------------
FROM MY TAB1 FOR ID 1 HAVE VALUES 1,2,3 FOR COMPANY RAJ
SO FOR THIS OUTPUT WILL BE RAJ AND @p_NO WILL 1,2,3
OK GOT IT now ......
Raj Acharya
September 8, 2009 at 6:11 am
Without commenting on the wisdom(?) of your approach, I will say that lists of values are tested using IN, not equals (=). Another method would be to populate a table variable to join to your primary table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2009 at 6:42 am
Hi, thanks for your reply, actually the correct code i am using is:(just an excerpt)
AND
( Profession= CASE when @Profession is not null then
(select Profession from BK_TagSMS_Access where Profession in
(select cast(value as int) from BK_TagSMS_ParameterSplit(@Profession,',')))
--@Profession
else Profession end)
AND
( LocalityId= CASE when @LocalityId 0 then
@LocalityId else LocalityId end)
AND
( City= CASE when @City 0 then
@City else City end)
AND
( Agerange= CASE when @Agerange 0 then
@Agerange else Agerange end)
AND
the profession part returns the correct values when i execute the query but because of still anding the subquery result with other parameters in the where clause i get 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.
"
my Question is how do i go about this, any other option, will using a dynamic query solve this problem and how?
thanks.
September 8, 2009 at 6:52 am
hi,
are you facing problem where these subquery error occurs ?
is this right
then if you are going to execute direct procedure then will show you this error but it will be little bit difficult to go to that line.
and for easy way what i am doing is
just take all the parameter on new query window declare that pass values into that and put your procedure query through this you will directly go to your error where that happen this will easy to find error but little bit more work to do for finding that.
Raj Acharya
September 8, 2009 at 6:52 am
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= .... "
You subquery is returning a set of values. I say again: Such comparisons are done by using "IN" not "=".
where [column1] IN (1,2,3,4)
not
where [column1] = (1,2,3,4)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2009 at 6:57 am
yeh you are right where = (1,2,3,4)
will be get error for that
and another cause for that where = case when 1 or 2
if she used case then one or more value comes into that then also this can happen
but don't know on which line you are trying to tell.........
and if mayor use like this where = (1,2,3,4) then pls use where in (1,2,3,4)
Raj Acharya
September 8, 2009 at 7:06 am
select Profession from BK_TagSMS_Access where Profession in
(select cast(value as int) from BK_TagSMS_ParameterSplit(@Profession,','))
is this query run completely ............
do one thing to get complete solution for your problem give some sample data so all can help you completely
Raj Acharya
September 8, 2009 at 7:24 am
hi,
i tried changing the subquery to 'in' instead of the = and i was still getting the same error. also i changed every operator in the query to in , yet i still get the same error, changing it to :
Select firstname,gsm,lastrecieveddate from BK_TagSMS_Access where (
( locationId in (CASE when @locationId is not null then @locationId
else locationId
end))
AND
( Profession in (CASE when @Profession is not null then
(select Profession from BK_TagSMS_Access where Profession in
(select cast(value as int) from BK_TagSMS_ParameterSplit(@Profession,',')))
--@Profession
else Profession end))
AND
( LocalityId in (CASE when @LocalityId is not null then
@LocalityId else LocalityId end))
AND
( City in(CASE when @City is not null then
@City else City end))
AND
( Agerange in (CASE when @Agerange is not null then
@Agerange else Agerange end))
AND
( Education in (CASE when @education is not null then
@education else Education end))
AND
( Religion in(CASE when @Religion is not null then
@religion else Religion end))
AND
also, running the subquery alone returns 6 rows from the table.
September 8, 2009 at 8:02 am
hi,
can u some sample data with
create script of table
insert script
and your query which causing error
and one more thing when you did as per i told you declare all the your sp input parameter
and put this select query .... and see where problem comes
and i think for your hint this problem mostly comes where you use = and here more then one value will come...... so better to check your queries every part one by one don't get frustrated just be cool........ concentrate on your query with cool mind you will definitely got the solution, or not send me sample data
Raj Acharya
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply