how write long sql query in string format in multiline

Question

what i have done so far is putting @ at the starting point of the string as shown below and it is working fine:

strPreviousYearStatementQuery = @"

              if exists       
                       (select TOP 1 1 from tbl_addbill where forUser='sun4269' and bill_date is null and bill_no='2015-2016' )
                 begin 
                      (select 'record found')/*table[0]*/
                      (select B_id,amount,amount_paid  from tbl_addbill where bill_no='2015-2016'  and  forUser='sun4269' and bill_date is null)/*table[1]*/
                      (select distinct P_id from tbl_addparty where forUser='sun4269' and P_id not in (select B_id from tbl_addbill where bill_no='2015-2016' and                                           forUser='sun4269' and bill_date is null ))/*table[2]*/ 
                 end
                 else begin
                        if EXISTS (select top 1 1 from tbl_addbill where forUser='sun4269' and bill_date between '2015-04-01' and '2016-03-31')
                            begin 
                            select 'data inserted'/*table[0]*/ 
                            insert into tbl_addbill (bill_no,B_id,amount,tax,amount_paid,forUser) output inserted.B_id , inserted.amount, inserted.amount_paid /*table[1]*/ 
                            select bill_no='2015-2016', B_id, COALESCE(sum(amount),0), COALESCE (sum(tax),0), COALESCE(sum(amount_paid),0), 'sun4269' from tbl_addbill where                                      forUser='sun4269' and bill_date between '2015-04-01' and '2016-03-31' group by B_id; 
                            (select distinct P_id from tbl_addparty where forUser='sun4269' and P_id not in (select B_id from tbl_addbill where bill_no='2015-2016' and                                           forUser='sun4269' and bill_date is null)/*table[2]*/) 
                        end
                        else
                        select 'no previous year record'
                        end
                        ";

but what i need to do is this and it is giving me the error :

strPreviousYearStatementQuery = @"

                  if exists       
                           (select TOP 1 1 from tbl_addbill where forUser='"+userid+"' and bill_date is null and bill_no='" + (int.Parse(year.ToString()) - 1) + "-"+year+"' )
                     begin 
                          (select 'record found')/*table[0]*/
                          (select B_id,amount,amount_paid  from tbl_addbill where bill_no='" + (int.Parse(year.ToString()) - 1) + "-"+year+"'  and  forUser='"+userid+"' and bill_date is null)/*table[1]*/
                          (select distinct P_id from tbl_addparty where forUser='"+userid+"' and P_id not in (select B_id from tbl_addbill where bill_no='" + (int.Parse(year.ToString()) - 1) + "-"+year+"' and  forUser='"+userid+"' and bill_date is null ))/*table[2]*/ 
                     end
                     else begin
                            if EXISTS (select top 1 1 from tbl_addbill where forUser='"+userid+"' and bill_date between '2015-04-01' and '2016-03-31')
                                begin 
                                select 'data inserted'/*table[0]*/ 
                                insert into tbl_addbill (bill_no,B_id,amount,tax,amount_paid,forUser) output inserted.B_id , inserted.amount, inserted.amount_paid /*table[1]*/ 
                                select bill_no='" + (int.Parse(year.ToString()) - 1) + "-"+year+"', B_id, COALESCE(sum(amount),0), COALESCE (sum(tax),0), COALESCE(sum(amount_paid),0), '"+userid+"' from tbl_addbill where                                      forUser='"+userid+"' and bill_date between '2015-04-01' and '2016-03-31' group by B_id; 
                                (select distinct P_id from tbl_addparty where forUser='"+userid+"' and P_id not in (select B_id from tbl_addbill where bill_no='" + (int.Parse(year.ToString()) - 1) + "-"+year+"' and                                           forUser='"+userid+"' and bill_date is null)/*table[2]*/) 
                            end
                            else
                            select 'no previous year record'
                            end
                            ";

Your variable could include single quote, so you have to replace it with single single quote like replace(" ' ", " ' ' ") — Tony Dong
– Tony Dong, Commented Oct 26, 2016 at 15:57
If you break the line continuation initiated by @ with a concatenation, then you should restart the string continuation with another @. (or use again the + to concatenate the pieces) — Steve
– Steve, Commented Oct 26, 2016 at 15:59
Better you make a SP for this. Or simply try to concatenate them. — AT-2017
– AT-2017, Commented Oct 26, 2016 at 16:02

Marcelo Gondim · Accepted Answer · 2016-10-26 16:47:33Z

Try string.Format();

strPreviousYearStatementQuery = string.Format(@"

              if exists       
                       (select TOP 1 1 from tbl_addbill where forUser='{0}' and bill_date is null and bill_no='{1}-{2}' )
                 begin
                      (select 'record found')/*table[0]*/
                      (select B_id, amount, amount_paid  from tbl_addbill where bill_no = '{1}-{2}'  and  forUser = '{0}' and bill_date is null)/*table[1]*/
                      (select distinct P_id from tbl_addparty where forUser = '{0}' and P_id not in (select B_id from tbl_addbill where bill_no = '{1}-{2}' and  forUser = '{0}' and bill_date is null))/*table[2]*/ 
                 end
                 else begin
                        if EXISTS(select top 1 1 from tbl_addbill where forUser = '{0}' and bill_date between '2015-04-01' and '2016-03-31')
                            begin
                            select 'data inserted'/*table[0]*/
                            insert into tbl_addbill(bill_no, B_id, amount, tax, amount_paid, forUser) output inserted.B_id , inserted.amount, inserted.amount_paid /*table[1]*/
                                select bill_no = '{1}-{2}', B_id, COALESCE(sum(amount), 0), COALESCE(sum(tax), 0), COALESCE(sum(amount_paid), 0), '{0}' from tbl_addbill where forUser = '{0}' and bill_date between '2015-04-01' and '2016-03-31' group by B_id;
        (select distinct P_id from tbl_addparty where forUser = '{0}' and P_id not in (select B_id from tbl_addbill where bill_no = '{1}-{2}' and forUser = '{0}' and bill_date is null)/*table[2]*/) 
                        end
                        else
                        select 'no previous year record'
                        end
                        ", userid, (int.Parse(year.ToString()) - 1), year);

Jermaine · Accepted Answer · 2016-10-26 16:03:35Z

0

Are you just trying to insert line feeds?

select 
'First line of string'+char(13)+char(10)
 +'Second line of string'

answered Oct 26, 2016 at 16:03

Jermaine

1031 silver badge8 bronze badges

Collectives™ on Stack Overflow

how write long sql query in string format in multiline

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related