sqlserver 自定义、删除、执行 函数和存储过程

自定义存储过程：

if (object_id('p_wm_get_dynstore', 'P') is not null)    drop proc p_wm_get_dynstoregocreate procedure p_wm_get_dynstore(@stock_id int =0,    @useful_date int =0,    @i_state int =0,    @q_state int =0,    @is_batch bit = 0,    @product_code varchar(250) ='')-- =============================================-- Author:XXX-- Create date: 2016-02-18-- Description:XXXXXXX-- =============================================asbegindeclare @ifstr varchar(2000) = ''declare @newifstr varchar(2000)declare @pro_id intdeclare @sqlstr varchar(2000)if @stock_id =0 or @stock_id is null RaisError('传入参数错误:stock_id必填',16,1)if @useful_date <> 0 and @useful_date is not null Set @ifstr = @ifstr + ' and DATEDIFF(day, makedate, getdate()) <=' + str(@useful_date) if @i_state <> 0 and @i_state is not null  Set @ifstr = @ifstr + 'and v_inventorystate_id=' + str(@i_state) if @q_state <> 0 and @q_state is not null  Set @ifstr = @ifstr + ' and v_qualitystate_id=' + str(@q_state)if ISNULL( @product_code,'') != ''Set @pro_id = (SELECT a.id  FROM bs_product a WHERE a.default_code=@product_code)if @pro_id <> 0 and @pro_id is not null Set @ifstr = @ifstr + ' and product_id=' + str(@pro_id) if ISNULL( @ifstr,'') != ''Set @newifstr =  'stock_location_id in (SELECT lot_stock_id  FROM bs_stock_warehouse WHERE id=' + str(@stock_id) + ')'+ @ifstrelseSet @newifstr =  'stock_location_id in (SELECT lot_stock_id  FROM bs_stock_warehouse WHERE id='+ str(@stock_id) + ')'if @is_batch <> 0 and @is_batch is not nullSet @sqlstr = 'select p.name_template,p.default_code,p.specs,u.name,d.qty,d.lockqty,(d.qty-d.lockqty)as useqty,l.name as ''库位'' ,w.name as w_name,b.name as b_name,c.name as c_name from( SELECT SUM(qty) as qty,SUM(lockqty) as lockqty,SUM(useableqty) as useableqty,product_id,stock_location_id,v_qualitystate_id,v_inventorystate_id,uom_id FROM wm_dynstore WHERE '+ @newifstr +' GROUP BY product_id,stock_location_id,v_qualitystate_id,v_inventorystate_id,uom_id )as d left join bs_product p on p.id = d.product_idleft join bs_product_uom u on u.id = d.uom_idleft join bs_stock_location l on l.id = d.stock_location_idleft join bs_stock_warehouse w on w.id = l.stock_warehouse_idleft join bs_publictype_d b on b.id = d.v_inventorystate_idleft join bs_publictype_d c on c.id = d.v_qualitystate_id'elseSet @sqlstr = 'select p.name_template,p.default_code ,p.specs ,u.name ,d.qty ,d.lockqty,(d.qty-d.lockqty) as useqty,l.name as ''库位'' ,w.name as w_name,b.name as b_name,c.name as c_name from( SELECT qty,lockqty,useableqty,product_id,stock_location_id,v_qualitystate_id,v_inventorystate_id FROM wm_dynstore WHERE '+ @newifstr +' )as d left join bs_product p on p.id = d.product_idleft join bs_product_uom u on u.id = d.uom_idleft join bs_stock_location l on l.id = d.stock_location_idleft join bs_stock_warehouse w on w.id = l.stock_warehouse_idleft join bs_publictype_d b on b.id = d.v_inventorystate_idleft join bs_publictype_d c on c.id = d.v_qualitystate_id;'print '123'print @sqlstrendExecute(@sqlstr)goexec p_wm_get_dynstore 5621,0,0,0,1;exec p_wm_get_dynstore 5621,5,0,0,1,'2.40010';

自定义函数：

---------------------------------------查询截止日期的入库数量-----------------------------------if OBJECT_ID('f_wm_get_stockin','if')is not nulldrop function f_wm_get_stockingo-------- =============================================-------- Author:XXX-------- Create date: 2016-03-03-------- Description:XXXXXXXXXXXXXXXXX-------- =============================================create function f_wm_get_stockin(@stock_id int,@company_id int, @begindate varchar(200),@enddate varchar(200),@product_code varchar(200),@stock_state varchar(250), @quality_state varchar(250))returns tableas return (SELECT d.res_company_id,d.stock_warehouse_id,d.product_id,SUM(s.qty) as qty, SUM(s.qty*p.often_switch) as qty_often,SUM(s.qty*d.price_unit) as amount, SUM(s.qty*d.price_unit)/SUM(s.qty) as price_unit,SUM(s.qty*d.price_unit)/SUM(s.qty*p.often_switch) as price_oftenFROM wm_dynstore dLEFT JOIN wm_storemv s ON s.mgin_id = d.idLEFT JOIN bs_product p on p.id = d.product_idWHERE (@company_id <= 0  or d.res_company_id=@company_id) and (@stock_id <= 0  or d.stock_warehouse_id=@stock_id) and (@product_code is null  or p.default_code in (@product_code)) and (@stock_state is null  or d.v_inventorystate_id in (@stock_state)) and (@quality_state is null  or d.v_qualitystate_id in (@quality_state)) AND s.wtime_d >= @begindate AND s.wtime_d <= @enddateGROUP BY d.res_company_id,d.stock_warehouse_id,d.product_id)goselect * from f_wm_get_stockin(8,1,'2015-12-01','2015-12-31','','','');