进销存开发用到的sql语句

CREATE TABLE jxc_StockIn(id int IDENTITY (1, 1), 操作类型 varchar(20), 产品名称 varchar(100), 产品规格 varchar(20),
颜色 varchar(15), 尺码 varchar(20), 进货数量 int ， 进货单价 float, 供应商 varchar(50), 入货库位 varchar(20), 备注 varchar(1000))

create procedure proc_jxc_insStocIn
@操作类型 varchar(20),
@产品名称 varchar(100),
@产品规格 varchar(20),
@颜色 varchar(15),
 @尺码 varchar(20),
@进货数量 int,
@进货单价 float,
@总金额 float,
@供应商 varchar(100),
/*@店铺名 30*/
@仓库 varchar(20),
@货架 varchar(20),
@备注 varchar(1000),
@操作员 varchar(20)

  as
 insert jxc_stockIn
(操作类型, 产品名称, 产品规格, 颜色, 尺码, 进货数量, 进货单价, 总金额, 供应商, 仓库, 货架, 备注,
                操作员)values(

@操作类型, @产品名称, @产品规格, @颜色, @尺码, @进货数量, @进货单价, @总金额, @供应商, @仓库, @货架, @备注,
                @操作员)

/*更新库存表*/
update jxc_stock set 库存=库存+@进货数量 where jxc_stock.产品规格=@产品规

ALTER PROCEDURE proc_jcx_StockOut
 @orderID varchar(25)
 /*已出库，标记为待发货，同时要写到jxc出库里*/
AS
  update shou set 订单状态='等待发货' where (SUBSTRING(订单号, 0, CHARINDEX('_', 订单号))=@orderID)
 
  select  * into jxc_stockOut from shou where  (SUBSTRING(订单号, 0, CHARINDEX('_', 订单号)) =@orderID)

USE [s223706]
GO
/****** 对象:  StoredProcedure [dbo].[proc_test]    脚本日期: 12/13/2011 10:26:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_test]
 as
declare @orderID varchar(30)
declare @iCount int
declare @i int
declare @id int
declare @_orderID varchar(30)
declare @产品数量 int
set @orderID='124744904'
set @i=1
/*get the same OrderID rows shou table shou rename to stockout*/
set @iCount=(select count(*) from shou where (SUBSTRING(订单号, 0, CHARINDEX('_', 订单号)) =@orderID))
while(@i<=@iCount)
 begin
--select top(@i-1) shou.id from shou
 select  top(1) @id= id,@_orderID=订单号,@产品数量=产品数量   from shou where (SUBSTRING(订单号, 0, CHARINDEX('_', 订单号)) =@orderID) and
 id not in (select top(@i-1) id from shou where (SUBSTRING(订单号, 0, CHARINDEX('_', 订单号)) =@orderID)) 
print(@_orderID)
 print(@产品数量)
set @i=@i+1
end

出库完成后更新收支表？