34,576
社区成员
发帖
与我相关
我的任务
分享
If Object_id('User','U') Is Not null
Drop Table [User]
Go
Create Table [User](ID int Not null,[Name] nvarchar(50),Constraint PK_User_ID Primary Key(ID Asc))
Go
Insert Into [User] Select 89,'Zhao' union all
Select 99,'li'
Go
If Object_id('rUserByID','P') Is Not null
Drop Proc rUserByID
Go
Create Proc rUserByID
(@ID int)
As
declare @sql varchar(100)
declare @count int
set @sql ='Select top 1 ID,Name From (select top '+cast(@ID as varchar(100))+' ID,Name from [User]) a order by id desc'
select @count=count(*) from [user]
if (@count<@ID)
Raiserror 50001 '找不到记录!'
else
exec(@sql)
Go
Exec rUserByID 2
/*
ID Name
----------- --------------------------------------------------
99 li
*/
--沒太明白你的意思
If Object_id('User','U') Is Not null
Drop Table [User]
Go
Create Table [User](ID int Not null,[Name] nvarchar(50),Constraint PK_User_ID Primary Key(ID Asc))
Go
Insert Into [User] Select 89,'Zhao1'
Insert Into [User] Select 90,'Zhao2'
Insert Into [User] Select 100,'Zhao3'
Insert Into [User] Select 10,'Zhao4'
Go
If Object_id('rUserByID','P') Is Not null
Drop Proc rUserByID
Go
Create Proc rUserByID
(@ID int)
As
if not exists(Select ID,Name From [User] Where ID=@ID)
Raiserror 50001 '找不到记录!'
else
Select top 1 ID,Name From [User] Where ID=@ID
Go
Exec rUserByID 1
/*
伺服器: 訊息 50001,層級 16,狀態 1,程序 rUserByID,行 5
找不到记录!*/
Exec rUserByID 89
If Object_id('User','U') Is Not null
Drop Table [User]
Go
Create Table [User](ID int Not null,[Name] nvarchar(50),Constraint PK_User_ID Primary Key(ID Asc))
Go
Insert Into [User] Select 89,'Zhao'
Go
If Object_id('rUserByID','P') Is Not null
Drop Proc rUserByID
Go
Create Proc rUserByID
(@ID int)
As
if not exists(Select ID,Name From [User] Where ID=@ID)
Raiserror 50001 '找不到记录!'
else
Select ID,Name From [User] Where ID=@ID
Go
Exec rUserByID 89
/*
(所影响的行数为 1 行)
ID Name
----------- --------------------------------------------------
89 Zhao
(所影响的行数为 1 行)
*/
If Object_id('User','U') Is Not null
Drop Table [User]
Go
Create Table [User](ID int Not null,[Name] nvarchar(50),Constraint PK_User_ID Primary Key(ID Asc))
Go
Insert Into [User] Select 89,'Zhao'
Go
If Object_id('rUserByID','P') Is Not null
Drop Proc rUserByID
Go
Create Proc rUserByID
(@ID int)
As
Select ID,Name From [User] Where ID=@ID
If @@Rowcount =0
Raiserror 50001 '找不到记录!'
Go
Exec rUserByID 1
/*
(0 行受影响)
消息 50001,级别 16,状态 1,过程 rUserByID,第 6 行
找不到记录!
*/