如何在多行中以字符串格式编写长SQL查询

本文关键字：格式 查询 SQL 字符串 | 更新日期: 2023-09-27 18:14:58

到目前为止，我所做的是将@放在字符串的起始点，如下所示，它工作得很好:

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
                        ";

但是我需要做的是，它给了我错误:

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
                            ";

如何在多行中以字符串格式编写长SQL查询

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);

你只是想插入换行吗?

select 
'First line of string'+char(13)+char(10)
 +'Second line of string'
相关文章：