sql建表，建sp

標籤：constrain   var   iss   test   nvarchar   tab   nbsp   isnull   lease   

舉個栗子

 

if object_id(N‘[dbo].[TestForekoy_Mission]‘,N‘u‘) is not null
    drop table [dbo].[TestForekoy_Mission]
go
create table [dbo].[TestForekoy_Mission]
(
    [Mno]          int     identity(100001,1)     not null  primary key,
    [Mname]        nvarchar(20)      not null,
    [Detail]       nvarchar(500),
    [DateRelease]  date,
    [State]        nvarchar(20)     
)
go
alter table [dbo].[TestForekoy_Mission] add constraint DF_TestForekoy_Mission_DateRelease default (GetDate()) for [DateRelease]
go
alter table [dbo].[TestForekoy_Mission] add constraint DF_TestForekoy_Mission_State default ‘未進行‘ for [State]
go

 

if object_id(N‘[dbo].[TestForekoy_uspMission_Add]‘,N‘p‘) is not null
    drop procedure [dbo].[TestForekoy_uspMission_Add]
go
create procedure [dbo].[TestForekoy_uspMission_Add]
    @Mname        nvarchar(20),
    @Detail       nvarchar(500),
    @DateRelease  date,
    @State        nvarchar(20)
as
begin
    set nocount on
    insert into [dbo].[TestForekoy_Mission]
    values(@Mname,@Detail,@DateRelease,@State)
    set nocount off
end
go
grant execute on [dbo].[TestForekoy_uspMission_Add] to [db_spexecute],[siteuser]
go

 

if object_id(N‘[dbo].[TestForekoy_uspStaffSecurity_Update]‘,N‘p‘) is not null
    drop procedure [dbo].[TestForekoy_uspStaffSecurity_Update]
go
create procedure [dbo].[TestForekoy_uspStaffSecurity_Update]
    @Sno        int          =null,
    @Password   varchar(20)  =null,
    @Question   nvarchar(50) =null,
    @Answer     nvarchar(50) =null,
 @InputWrong int          =null
as
begin
    set nocount on
    update [dbo].[TestForekoy_StaffSecurity]
    set [Password] = isnull(@Password,[Password]),
        [Question] = isnull(@Question,[Question]),
        [Answer]   = isnull(@Answer,[Answer]),
  InputWrong = isnull(@InputWrong,InputWrong)
    where [Sno][email protected]
    set nocount off
end
go
grant execute on [dbo].[TestForekoy_uspStaffSecurity_Update] to [db_spexecute],[siteuser]
go

sql建表，建sp