1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID6 6 (N'[dbo].[tbl_User]') AND type in (N'U'))
7 BEGIN
8 CREATE TABLE [dbo].[tbl_User](
[uid] [bigint] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NULL,
[pwd] [varchar](50) NULL,
[userNewId] [varchar](50) NULL CONSTRAINT
9 [DF_tbl_User_userNewId_1] DEFAULT (newid()),
10 CONSTRAINT [PK_tbl_User] PRIMARY KEY CLUSTERED
(
[uid] ASC
11 )WITH ( PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_CheckNewID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Proc_CheckNewID]
-- Add the parameters for the stored procedure here
@newID varchar(50),
@username varchar(50),
@uid bigint output,
@Ret int output -- 1.验证通过,2.验证失败
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
select @uid = uid from tbl_User where userNewId = @newID and username = @username
if(@uid is not null)
begin
set @Ret = 1 --验证通过
update tbl_User set userNewId = newid() where userNewId = @newID
end
else
set @Ret = 0 --未通过验证
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserLogin]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UserLogin]
-- Add the parameters for the stored procedure here
@username varchar(50),
@pwd varchar(50) ,
@uid bigint output,
@newID varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
select @uid = uid,@newID =userNewId from tbl_User where username = @username and pwd = @pwd
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNewID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetNewID]
-- Add the parameters for the stored procedure here
@uid bigint,
@username varchar(50) output,
@newID varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
select @newID = userNewId,@username = username from tbl_User where uid = @uid
END
'
END服务器: 消息 170,级别 15,状态 1,行 11
第 11 行: '(' 附近有语法错误。-----------
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'sys.objects' 无效。哪位好心大人看看为什么老说那个WITH 语句行错误啊!!
还有为什么'sys.objects' 无效尼?
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID6 6 (N'[dbo].[tbl_User]') AND type in (N'U'))
7 BEGIN
8 CREATE TABLE [dbo].[tbl_User](
[uid] [bigint] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NULL,
[pwd] [varchar](50) NULL,
[userNewId] [varchar](50) NULL CONSTRAINT
9 [DF_tbl_User_userNewId_1] DEFAULT (newid()),
10 CONSTRAINT [PK_tbl_User] PRIMARY KEY CLUSTERED
(
[uid] ASC
11 )WITH ( PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_CheckNewID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Proc_CheckNewID]
-- Add the parameters for the stored procedure here
@newID varchar(50),
@username varchar(50),
@uid bigint output,
@Ret int output -- 1.验证通过,2.验证失败
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
select @uid = uid from tbl_User where userNewId = @newID and username = @username
if(@uid is not null)
begin
set @Ret = 1 --验证通过
update tbl_User set userNewId = newid() where userNewId = @newID
end
else
set @Ret = 0 --未通过验证
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserLogin]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UserLogin]
-- Add the parameters for the stored procedure here
@username varchar(50),
@pwd varchar(50) ,
@uid bigint output,
@newID varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
select @uid = uid,@newID =userNewId from tbl_User where username = @username and pwd = @pwd
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNewID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetNewID]
-- Add the parameters for the stored procedure here
@uid bigint,
@username varchar(50) output,
@newID varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
select @newID = userNewId,@username = username from tbl_User where uid = @uid
END
'
END服务器: 消息 170,级别 15,状态 1,行 11
第 11 行: '(' 附近有语法错误。-----------
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'sys.objects' 无效。哪位好心大人看看为什么老说那个WITH 语句行错误啊!!
还有为什么'sys.objects' 无效尼?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货