sql.sql

很久 宏观经济韩国锦湖国家交换机海关核发个回复光滑

		commit
	end' 
END
GO

/****** 对象:  StoredProcedure [dbo].[RTLS_Delete]    脚本日期: 04/13/2007 15:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RTLS_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--
--用途：添加警告日至
--时间：2007年4月10日
-------------------------------------
CREATE PROCEDURE [dbo].[RTLS_Delete]
@ID int
AS
begin tran
DELETE FROM [dbo].[RTLS]
      WHERE [ID] = @ID
if @@error!=0
	begin
		rollback
	end
else
	begin
		commit
	end' 
END

/****** 对象:  StoredProcedure [dbo].[Jail_Create]    脚本日期: 04/13/2007 15:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Jail_Create]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--
--用途：添加警告日至
--时间：2007年4月10日
-------------------------------------
CREATE PROCEDURE [dbo].[Jail_Create]
@Class int,
@Number nvarchar(50),
@Name nvarchar(50),
@Police int,
@Remarks nvarchar(max),
@Jail nvarchar(50)
AS
begin tran
INSERT INTO [dbo].[Jail] ([Class],[Number],[Name],[Police],[Remarks],[Jail])
VALUES (@Class,,@Number,@Name,@Police,@Remarks,@Jail)
if @@error!=0
	begin
		rollback
	end
else
	begin
		commit
	end' 
END
GO

/****** 对象:  StoredProcedure [dbo].[Jail_UpDate]    脚本日期: 04/13/2007 15:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Jail_UpDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--
--用途：添加警告日至
--时间：2007年4月10日
-------------------------------------
CREATE PROCEDURE [dbo].[Jail_Create]
@ID int,
@Class int,
@Number nvarchar(50),
@Name nvarchar(50),
@Police int,
@Remarks nvarchar(max),
@Jail nvarchar(50)
AS
begin tran
UPDATE [dbo].[Jail]
SET [Class] = @Class
        ,[Number] = @Number
        ,[Name] = @Name
        ,[Police] = @Police
        ,[Remarks] = @Remarks
        ,[Jail] = @Jail
WHERE [ID] = @ID
if @@error!=0
	begin
		rollback
	end
else
	begin
		commit
	end' 
END
GO

/****** 对象:  StoredProcedure [dbo].[Jail_Delete]    脚本日期: 04/13/2007 15:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Jail_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--
--用途：删除监区信息
--时间：2007年4月10日
-------------------------------------
CREATE PROCEDURE [dbo].[Jail_Delete]
@ID int
AS
begin tran
DELETE FROM [dbo].[Jail]
      WHERE [ID] = @ID
if @@error!=0
	begin
		rollback
	end
else
	begin
		commit
	end' 
END
GO

/****** 对象:  Table [dbo].[Transfer]    脚本日期: 04/13/2007 15:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transfer]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Transfer](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Prisoner] [int] NOT NULL CONSTRAINT [DF_DDJL_FRID]  DEFAULT ((0)),
	[Belong] [int] NOT NULL CONSTRAINT [DF_DDJL_YSJQ]  DEFAULT ((0)),
	[Adjust] [int] NOT NULL CONSTRAINT [DF_DDJL_DRJQ]  DEFAULT ((0)),
	[Time] [datetime] NOT NULL CONSTRAINT [DF_DDJL_DRSJ]  DEFAULT (getdate()),
	[Reason] [nvarchar](200) NOT NULL CONSTRAINT [DF_DDJL_DRYY]  DEFAULT ('0'),
	[Police] [int] NOT NULL CONSTRAINT [DF_DDJL_FZR]  DEFAULT ((0)),
	[Remarks] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Transfer', N'COLUMN',N'ID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自动编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Transfer', @level2type=N'COLUMN',@level2name=N'ID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Transfer', N'COLUMN',N'Prisoner'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'犯人编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Transfer', @level2type=N'COLUMN',@level2name=N'Prisoner'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Transfer', N'COLUMN',N'Belong'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'原属监区' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Transfer', @level2type=N'COLUMN',@level2name=N'Belong'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Transfer', N'COLUMN',N'Adjust'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'调入监区' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Transfer', @level2type=N'COLUMN',@level2name=N'Adjust'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Transfer', N'COLUMN',N'Time'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'调入时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Transfer', @level2type=N'COLUMN',@level2name=N'Time'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Transfer', N'COLUMN',N'Reason'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'调入原因' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Transfer', @level2type=N'COLUMN',@level2name=N'Reason'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Transfer', N'COLUMN',N'Police'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'负责人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Transfer', @level2type=N'COLUMN',@level2name=N'Police'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'Transfer', N'COLUMN',N'Remarks'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Transfer', @level2type=N'COLUMN',@level2name=N'Remarks'
GO

/****** 对象:  StoredProcedure [dbo].[Transfer_UpDate]    脚本日期: 04/14/2007 16:38:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transfer_UpDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Transfer_UpDate]
GO

/****** 对象:  StoredProcedure [dbo].[Transfer_UpDate]    脚本日期: 04/13/2007 15:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transfer_UpDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--
--用途：更新调动记录
--时间：2007年4月10日
-------------------------------------
CREATE PROCEDURE [dbo].[Transfer_UpDate]
@ID int,
@Prisoner int,
@Belong int,
@Adjust int,
@Time datetime,
@Reason nvarchar(200),
@Police int,
@Remarks nvarchar(max)
AS
begin tran
UPDATE [dbo].[Transfer]
   SET [Prisoner] = @Prisoner
      ,[Belong] = @Belong
      ,[Adjust] = @Adjust
      ,[Time] = @Time
      ,[Reason] = @Reason
      ,[Police] = @Police
      ,[Remarks] = @Remarks
 WHERE [ID] = @ID
if @@error!=0
	begin
		rollback
	end
else
	begin
		commit
	end' 
END
GO

/****** 对象:  StoredProcedure [dbo].[Transfer_Delete]    脚本日期: 04/14/2007 16:38:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transfer_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Transfer_Delete]
GO

/****** 对象:  StoredProcedure [dbo].[Transfer_Delete]    脚本日期: 04/13/2007 15:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transfer_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--
--用途：删除调动记录
--时间：2007年4月10日
-------------------------------------
CREATE PROCEDURE [dbo].[Transfer_Delete]
@ID int
AS
begin tran
DELETE FROM [dbo].[Transfer]
      WHERE [ID] = @ID
if @@error!=0
	begin
		rollback
	end
else
	begin
		commit
	end' 
END
GO

/****** 对象:  StoredProcedure [dbo].[Transfer_Create]    脚本日期: 04/14/2007 16:38:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transfer_Create]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Transfer_Create]
GO

/****** 对象:  StoredProcedure [dbo].[Transfer_Create]    脚本日期: 04/13/2007 15:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transfer_Create]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--
--用途：添加调动记录
--时间：2007年4月10日
-------------------------------------
CREATE PROCEDURE [dbo].[Transfer_Create]
@Prisoner int,
@Belong int,
@Adjust int,
@Time datetime,
@Reason nvarchar(200),
@Police int,
@Remarks nvarchar(max)
AS
begin tran
INSERT INTO [dbo].[Transfer] ([Prisoner],[Belong],[Adjust],[Time],[Reason],[Police],[Remarks])
VALUES (@Prisoner,@Belong,@Adjust,@Time,@Reason,@Police,@Remarks)

UPDATE [dbo].[Prisoner]
   SET [Jail] = @Adjust
 WHERE [ID] = @Prisoner

if @@error!=0
	begin
		rollback
	end
else
	begin
		commit
	end' 
END
GO

GO
/****** 对象:  Table [dbo].[State]    脚本日期: 04/20/2007 02:19:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[State](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CardID] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[State] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]