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


i using sql server 2008 management studio.
have stored procedure accepts several parameters.
1 parameter name @recvemail default value 'b'.
if value of @recvemail 'y' users opted email news letter should displayed in output.
if @recvemail 'n' users not wish receive email letter should included in output.
if value 'b' users should displayed in output, whether opted news letter or not opted news letter.

now default value 'b' tried set parameter argument values 'y', 'n' not getting output.

my stored proc 

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'      begin          if @recvemail = 'b'       begin         set @recvemail='y' + ',' + 'n'       end   end                   begin          if @membershipname = 'all'       begin           set @membershipname=''       end   end begin  select              row_number() on (order mu.f_name) [no],             mu.f_name  +' '+mu.l_name  devoteename ,               mu.pers_email email,               mu.home_phone phoneno,             mp.name membershipname,              mu.recv_email optedforemail,             d1.memb_id ,               d1.amt_due amount_due,               convert(varchar(10),d1.nextduedate,101) duedate,               d1.pay_term   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                  d1.status in ('a','d','c')             and(isnull(@from,'') = '' or convert(date,d1.nextduedate ) >=  convert(date,@from) )                           ,  (isnull(@to,'') = '' or convert(date,d1.nextduedate ) <= convert(date,@to ))                          ,  (isnull(@devoteename,'') = '' or (f_name +' '+l_name) '%' + @devoteename + '%')             , (recv_email in (@recvemail ) )             , (isnull(@membershipname,'') = '' or (mp.name) = @membershipname)                  , (isnull (@email, '') = '' or mu.pers_email = @email)                 , (isnull (@phoneno, '') = '' or mu.home_phone = @phoneno)               --and (isnull (@minamount,0) = 0 or cast(d1.amt_due decimal(10,2)) >=@minamount)               --and (isnull (@maxamount, 0) =0 or cast(d1.amt_due decimal(10,2)) <= @maxamount)               , d1.amt_due<>0               , mu.status='a'               , (         (          @log_cond = 'b'           , d1.amt_due between @minamount , @maxamount         )             or          (          @log_cond <> 'b'           , @log_cond <> '0'                  ,            (           isnull(@log_cond,'')=''                or (@log_cond = '>' , d1.amt_due > @minamount)               or (@log_cond = '<' , d1.amt_due < @minamount)          or (@log_cond = '>=' , d1.amt_due >= @minamount)               or (@log_cond = '<=' , d1.amt_due <= @minamount)                 or (@log_cond='=' , d1.amt_due =@minamount)          )             )             or             (          isnull(@log_cond,'')='0'              )            )  end

i tried execute procedure values not getting output 

use chtlive go exec proc_getmembershipemailbackup  @from=n'1800-06-11',@to=n'2099-06-14',@devoteename=n'',@log_cond='', @recvemail='b'

if change parameter value @recvemail 'b' 'y', getting output correctly , users opted email letter displayed correctly in output.
changed value 'b' 'n' getting output correctly , users have not opted email letter displayed in output.
value 'b' not getting output. should output such users have either opted or not opted email letter should present in output.

as mark sinkinson wrote in his comment, in() operator expects comma separated list of arguments, have supplied single argument contains comma separated list of values.

change part of stored procedure:

and (recv_email in (@recvemail ) )

to this:

and (recv_email = @recvemail or @recvemail = 'b')

Comments

Post a Comment

Popular posts from this blog

javascript - Google App Script ContentService downloadAsFile not working -

i have web app developed using google app script htmlservice , html form, populating excel sheet in google drive using spreadsheetapp . , 1 section calling contentservice download data excel file. function doget(e) { // read excel sheet //getappfile(); // render application html template return htmlservice.createtemplatefromfile('index').evaluate() .settitle('go smart') .setsandboxmode(htmlservice.sandboxmode.iframe); } function downloaddoublequatecsvfile() { var sheetid = propertiesservice.getscriptproperties().getproperty('sheetid'); var ss = spreadsheetapp.openbyid(sheetid).getactivesheet(); //var ss = spreadsheetapp.getactivespreadsheet().getactivesheet(); var maxcolumn = ss.getlastcolumn(); var maxrow = ss.getlastrow(); var data = ss.getrange(1, 1, maxrow, maxcolumn).getvalues(); if (data.length > 1) { var csv = ""; (var row = 0; row < data.length; row++) { (var c...
Read more

javascript - Function overwritting -

is there way overwrite function or event handling in function? using addeventlistener, can have many event handlers on element want var test=document.getelementbyid("div"); test.addeventlistener("click",function(){alert("im first");},false); test.addeventlistener("click",function(){alert("im second");},false); // alerts "im first" , after "im second" but if want overwrite function/event example based on client width example this function one() { alert("first"); } function two() { alert("second"); } window.onresize = function() { var test = document.getelementbyid("div"); if (window.innerwidth > 500) { test.onclick = 1 } else { test.onclick = two; } } is possible in javascript? in case use effective little known approach. addeventlistener can accept object property handleevent event handler. in case it's easy overwr...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more