Concatenated string as parameter argument value does not give output in stored procedure

Question

I am using SQL SERVER 2008 management studio.
I have a stored procedure which accepts several parameters.
One parameter name is @recvemail with default value as 'B'.
If the value of @recvemail is 'Y' then all the users who opted for email news letter should be displayed in the output.
If @recvemail is 'N' then users who do not wish to receive email letter should be included in the output.
If the value is 'B' then all the users should be displayed in the output, whether they who opted for news letter or not opted for news letter.

Now for default value 'B' i tried the set the parameter argument values as 'Y', 'N' but I am not getting any output.

My stored proc is

Create procedure [dbo].[proc_getMembershipEmailbackup] 
-- exec proc_getMembershipEmail @From=N'1991-06-11',@To=N'2017-06-14',@DevoteeName=N'',@MembershipName=N'Membership Life',@log_cond=N'',@recvemail=N'N'

@From varchar(10),                                      
@To varchar(10),                                      
@DevoteeName varchar(60)='',   
@MembershipName varchar(100)='ALL',           
@Email varchar(50)= '',                             
@Phoneno varchar(50)= '',  
@MinAmount decimal =Null,    
@MaxAmount decimal =NULL,  
@log_cond Varchar(5), 
@recvemail char(61)='B' 

As   
Begin     
    if @recvemail = 'B'  
    Begin  
      Set @recvemail='Y' + ',' + 'N'  
    End  
end                  
Begin     
    if @MembershipName = 'ALL'  
    Begin  
        Set @MembershipName=''  
    End  
end
begin

select 
            ROW_NUMBER() over (order by mu.f_name) as [No],
            mu.f_name  +' '+mu.l_name  as DevoteeName ,  
            mu.pers_email as Email,  
            mu.home_phone as PhoneNo,
            mp.name as MembershipName, 
            mu.recv_email as optedforemail,
            d1.memb_id ,  
            d1.amt_due as amount_due,  
            CONVERT(varchar(10),d1.nextduedate,101) as duedate,  
            d1.pay_term


from d_user_memb d1   
            left join m_user mu on d1.user_id = mu.user_id  
            left join m_memb_pledge mp on d1.memb_id = mp.memb_id   
where  
            d1.status in ('A','D','C')
            AND(Isnull(@From,'') = '' or CONVERT(date,d1.nextduedate ) >=  CONVERT(date,@From) )              
            and  (Isnull(@To,'') = '' or CONVERT(date,d1.nextduedate ) <= CONVERT(date,@To ))             
            and  (Isnull(@DevoteeName,'') = '' or (f_name +' '+l_name) like '%' + @DevoteeName + '%')
            and (recv_email in (@recvemail ) )
            and (Isnull(@MembershipName,'') = '' or (mp.name) = @MembershipName)     
            and (ISNULL (@Email, '') = '' or mu.pers_email = @Email)    
            and (ISNULL (@Phoneno, '') = '' or mu.home_phone = @Phoneno) 

            --and (ISNULL (@MinAmount,0) = 0 or cast(d1.amt_due as decimal(10,2)) >=@MinAmount)  
            --and (ISNULL (@MaxAmount, 0) =0 or cast(d1.amt_due as decimal(10,2)) <= @MaxAmount)  
            and d1.amt_due<>0  
            and mu.status='A'  
            and (    
    (    
     @log_cond = 'b'     
     AND d1.amt_due BETWEEN @MinAmount AND @MaxAmount    
    )        
    OR     
    (    
     @log_cond <> 'b'     
     AND @log_cond <> '0'            
     AND      
     (    
      isnull(@log_cond,'')=''         
      OR (@log_cond = '>' AND d1.amt_due > @MinAmount)        
      OR (@log_cond = '<' AND d1.amt_due < @MinAmount)   
      OR (@log_cond = '>=' AND d1.amt_due >= @MinAmount)        
      OR (@log_cond = '<=' AND d1.amt_due <= @MinAmount)          
      OR (@log_cond='=' AND d1.amt_due =@MinAmount)    
     )        
    )        
    OR        
    (    
     ISNULL(@log_cond,'')='0'         
    )        
   )

