sp.sql

来自「foxpro连接sqlserver的例子」· SQL 代码 · 共 367 行

-- 商品销售发票
-- 表.存储过程
-- 红虎于2002.12.14

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DeleteInvoiceNo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_DeleteInvoiceNo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DeleteItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_DeleteItem]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetInvoiceDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetInvoiceDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetInvoiceInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetInvoiceInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetSystemDate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetSystemDate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_NewInvoiceNo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_NewInvoiceNo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateItemList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateItemList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateItemMast]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateItemMast]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateSystemData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateSystemData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_isExistInvoiceNo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_isExistInvoiceNo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_isExistItemno]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_isExistItemno]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



/****** Object:  Stored Procedure dbo.usp_DeleteInvoiceNo    Script Date: 02-12-5 15:40:53 ******/
CREATE PROCEDURE usp_DeleteInvoiceNo
@tcInvoiceNo char(10)
-- With Encryption
 AS

Declare @intInvoiceID int
Declare @intReturn int
Set @intReturn = 0
Begin Transaction
	Select @intInvoiceID = ID From InvoiceOrder Where RTrim(InvoiceNo) = RTrim(@tcInvoiceNo)
	If @@RowCount > 0 
		Begin
		-- 含有发票号码时
		Delete From InvoiceOrder Where RTrim(InvoiceNo) = RTrim(@tcInvoiceNo)
		Delete From InvoiceItemList Where InvoiceID = @intInvoiceID
		Set @intReturn = 1
		End
	Else
		Begin
		-- 不含有时
		Set @intReturn = 2
		End
Commit Transaction

SELECT case @intReturn 
		When 0 Then '失败'
		When 1 Then '成功'
		When 2 Then '无票'
		end as result

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- 删除某产品
-- 红虎于2002.11.29
-- 返回错误信息：
--	1 = 正常删除成功
--	2 = 不存在被删除的产品
--	3 = 产品已经被使用。

CREATE PROCEDURE usp_DeleteItem
@tnItemID int
-- With Encryption
 AS
Declare @intReturn int
Set @intReturn	= 0

Begin Transaction
	Select ID From ItemMast Where ID = @tnItemID
	If @@RowCount > 0
 		-- 表示存在产品
		Begin
			Select Top 1 ID From InvoiceItemList Where ItemID = @tnItemID
			If @@RowCount > 0	-- 表示 存在有正在使用的产品，就不能删除产品
				Set @intReturn = 3
			Else
				Begin
				Delete From Itemmast Where ID = @tnItemID
				Set @intReturn = 1
				End
		End
	Else
		Set @intReturn	= 2
Commit
Select @intReturn as result
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_GetInvoiceDetail
@tcInvoiceNo char(10)
-- With Encryption
 AS

Select * From View_InvoiceDetail
	Where rTrim(InvoiceNo) = rTrim(@tcInvoiceNo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_GetInvoiceInfo
@tcInvoiceNo char(10)
-- With Encryption
 AS

Select * From View_Order
	Where rTrim(InvoiceNo) = rTrim(@tcInvoiceNo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_GetSystemDate 
-- With Encryption
AS
Select GetDate() as SysDate
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_NewInvoiceNo 
@tcInvoiceNo char(10)
-- With Encryption
 AS

Begin Transaction
	Select Top 1 ID From InvoiceOrder Where rTrim(InvoiceNo) = rTrim(@tcInvoiceNo)
	If @@RowCount = 0
	Begin
		Insert Into InvoiceOrder (InvoiceNo) values (@tcInvoiceNo)
	End
	Select * From SystemData Where item='invno' and [value] > @tcInvoiceNo
	If @@RowCount = 0
	Begin
		Update SystemData Set [value] = RTrim(@tcInvoiceNo) Where Item='invno'
	End
Commit
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_UpdateItemList
@tnItemListID	int,
@tcInvoiceNo	char(10),
@tcItemno	char(10),
@tnItemPrice	money,
@tnItemQty	money,
@tnItemAmount	money
-- With Encryption
AS

Declare @intInvoiceID 	int
Declare @intItemID	int
Declare @intItemListID	int
Set @intInvoiceID	= 0
Set @intItemID		= 0
Set @intItemListID	= 0

Select @intInvoiceID = ID From InvoiceOrder Where RTrim(InvoiceNo) = RTrim(@tcInvoiceNo)
if @intInvoiceID > 0
Begin
	Select @intItemID = ID From Itemmast Where RTrim(ItemNo) = RTrim(@tcItemno)
	if @intItemID > 0
	Begin
		IF @tnItemListID > 0
			-- 更新
			Begin
				Update InvoiceItemList Set 
					InvoicePrice	= @tnItemPrice,
					InvoiceQty	= @tnItemQty,
					InvoiceAmount	= @tnItemAmount
				Where ID = @tnItemListID
			End
		Else
			-- 插入
			Begin
				Insert into InvoiceItemList
					(InvoiceID,ItemID,InvoicePrice,InvoiceQty,InvoiceAmount)
				values
					(@intInvoiceID,@intItemID,@tnItemPrice,@tnItemQty,@tnItemAmount)
			End
	End
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_UpdateItemMast
@tnItemTypeID int,
@tcItemNo char(10),
@tcItemFullName varchar(50),
@tcItemModel	varchar(50),
@tcItemFrom	varchar(50),
@tnItemPrice	money,
@tcItemUnit	char(4)
-- With Encryption
 AS

Select 1 From ItemMast Where rTrim(Itemno) = rTrim(@tcItemNo)
If @@RowCount > 0
Begin
	Update ItemMast Set
		ItemTypeID	= @tnItemTypeID,
		ItemFullName	= rtrim(@tcItemFullName),
		ItemModel	= rtrim(@tcItemModel),
		ItemFrom	= rtrim(@tcItemFrom),
		ItemPrice	= @tnItemPrice,
		ItemUnit	= rtrim(@tcItemUnit)
	Where rTrim(Itemno) = rTrim(@tcItemNo)
End
Else
Begin
	Insert Into ItemMast 
		(ItemTypeID,Itemno,ItemFullName,ItemModel,ItemFrom,ItemPrice,ItemUnit)
	values
		(@tnItemTypeID,@tcItemno,@tcItemFullName,@tcItemModel,@tcItemFrom,@tnItemPrice,@tcItemUnit)
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_UpdateSystemData
@tcItem char(20),
@tcValue char(20)
-- With Encryption
 AS
Begin Transaction
Select ID From SystemData Where rTrim(item) = rTrim(@tcItem)
IF @@RowCount > 0
	Update SystemData Set [value] = rTrim(@tcValue) Where rTrim(item) = rTrim(@tcItem)
Else
	Insert Into SystemData (Item,[value]) values (rTrim(@tcItem),rTrim(@tcValue))

Commit
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_isExistInvoiceNo
@tcInvoiceNo char(10)
-- With Encryption
 AS

Select Top 1 ID as isExist FROM InvoiceOrder Where rTrim(InvoiceNo) = rTrim(@tcInvoiceNo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_isExistItemno
@tcItemno char(10)
-- With Encryption
 AS

Select Top 1 ID as isExist From Itemmast Where RTrim(Itemno) = RTrim(@tcItemno)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO