--创建存储 create proc mysp ( @name varchar(30) ) as begin select * from [你的表] where Name=@name select @name --下面是执行存储代码 exec mysp @name='abc'
--创建存储 create proc mysp ( @name nvarchar(30) ) as begin select max(Score) score from [你的表] where Name=@name select @name --下面是执行存储代码 exec mysp @name=N'甲'
又贴错了 --创建存储 create proc mysp ( @name nvarchar(30) ) as begin select max(Score) score from [你的表] where Name=@name end --下面是执行存储代码 exec mysp @name=N'甲'
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([ID] INT,[Name] VARCHAR(2),[Score] INT) INSERT [tb] SELECT 1,'甲',60 UNION ALL SELECT 2,'甲',80 UNION ALL SELECT 3,'甲',100 GO--> 测试语句: IF OBJECT_ID('p') IS NOT NULL DROP proc p GO create proc p @name varchar(10), @maxid int output, @ids varchar(10)output as begin select @maxid=[ID] from tb as t where name=@name and not exists(select 1 from tb where name=t.name and [Score]>t.[Score])select @ids=isnull(@ids+',','')+ltrim(id) from tb as t where name=@name and [ID]!=@maxid end go --调用 declare @id1 int ,@id2 varchar(10) exec p '甲',@id1 output,@id2 output select @id1,@id2/* ----------- ---------- 3 1,2(1 行受影响) */
create proc mysp
(
@name varchar(30)
)
as
begin
select * from [你的表]
where Name=@name
select @name
--下面是执行存储代码
exec mysp @name='abc'
create proc mysp
(
@name nvarchar(30)
)
as
begin
select max(Score) score from [你的表]
where Name=@name
select @name
--下面是执行存储代码
exec mysp @name=N'甲'
--创建存储
create proc mysp
(
@name nvarchar(30)
)
as
begin
select max(Score) score from [你的表]
where Name=@name
end
--下面是执行存储代码
exec mysp @name=N'甲'
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[Name] VARCHAR(2),[Score] INT)
INSERT [tb]
SELECT 1,'甲',60 UNION ALL
SELECT 2,'甲',80 UNION ALL
SELECT 3,'甲',100
GO--> 测试语句:
IF OBJECT_ID('p') IS NOT NULL DROP proc p
GO
create proc p
@name varchar(10),
@maxid int output,
@ids varchar(10)output
as
begin
select @maxid=[ID] from tb as t
where name=@name
and not exists(select 1 from tb where name=t.name and [Score]>t.[Score])select @ids=isnull(@ids+',','')+ltrim(id) from tb as t
where name=@name
and [ID]!=@maxid
end
go
--调用
declare @id1 int ,@id2 varchar(10)
exec p '甲',@id1 output,@id2 output
select @id1,@id2/*
----------- ----------
3 1,2(1 行受影响)
*/