September 8, 2009 at 3:44 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:(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 4:15 am
( Profession= CASE when @Profession 0 then
-- (select Profession from BK_TagSMS_Access
----select value from BK_TagSMS_ParameterSplit(@Profession,',')))
@Profession
else Profession end)
AND
Not sure if I understood your requirement correctly but if this part of the code is a problem and if you have multiple values, then it should have been Profession in (<>) right?
I suggest writing a dynamic query in this case to buil the sql and then execute it.
---------------------------------------------------------------------------------
September 8, 2009 at 4:31 am
Try this article for the general pro's and cons.
http://sqlserverpedia.com/blog/sql-server-bloggers/catch-all-queries/
Try the exists clause to test your output from BK_TagSMS_ParameterSplit
September 8, 2009 at 4:34 am
PP (9/8/2009)
( Profession= CASE when @Profession 0 then
-- (select Profession from BK_TagSMS_Access
----select value from BK_TagSMS_ParameterSplit(@Profession,',')))
@Profession
else Profession end)
AND
Not sure if I understood your requirement correctly but if this part of the code is a problem and if you have multiple values, then it should have been Profession in (<>) right?
I suggest writing a dynamic query in this case to buil the sql and then execute it.
September 8, 2009 at 6:46 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.
reading the articles posted as reply only tells me to write a dynamic query, if i write one how will i handle the multiple value part?
September 8, 2009 at 7:52 am
There could be ways to do this without the dynamic query also, but i can think of this at the moment,
declare @wheresql nvarchar(max)
declare @selectsql nvarchar(max)
declare @sql nvarchar(max)
Set @selectsql = 'SELECT * FROM MAINTABLE WHERE YOURCOLUMN '
IF (@yourparameter)
BEGIN
set @wheresql = ' = @yourparameter'
END
ELSE
set @wheresql = 'IN(SELECT COLUMN FROM REFTABLE)'
SET @sql = @selectsql + @wheresql
PRINT @sql
EXEC sp_executeSQL @sql
No guarantee as I just concentrated on that part of your code only. Folks might come up with killer queries! 🙂
---------------------------------------------------------------------------------
September 8, 2009 at 11:02 am
Hi ,
here the problem is at the below part right?
/*
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)
*/
so can you try giving 'IN' instead of giving '=' , because the field Profession can be any of the value resulting by the sub query . So try the below code
/*
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 here give the select statemet instead of giving the Profession field directly end))
*/
Regards,
MC
Thanks & Regards,
MC
September 8, 2009 at 1:28 pm
The subquery here is acting as an 'expression' and I think since this is returning more than one value, it can not be used as an expression. Query would throw an error. Try this,
CREATE TABLE CASE_IN_TEST(ID int, Some_VALUE Varchar(2))
INSERT INTO CASE_IN_TEST VALUES(1,'A')
INSERT INTO CASE_IN_TEST VALUES(1,'B')
INSERT INTO CASE_IN_TEST VALUES(1,'C')
INSERT INTO CASE_IN_TEST VALUES(2,'D')
INSERT INTO CASE_IN_TEST VALUES(2,'E')
INSERT INTO CASE_IN_TEST VALUES(2,'F')
--This would give an error (since subquery would return more than one value)
SELECT * FROM CASE_IN_TEST WHERE ID IN (CASE WHEN 1=1 Then (SELECT ID FROM CASE_IN_TEST WHERE ID =1)
ELSE (SELECT ID FROM CASE_IN_TEST WHERE ID =2) END)
Truncate table CASE_IN_TEST
INSERT INTO CASE_IN_TEST VALUES(1,'A')
INSERT INTO CASE_IN_TEST VALUES(2,'D')
--This should now be fine since subquery returns only one value!
SELECT * FROM CASE_IN_TEST WHERE ID IN (CASE WHEN 1=1 Then (SELECT ID FROM CASE_IN_TEST WHERE ID =1)
ELSE (SELECT ID FROM CASE_IN_TEST WHERE ID =2) END)
---------------------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply