【正文】
TERED ( [bookID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[BookType]39。) AND type in (N39。U39。)) BEGIN CREATE TABLE [dbo].[BookType]( [bookTypeID] [int] IDENTITY(1,1) NOT NULL, [bookTypeName] [nvarchar](50) NOT NULL, [bookTypeDesc] [nvarchar](50) NULL ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[BorrowRec]39。) AND type in (N39。U39。)) BEGIN CREATE TABLE [dbo].[BorrowRec]( [borrowRecID] [int] IDENTITY(1,1) NOT NULL, [readerID] [nvarchar](50) NOT NULL, [bookID] [nvarchar](50) NOT NULL, [beginDate] [datetime] NULL, [endDate] [datetime] NULL, [borrowStatus] [bit] NULL, [overdueDays] [int] NULL, [penalty] [money] NULL, CONSTRAINT [PK_BorrowRec_1] PRIMARY KEY CLUSTERED ( [borrowRecID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END 存儲過程腳本: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_SearchBook]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。 CREATE PROCEDURE [dbo].[Proc_SearchBook] (@bookTypeID int, @queryByCol [varchar](10), @Content [varchar](50) ) AS begin declare @sql nvarchar(1000) 生成的 sql語句 if @bookTypeID=0 0為 “ 全部 ” begin set @sql=39。39。select * from Book where 39。39。+@queryByCol+39。39。 like 39。39。39。39。%39。39。+@Content+39。39。%39。39。39。39。39。39。 exec sp_executesql @sql end else begin set @sql=39。39。select * from Book where [bookTypeID]=39。39。+str(@bookTypeID)+39。39。 and 39。39。+@queryByCol+39。39。 like 39。39。39。39。%39。39。+@Content+39。39。%39。39。39。39。39。39。 exec sp_executesql @sql end end 39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[proc_InAndOut]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。CREATE proc [dbo].[proc_InAndOut] ( @outParam int output, @inParam nvarchar(50) ) as if exists(select * from [user] where userName=@inParam) begin set @outParam=1。 end else begin set @outParam=0。 end 39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_UpdateUserInfo]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。CREATE PROC [dbo].[Proc_UpdateUserInfo] @userID NVARCHAR(50),?? ) AS UPDATE Users SET userDepartment=@userDepartment,......) WHERE userID=@userID39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_UsersDetail]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。create PROCEDURE [dbo].[Proc_UsersDetail] @UserID [varchar](50) AS SELECT * FROM [dbo].[Users] WHERE userID=@UserID39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_GetUserInfoByID]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。CREATE PROCEDURE [dbo].[Proc_GetUserInfoByID] @userID VARCHAR(50) AS SELECT * FROM [dbo].[Users] WHERE userID=@userID 39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_GetUsers]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。CREATE PROCEDURE [dbo].[Proc_GetUsers] AS SELECT * FROM Users 39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_DeleteUsers]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。Create procedure [dbo].[Proc_DeleteUsers] @userID nvarchar(50) AS DELETE FROM Users where UserID=@userID 39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_UpdateUser]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。CREATE PROC [dbo].[Proc_UpdateUser] (@userID nvarchar(50),?? ) AS update [Users] set userName=@userName,...... where userID=@userID39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_GetRole]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。create proc [dbo].[Proc_GetRole] AS SELECT * FROM [Role] 39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_GetBooks]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。create PROC [dbo].[Proc_GetBooks] AS SELECT * FROM Book 39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID(N39。[dbo].[Proc_DeleteBook]39。) AND type in (N39。P39。, N39。PC39。)) BEGIN EXEC @statement = N39。CREATE PROC [dbo].[Proc_DeleteBook] @bookID nvarchar(50) AS DELETE FROM Book WHERE bookID=@bookID 39。 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM WHE