End

I tried to execute the procedure with values like this but I am not getting any output

use CHTLIVE
GO
exec proc_getMembershipEmailbackup  @From=N'1800-06-11',@To=N'2099-06-14',@DevoteeName=N'',@log_cond='', @recvemail='B'

If the change the parameter value @recvemail from 'B' to 'Y', I am getting output correctly and all users who opted for email letter is displayed correctly in the output.
Similarly I changed the value from 'B' to 'N' I am getting the output correctly and all users who have not opted for email letter is displayed in the output.
Only for value 'B' I am not getting output. I should get the output such that all users who have either opted or not opted for email letter should be present in the output.

You cannot use IN like that. That's not how the clause works. IN must be explicitly defined and not using a variable of concatenated strings. — Mark Sinkinson
– Mark Sinkinson, Commented Jun 15, 2015 at 8:08

Community · Accepted Answer · 2017-05-23 11:58:32Z

2

As Mark Sinkinson wrote in his comment, the IN() operator expects a comma separated list of arguments, but you have supplied it with a single argument that contains a comma separated list of values.

Change this part of the stored procedure:

and (recv_email in (@recvemail ) )

to this:

and (recv_email = @recvemail OR @recvemail = 'B')

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jun 15, 2015 at 8:12

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Panagiotis Kanavos Over a year ago

I assume you mean recv_email = case .... This can result in an inefficient execution plan. The optimizer will cache the execution plan that results from the first execution. If that is a B, it will not take filtering of recv_email into account, eg ignoring any indexes that include it

Zohar Peled Over a year ago

@PanagiotisKanavos you are correct. I've edited my answer.

BSG Over a year ago

Thanks @Zohar Peled for your brilliant answer. I got it fixed after changing my procedure as per your suggestion. You just fixed my problem in one line of code. Thank you so much.

Panagiotis Kanavos · Accepted Answer · 2015-06-15 08:23:40Z

2

The line

and (recv_email in (@recvemail ) )

is equivalent to

and recv_email = @recvemail

IN works against a list of arguments where you only pass a single argument. It doesn't parse the arguments' contents to see if any of them could be interpreted as a comma-separated list.

If you want to check against a list of options, create a table valued parameter and use it in your IN clause:

declare @emailoptions table (Option varchar(2))

IF (@recvemail = 'B'  OR @recvemail = 'Y')
begin
    insert into @emailoptions VALUES('Y')
end if
IF (@recvemail = 'B'  OR @recvemail = 'N')
begin
    insert into @emailoptions VALUES('N')
end if

This would allow you to joint your tables with @emailOptions

from d_user_memb d1   
    left join m_user mu on d1.user_id = mu.user_id  
    left join m_memb_pledge mp on d1.memb_id = mp.memb_id   
    inner join @emailOptions on Option=@recvc_email

This allows you to use more than two options, and the optimizer will be able to use any indexes that include recv_email to create a faster execution plan.

answered Jun 15, 2015 at 8:23

Panagiotis Kanavos

134k16 gold badges211 silver badges271 bronze badges

5 Comments

Zohar Peled Over a year ago

Though I generally agree with the table valued parameter approach and have recommended it numerous times both in stackoverflow and other websites, I think you are shooting flies with an elephant gun on this one. I don't imagine having another option to a yes or no question...

BSG Over a year ago

Thanks for your solution. I accepted @Zohar Peled as my answer since his solution fixed my problem in one line.

Panagiotis Kanavos Over a year ago

@Zohar Depends on how frequent the call is. If it's once in a while, I agree. If it's frequent, the lack of indexing will be a pain

Panagiotis Kanavos Over a year ago

@BSG different solutions are appropriate for different scenarios. A once-in-a-while backup may not need high performance. Frequent mass mailings though would, to avoid blocking your user table for a long time. You could also perhaps use different views per scenario (I would) to simplify the stored procedure

Zohar Peled Over a year ago

@PanagiotisKanavos see my edited answer. there is nothing to stop the database from using any index on the recv_email in my suggested solution...

Collectives™ on Stack Overflow

Concatenated string as parameter argument value does not give output in stored procedure

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related