باسلام خدمت دوستان.
من در sql این پروسیجر رو نوشتم:
ALTER proc [dbo].[s_repgeneralfactorlist] @fromdate nvarchar(10), @todate nvarchar(10), @accode int as (select factor.creatdate, case factor.typefactor when 1 then N'خرید' when 2 then N'فروش' when 3 then N'مرجوعی خرید' when 4 then N'مرجوعی فروش'end, factor.factornumber, factor.debtor, factor.creditor, factor.accountbalance from factor where factor.creatdate between @fromdate and @todate and factor.accountcode=@accode union select factordetail.goodsname, case factordetail.typefactor when 1 then N'خرید' when 2 then N'فروش' when 3 then N'مرجوعی خرید' when 4 then N'مرجوعی فروش'end, factordetail.number, factordetail.productcode, factordetail.saleprice, factordetail.total from factor inner join factordetail on factor.factornumber=factordetail.factornumber and factor.accountcode=@accode) union select tblwpcash.creatdate, tblwpcash.status, tblwpcash.getcode, tblwpcash.debtor, tblwpcash.creditor, tblwpcash.accountbalance from tblwpcash where tblwpcash.creatdate between @fromdate and @todate and tblwpcash.accountcode=@accode and tblwpcash.getcode=5 and tblwpcash.status=N'دریافت نقدی' union select tblwpcash.creatdate, tblwpcash.status, tblwpcash.paycode, tblwpcash.debtor, tblwpcash.creditor, tblwpcash.accountbalance from tblwpcash where tblwpcash.creatdate between @fromdate and @todate and tblwpcash.accountcode=@accode and tblwpcash.paycode=6 and tblwpcash.status=N'پرداخت نقدی' union select tblwpcash.creatdate, case tblwpcash.status when N'پرداخت انتقالی حساب معین' then N'پرداخت انتقالی' end, tblwpcash.paycode, tblwpcash.debtor, tblwpcash.creditor, tblwpcash.accountbalance from tblwpcash where tblwpcash.creatdate between @fromdate and @todate and tblwpcash.accountcode=@accode and tblwpcash.paycode=6 and tblwpcash.status=N'پرداخت انتقالی حساب معین' union select tblwpcash.creatdate, case tblwpcash.status when N'پرداخت انتقالی حساب معین' then N'دریافت انتقالی' end, tblwpcash.getcode, tblwpcash.debtor, tblwpcash.creditor, tblwpcash.accountbalance from tblwpcash where tblwpcash.creatdate between @fromdate and @todate and tblwpcash.accountcodet=@accode and tblwpcash.getcode=5 and tblwpcash.status=N'پرداخت انتقالی حساب معین' union select tblcheckpay.creatdate, tblcheckpay.checkstatus, tblcheckpay.paynum, tblcheckpay.debtor, tblcheckpay.creditor, tblcheckpay.accountbalance from tblcheckpay where tblcheckpay.creatdate between @fromdate and @todate and tblcheckpay.accountcode=@accode and tblcheckpay.paynum=8 union select tblcheckpay.creatdate, tblcheckpay.checkstatus, tblcheckpay.paychback, tblcheckpay.debtor, tblcheckpay.creditor, tblcheckpay.accountbalance from tblcheckpay where tblcheckpay.creatdate between @fromdate and @todate and tblcheckpay.accountcode=@accode and tblcheckpay.paychback=10 union (select tblcheckpay.creatdate, tblcheckpay.checkstatus, tblcheckpay.paynum, tblcheckpay.debtor, tblcheckpay.creditor, tblcheckpay.accountbalance from tblcheckpay where tblcheckpay.creatdate between @fromdate and @todate and (tblcheckpay.accountcode=@accode and tblcheckpay.paynum=8) union select tblcheckreceipt.creatdate, case tblcheckreceipt.checkstatus when N'خرج شده' then N'چک پرداختی' end as 'وضعیت', tblcheckreceipt.chspent, tblcheckreceipt.debtor, tblcheckreceipt.creditor, tblcheckreceipt.accountbalance from tblcheckreceipt where tblcheckreceipt.creatdate between @fromdate and @todate and (tblcheckreceipt.accountcoder=@accode and tblcheckreceipt.chspent=11)) union select tblcheckreceipt.creatdate, tblcheckreceipt.checkstatus, tblcheckreceipt.getnum, tblcheckreceipt.debtor, tblcheckreceipt.creditor, tblcheckreceipt.accountbalance from tblcheckreceipt where tblcheckreceipt.creatdate between @fromdate and @todate and tblcheckreceipt.accountcode=@accode and tblcheckreceipt.getnum=7 union select tblcheckreceipt.creatdate, tblcheckreceipt.checkstatus, tblcheckreceipt.getchback, tblcheckreceipt.debtor, tblcheckreceipt.creditor, tblcheckreceipt.accountbalance from tblcheckreceipt where tblcheckreceipt.creatdate between @fromdate and @todate and tblcheckreceipt.accountcode=@accode and tblcheckreceipt.getchback=9 exec s_repgeneralfactorlist '1396/01/01','1396/11/30',10
و در اجرا این رکوردهارو برمیگردونه که درسته:
حالا اگه بخوام تو استیمول کانکشن جدید درست کنم و این پروسیجر رو بعنوان datasource انتخاب کنم باید همه فیلدهایی که تو select های پروسیجر نوشتم رو بعنوان ورودی دیتاسورس تعریف کنم؟ خیلی از فیلدها تکراریه، تو استیمول خطا نداره؟
هیچ کاربری تا کنون از این پست تشکر نکرده است
با ما تماس بگیرید تا در این مسیر همراهتان باشیم :)