典型存儲過程

1、1. 進銷存存儲過程1.1 TopExStoreByID簡要說明：給出出庫單號export_number，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE dbo.TopExStoreByID - Add the parameters for the stored procedure hereexport_number varchar(20),company_name varc

2、har(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET NOCOUNT ON; - Insert statements for procedure heredeclare sql varchar(1000) set sql='select top 1 export_number,storename,isnull(company_name,'''') as

3、 company_name,shopname,isnull(exporttime,'''') as exporttime,loadtime,username,isnull(loadtime_update,'''') as loadtime_update,isnull(username_update,'''') as username_update,isnull(remark,'''') as remark from t_export_store where expor

4、t_number like ''' +export_number +''' and (company_name =''' +company_name +''' or company_name in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') and export_number not in (select top &

5、#39; +cast(current_record as varchar)+ ' export_number from t_export_store where export_number like ''' +export_number +'''and (company_name =''' +company_name +''' or company_name in (select distinct company_name from t_sale_company where up_compa

6、ny=''' +company_name +''') ) for xml raw'EXEC (sql)END1.2 TopOrderPurchase簡要說明：給出采購單號purchase_order_number，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE dbo.TopOrderPurchase - Add the

7、 parameters for the stored procedure herepurchase_order_number varchar(20),company_name varchar(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET NOCOUNT ON; - Insert statements for procedure heredeclare sql varchar(1000) se

8、t sql='select top 1 purchase_order_number,storename,company_name,isnull(ordertime,'''') as ordertime,loadtime,username,isnull(loadtime_update,'''') as loadtime_update,isnull(username_update,'''') as username_update,isnull(remark,''''

9、;) as remark from t_order_purchase where purchase_order_number like ''' +purchase_order_number +''' and (company_name =''' +company_name +''' or company_name in (select distinct company_name from t_sale_company where up_company=''' +company

10、_name +''') and purchase_order_number not in (select top ' +cast(current_record as varchar) +' purchase_order_number from t_order_purchase where purchase_order_number like ''' +purchase_order_number +''' and (company_name =''' +company_name +&#

11、39;'' or company_name in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') ) for xml raw'EXEC (sql)END1.3 TopOrderStoreInByInputID簡要說明：給出入庫單號purchase_input_number，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信

12、息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE dbo.TopOrderStoreInByInputID - Add the parameters for the stored procedure herepurchase_input_number varchar(20),company_name varchar(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- inte

13、rfering with SELECT statements.SET NOCOUNT ON; - Insert statements for procedure heredeclare sql varchar(1000) set sql='select top 1 purchase_input_number,isnull(purchase_order_number,'''') as purchase_order_number,isnull(company_name,'''') as company_name,storena

14、me,isnull(inputtime,'''') as inputtime,loadtime,username,isnull(loadtime_update,'''') as loadtime_update,isnull(username_update,'''') as username_update,isnull(remark,'''') as remark from t_input_purchase where purchase_input_number lik

15、e ''' +purchase_input_number +''' and (company_name =''' +company_name +''' or company_name in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') and purchase_input_number not in (select t

16、op ' +cast(current_record as varchar) +' purchase_input_number from t_input_purchase where purchase_input_number like ''' +purchase_input_number +''' and (company_name =''' +company_name +''' or company_name in (select distinct company_name fro

17、m t_sale_company where up_company=''' +company_name +''') ) for xml raw'EXEC (sql)END1.4 TopOrderStoreInByOrderID簡要說明：給出采購單號purchase_order_number，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE P

18、ROCEDURE dbo.TopOrderStoreInByOrderID - Add the parameters for the stored procedure herepurchase_order_number varchar(20),company_name varchar(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET NOCOUNT ON; - Insert statements

19、 for procedure heredeclare sql varchar(1000) set sql='select top 1 purchase_order_number,storename,company_name,loadtime,username,isnull(loadtime_update,'''') as loadtime_update,isnull(username_update,'''') as username_update from t_order_purchase where purchase_o

20、rder_number like ''' +purchase_order_number +''' and (company_name =''' +company_name +''' or company_name in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') and purchase_order_number n

21、ot in (select distinct purchase_order_number from t_input_purchase where purchase_order_number<>'''') and purchase_order_number not in (select top ' +cast(current_record as varchar)+ ' purchase_order_number from t_order_purchase where purchase_order_number like '

22、9;' +purchase_order_number +''' and purchase_order_number not in (select distinct purchase_order_number from t_input_purchase where purchase_order_number<>'''')' +'and (company_name =''' +company_name +''' or company_name in (sele

23、ct distinct company_name from t_sale_company where up_company=''' +company_name +''') ) for xml raw'EXEC (sql)END1.5 TopOrderStoreReByReturnID簡要說明：給出采購?fù)素泦翁杙urchase_return_number，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSE

24、T QUOTED_IDENTIFIER ONGOCREATE PROCEDURE dbo.TopOrderStoreReByReturnID - Add the parameters for the stored procedure herepurchase_return_number varchar(20),company_name varchar(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.S

25、ET NOCOUNT ON; - Insert statements for procedure heredeclare sql varchar(1000) set sql='select top 1 purchase_return_number,isnull(purchase_order_number,''' +''') as purchase_order_number,isnull(company_name,'''') as company_name,isnull(storename,''

26、;'') as storename,isnull(returntime,'''') as returntime,loadtime,username,isnull(loadtime_update,'''') as loadtime_update,isnull(username_update,'''') as username_update,isnull(remark,'''') as remark from t_return_purchase where

27、 purchase_return_number like ''' +purchase_return_number +''' and (company_name =''' +company_name +''' or company_name in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') and purchase_r

28、eturn_number not in (select top ' +cast(current_record as varchar)+ ' purchase_return_number from t_return_purchase where purchase_return_number like ''' +purchase_return_number +''' and (company_name =''' +company_name +''' or company_name in

29、(select distinct company_name from t_sale_company where up_company=''' +company_name +''') ) for xml raw'EXEC (sql)END1.6 TopReShopByID簡要說明：給出店鋪退貨單號shop_return_number，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_ID

30、ENTIFIER ONGOCREATE PROCEDURE dbo.TopReShopByID - Add the parameters for the stored procedure hereshop_return_number varchar(20),company_name varchar(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET NOCOUNT ON; - Insert sta

31、tements for procedure heredeclare sql varchar(1000) set sql='select top 1 shop_return_number,storename,isnull(company_name,'''') as company_name,shopname,isnull(returntime,'''') as returntime,loadtime,username,isnull(loadtime_update,'''') as loadti

32、me_update,isnull(username_update,'''') as username_update,isnull(remark,'''') as remark from t_return_shop where shop_return_number like ''' +shop_return_number +''' and (company_name =''' +company_name +''' or company_n

33、ame in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') and shop_return_number not in (select top ' +cast(current_record as varchar)+ ' shop_return_number from t_return_shop where shop_return_number like ''' +shop_

34、return_number +'''and (company_name =''' +company_name +''' or company_name in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') ) for xml raw'EXEC (sql)END1.7 TopFlitStoreByFlitID簡要說明：給出倉庫調(diào)撥單號st

35、ore_flitting_number，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE dbo.TopFlitStoreByFlitID store_flitting_number varchar(20),company_name_begin varchar(30),company_name_end varchar(30),current_record intASBEGINS

36、ET NOCOUNT ON;declare sql varchar(1500) set sql='select top 1 store_flitting_number,storename_begin,isnull(company_name_begin,'''') as company_name_begin,storename_end,isnull(company_name_end,'''') as company_name_end,flittingtime,loadtime,username,isnull(loadtime

37、_update,'''') as loadtime_update,isnull(username_update,'''') as username_update,isnull(remark,'''') as remark from t_flitting_store where store_flitting_number like ''' +store_flitting_number +''' and (company_name_begin ='

38、'' +company_name_begin +''' or company_name_begin in (select distinct company_name from t_sale_company where up_company=''' +company_name_begin +''') or company_name_end =''' +company_name_end +''' or company_name_end in (select dis

39、tinct company_name from t_sale_company where up_company=''' +company_name_end +''') and store_flitting_number not in (select top ' +cast(current_record as varchar)+ ' store_flitting_number from t_flitting_store where store_flitting_number like ''' +store_f

40、litting_number +''' and (company_name_begin =''' +company_name_begin +''' or company_name_begin in (select distinct company_name from t_sale_company where up_company=''' +company_name_begin +''') or company_name_end =''' +compan

41、y_name_end +''' or company_name_end in (select distinct company_name from t_sale_company where up_company=''' +company_name_end +''') for xml raw'EXEC (sql)END1.8 TopFlitStoreByFromStore簡要說明：倉庫調(diào)撥中，給出從倉庫名稱storename_begin，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，

42、即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE dbo.TopFlitStoreByFromStore - Add the parameters for the stored procedure herestorename_begin varchar(20),company_name_begin varchar(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra res

43、ult sets from- interfering with SELECT statements.SET NOCOUNT ON; - Insert statements for procedure heredeclare sql varchar(1000) set sql='select top 1 store_flitting_number,storename_begin,isnull(company_name_begin,'''') as company_name_begin,storename_end,isnull(company_name_en

44、d,'''') as company_name_end,flittingtime,loadtime,username,isnull(loadtime_update,'''') as loadtime_update,isnull(username_update,'''') as username_update,isnull(remark,'''') as remark from t_flitting_store where storename_begin like &#

45、39;'' +storename_begin +''' and (company_name_begin =''' +company_name_begin +''' or company_name_begin in (select distinct company_name from t_sale_company where up_company=''' +company_name_begin +''') and store_flitting_number no

46、t in (select top ' +cast(current_record as varchar)+ ' store_flitting_number from t_flitting_store where storename_begin like ''' +storename_begin +'''and (company_name_begin =''' +company_name_begin +''' or company_name_begin in (select distin

47、ct company_name from t_sale_company where up_company=''' +company_name_begin +''') ) for xml raw'EXEC (sql)END1.9 TopFlitStoreByToStore簡要說明：倉庫調(diào)撥中，給出到倉庫名稱storename_end，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_ID

48、ENTIFIER ONGOCREATE PROCEDURE dbo.TopFlitStoreByToStore - Add the parameters for the stored procedure herestorename_end varchar(20),company_name_end varchar(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET NOCOUNT ON; - Ins

49、ert statements for procedure heredeclare sql varchar(1000) set sql='select top 1 store_flitting_number,storename_begin,isnull(company_name_begin,'''') as company_name_begin,storename_end,isnull(company_name_end,'''') as company_name_end,flittingtime,loadtime,usern

50、ame,isnull(loadtime_update,'''') as loadtime_update,isnull(username_update,'''') as username_update,isnull(remark,'''') as remark from t_flitting_store where storename_end like ''' +storename_end +''' and (company_name_end ='

51、;'' +company_name_end +''' or company_name_end in (select distinct company_name from t_sale_company where up_company=''' +company_name_end +''') and store_flitting_number not in (select top ' +cast(current_record as varchar)+ ' store_flitting_numbe

52、r from t_flitting_store where storename_begin like ''' +storename_end +'''and (company_name_end =''' +company_name_end +''' or company_name_end in (select distinct company_name from t_sale_company where up_company=''' +company_name_end +

53、9;'') ) for xml raw'EXEC (sql)END1.10 TopFlitShopByFlitID簡要說明：店鋪調(diào)撥中，給出店鋪調(diào)撥單shop_flitting_number，進行模糊查詢，查詢當(dāng)前記錄current_record的信息，并且加上權(quán)限條件，即只能查詢本銷售公司或下一級銷售公司的信息USE SaleMSGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO- =- Author:<Author,Name>- Create date: <Create Date,>- Descri

54、ption:<Description,>- =CREATE PROCEDURE dbo.TopFlitShopByFlitID - Add the parameters for the stored procedure hereshop_flitting_number varchar(20),company_name varchar(30),current_record intASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET

55、NOCOUNT ON; - Insert statements for procedure heredeclare sql varchar(1500) set sql='select top 1 a.shop_flitting_number,a.shopname_begin,a.shopname_end,isnull(a.flittingtime,'''') as flittingtime,a.loadtime,a.username,isnull(a.loadtime_update,'''') as loadtime_up

56、date,isnull(a.username_update,'''') as username_update,isnull(a.remark,'''') as remark from t_flitting_shop a,t_shop b where a.shop_flitting_number like ''' +shop_flitting_number +''' and a.shopname_begin=b.shopname and (pany_name =''&#

57、39; +company_name +''' or pany_name in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') and a.shop_flitting_number not in (select top ' +cast(current_record as varchar)+ ' a.shop_flitting_number from t_flitting_sho

58、p a,t_shop b where a.shop_flitting_number like ''' +shop_flitting_number +''' and a.shopname_begin=b.shopname and (pany_name =''' +company_name +''' or pany_name in (select distinct company_name from t_sale_company where up_company=''' +company_name +''') order by a.shop_flitting_number asc) order by a.shop_flitting_number asc for xml raw'EXEC (sql)END1.11 TopFlitShopByFromShop簡要說明：店鋪調(diào)撥中，給出從店鋪名稱shopname_begin，進行模糊查詢，查詢當(dāng)前記錄cu