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

自定义存储过程：

if (object_id('p_wm_get_dynstore','P') is not null)
    drop proc p_wm_get_dynstore
go
create 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
-- =============================================
as
begin
	declare @ifstr varchar(2000) = ''
	declare @newifstr varchar(2000)
	declare @pro_id int
	declare @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) + ')'+ @ifstr
	else
		Set @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 null
		Set @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,uom_id )as d left join bs_product p on p.id = d.product_id
left join bs_product_uom u on u.id = d.uom_id
left join bs_stock_location l on l.id = d.stock_location_id
left join bs_stock_warehouse w on w.id = l.stock_warehouse_id
left join bs_publictype_d b on b.id = d.v_inventorystate_id
left join bs_publictype_d c on c.id = d.v_qualitystate_id'

	else
		Set @sqlstr = 'select p.name_template,(d.qty-d.lockqty) as useqty,c.name as c_name from
( SELECT qty,lockqty,useableqty,v_inventorystate_id FROM wm_dynstore WHERE '+ @newifstr +' )as d 
left join bs_product p on p.id = d.product_id
left join bs_product_uom u on u.id = d.uom_id
left join bs_stock_location l on l.id = d.stock_location_id
left join bs_stock_warehouse w on w.id = l.stock_warehouse_id
left join bs_publictype_d b on b.id = d.v_inventorystate_id
left join bs_publictype_d c on c.id = d.v_qualitystate_id;'
print '123'
print @sqlstr
end
Execute(@sqlstr)
go


exec p_wm_get_dynstore 5621,1;
exec p_wm_get_dynstore 5621,5,1,'2.40010';

自定义函数：

---------------------------------------查询截止日期的入库数量-----------------------------------
if OBJECT_ID('f_wm_get_stockin','if')is not null
	drop function f_wm_get_stockin
go
-------- =============================================
-------- 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 table
as 
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_often
	FROM wm_dynstore d
	LEFT JOIN wm_storemv s ON s.mgin_id = d.id
	LEFT JOIN bs_product p on p.id = d.product_id
	WHERE (@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 <= @enddate
	GROUP BY d.res_company_id,d.product_id	
)
go
select * from f_wm_get_stockin(8,'2015-12-01','2015-12-31','','');

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!