درمورد گرفتن یا ایجاد دستور TSQL می تونید اونو به صورت مستقیم وازد کنید یا چون کدتون زیاد هست (اگه ثابته) می تونید به صورت ریسورس به برنامه اضافه کنید
اینم یک روش ساده و غیر اصولی (کد های SQL رو اصلاح نکردم):
private string GetTSQL()
{
string TSQL = @"set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Sell_Main_Update_Ghest]
(
@sm_id_main [bigint],
@sm_cus_id_main [bigint],
@smd_product_id1_main [bigint] ,
@smd_product_id2_main [bigint] ,
@smd_product_id3_main [bigint] ,
@smd_product_id4_main [bigint] ,
@smd_product_id5_main [bigint] ,
@smd_id_main1 [bigint],
@smd_id_main2 [bigint],
@smd_id_main3 [bigint],
@smd_id_main4 [bigint],
@smd_id_main5 [bigint],
@sm_id [bigint],
@sm_cus_id [bigint],
@sm_date [nvarchar](10),
@sm_time [nvarchar](10),
@sm_rebate [bigint] ,
@sm_pardakht [bigint] ,
@smd_id [bigint] ,
@smd_sm_id [bigint] ,
@smd_cus_id [bigint] ,
@smd_product_id1 [bigint] ,
@smd_product_id2 [bigint] ,
@smd_product_id3 [bigint] ,
@smd_product_id4 [bigint] ,
@smd_product_id5 [bigint] ,
@smd_product_price1 [bigint] ,
@smd_product_price2 [bigint] ,
@smd_product_price3 [bigint] ,
@smd_product_price4 [bigint] ,
@smd_product_price5 [bigint] ,
@main_pay_sm_id [bigint],
@pay_ghest_count [bigint],
@pay_ghest_day [bigint],
@pay_sm_id [bigint],
@pay_cus_id [bigint],
@pay_ghest_price [bigint]
)
AS
--************************************************** *******************************
--if exists (select * from [Sell-Main] where [sm_id]=@sm_id)
--return 3
if not exists (select * from [Customers] where [cus_id]=@sm_cus_id)
return 2
if exists (select * from [Sell-Main] where [sm_id]=@sm_id and [sm_id]<>@sm_id_main)
return 3
if @smd_product_id1 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id1)
return 4
if @smd_product_id2 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id2)
return 5
if @smd_product_id3 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id3)
return 6
if @smd_product_id4 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id4)
return 7
if @smd_product_id5 <> 0
if not exists (select * from Products where [product_id]=@smd_product_id5)
return 8
if exists (select * from Peyment Where Peyment.[pay_cus_id]=@sm_cus_id_main and [pay_state]='پرداخت شده')
return 9
--************************************************** *******************************
Update [Sell-Main]
Set
[sm_id]=@sm_id ,
[sm_cus_id]=@sm_cus_id ,
[sm_date]=@sm_date ,
[sm_time]=@sm_time ,
[sm_rebate]=@sm_rebate ,
[sm_pardakht]=@sm_pardakht
where
[sm_id]=@sm_id_main
--************************************************** *******************************
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id
where
[smd_sm_id]=@sm_id_main
--OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY
--OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY OKEY
--************************************************** *******************************
if @smd_product_id1 <> @smd_product_id1_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id1 ,
[smd_product_price]=@smd_product_price1
where
[smd_id]=@smd_id_main1
end
--else
else if @smd_product_id1=0 and @smd_product_id1_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main1
--Insert Condition
if @smd_product_id1<>0 and @smd_product_id1_main=0
begin
declare @smd_id_number1 int
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
INSERT INTO [Sell-Main-Details]
(
smd_id,
smd_sm_id,
smd_cus_id,
smd_product_id,
smd_product_price
)
VALUES
(
@smd_id_number1+1,
@smd_sm_id,
@smd_cus_id,
@smd_product_id1,
@smd_product_price1
)
end
--************************************************** *******************************
if @smd_product_id2 <> @smd_product_id2_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id2 ,
[smd_product_price]=@smd_product_price2
where
[smd_id]=@smd_id_main2
end
--else
else if @smd_product_id2=0 and @smd_product_id2_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main2
--Insert Condition
if @smd_product_id2<>0 and @smd_product_id2_main=0
begin
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
INSERT INTO [Sell-Main-Details]
(
smd_id,
smd_sm_id,
smd_cus_id,
smd_product_id,
smd_product_price
)
VALUES
(
@smd_id_number1+1,
@smd_sm_id,
@smd_cus_id,
@smd_product_id1,
@smd_product_price1
)
end
--************************************************** *******************************
if @smd_product_id3 <> @smd_product_id3_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id3 ,
[smd_product_price]=@smd_product_price3
where
[smd_id]=@smd_id_main3
end
--else
else if @smd_product_id3=0 and @smd_product_id3_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main3
--Insert Condition
if @smd_product_id3<>0 and @smd_product_id3_main=0
begin
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
INSERT INTO [Sell-Main-Details]
(
smd_id,
smd_sm_id,
smd_cus_id,
smd_product_id,
smd_product_price
)
VALUES
(
@smd_id_number1+1,
@smd_sm_id,
@smd_cus_id,
@smd_product_id1,
@smd_product_price1
)
end
--************************************************** *******************************
if @smd_product_id4 <> @smd_product_id4_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id4 ,
[smd_product_price]=@smd_product_price4
where
[smd_id]=@smd_id_main4
end
--else
else if @smd_product_id4=0 and @smd_product_id4_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main4
--Insert Condition
if @smd_product_id4<>0 and @smd_product_id4_main=0
begin
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
INSERT INTO [Sell-Main-Details]
(
smd_id,
smd_sm_id,
smd_cus_id,
smd_product_id,
smd_product_price
)
VALUES
(
@smd_id_number1+1,
@smd_sm_id,
@smd_cus_id,
@smd_product_id1,
@smd_product_price1
)
end
--************************************************** *******************************
if @smd_product_id5 <> @smd_product_id5_main
begin
Update [Sell-Main-Details]
Set
[smd_sm_id]=@smd_sm_id ,
[smd_cus_id]=@smd_cus_id ,
[smd_product_id]=@smd_product_id5 ,
[smd_product_price]=@smd_product_price5
where
[smd_id]=@smd_id_main5
end
--else
else if @smd_product_id5=0 and @smd_product_id5_main<>0
Delete from [Sell-Main-Details] where [smd_sm_id]=@smd_id_main5
--Insert Condition
if @smd_product_id5<>0 and @smd_product_id5_main=0
begin
select @smd_id_number1=max(smd_id) from [Sell-Main-Details]
INSERT INTO [Sell-Main-Details]
(
smd_id,
smd_sm_id,
smd_cus_id,
smd_product_id,
smd_product_price
)
VALUES
(
@smd_id_number1+1,
@smd_sm_id,
@smd_cus_id,
@smd_product_id1,
@smd_product_price1
)
end
--************************************************** *******************************
--declare @Customer_Score int
--set @Customer_Score=(@smd_product_price1 + @smd_product_price2 + @smd_product_price3 + @smd_product_price4 + @smd_product_price5)/1000
--update Customers set [cus_score]=[cus_score]+@Customer_Score where [cus_id]=@sm_cus_id
--************************************************** *******************************
delete from [Peyment] where [pay_sm_id]=@main_pay_sm_id
--++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++
declare @count_checker3 int
select @count_checker3=count(*) from [Peyment]
if @count_checker3<>0
begin
declare @pay_id_number int
select @pay_id_number=max(pay_id) from [Peyment]
end
else
begin
set @pay_id_number=1
end
declare @datetime datetime
select @datetime=getdate()
--DT = Datetime
declare @DT nvarchar(10)
declare @i int
set @i=1
while (@i<=@pay_ghest_count)
begin
select @datetime=dateadd(dd,@pay_ghest_day,@datetime)
SELECT @dt=convert(nvarchar,@datetime,111)
insert into [Peyment]
(
pay_id,
pay_sm_id,
pay_cus_id,
pay_price,
pay_date
)
values
(
@pay_id_number+1,
@pay_sm_id,
@pay_cus_id,
@pay_ghest_price,
@DT
)
set @i=@i+1
select @pay_id_number=max(pay_id) from [Peyment]
end
--************************************************** **************
--************************************************** **************
declare @pp1 int
set @pp1=0
select @pp1=[sm_pardakht] from [Sell-Main] where [sm_id]=@sm_id_main
--***************************************
declare @Customer_Score_old int
declare @Customer_Score_new int
set @Customer_Score_old=0
set @Customer_Score_new=0
set @Customer_Score_old=@pp1/1000
set @Customer_Score_new=@sm_pardakht/1000
--***************************************
--تغییرات امتیاز و تعداد خرید مشتری قدیم و جدید
if @sm_cus_id_main <> @sm_cus_id
begin
update Customers set [cus_score]=[cus_score]-@Customer_Score_old ,[cus_sell_count]=[cus_sell_count]-1, [cus_ref_count]=[cus_ref_count]-1 where [cus_id]=@sm_cus_id_main
update Customers set [cus_score]=[cus_score]+@Customer_Score_new ,[cus_sell_count]=[cus_sell_count]+1 , [cus_ref_count]=[cus_ref_count]+1 where [cus_id]=@sm_cus_id
end
--اضافه و کم کردن امتیاز به معرف قدیم و جدید
declare @pc_old int
declare @pc_new int
select @pc_old=presenter_code from customers where cus_id=@sm_cus_id_main
select @pc_new=presenter_code from customers where cus_id=@sm_cus_id
update Customers set [cus_score]=[cus_score]-@Customer_Score_old where [cus_id]=@pc_old
update Customers set [cus_score]=[cus_score]+@Customer_Score_new where [cus_id]=@pc_new
--################################################## ###########################################
-- کم کردن محصول از تعداد کالای 1
if @smd_product_id1 <> @smd_product_id1_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id1_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id1
end
-- کم کردن محصول از تعداد کالای 2
if @smd_product_id2 <> @smd_product_id2_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id2_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id2
end
-- کم کردن محصول از تعداد کالای 3
if @smd_product_id3 <> @smd_product_id3_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id3_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id3
end
-- کم کردن محصول از تعداد کالای 4
if @smd_product_id4 <> @smd_product_id4_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id4_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id4
end
-- کم کردن محصول از تعداد کالای 5
if @smd_product_id5 <> @smd_product_id5_main
begin
update Products set [product_count]=[product_count]+1 where [product_id]=@smd_product_id5_main
update Products set [product_count]=[product_count]-1 where [product_id]=@smd_product_id5
end
--************************************************** *******************************
return 1";
return TSQL;
}
پاسخ با نقل قول