34,575
社区成员
发帖
与我相关
我的任务
分享
USE HOSPITAL
GO
CREATE PROCEDURE PROC_OUTPUTDEMO
@num int 10,
@id int 0,
@returnid int output
as
select top (@num) * from employee e inner join hospital h on e.hptid=h.hptid where e.id>@id order by e.id
select @returnid=max(id) from
(select top (@num) e.id from employee e inner join hospital h on e.hptid=h.hptid where e.id>@id order by e.id) as tab
go
===============================================================================
消息 102,级别 15,状态 1,过程 PROC_OUTPUTDEMO,第 2 行
'10' 附近有语法错误。
消息 137,级别 15,状态 2,过程 PROC_OUTPUTDEMO,第 6 行
必须声明标量变量 "@num"。
消息 137,级别 15,状态 1,过程 PROC_OUTPUTDEMO,第 7 行
必须声明标量变量 "@returnid"。
消息 137,级别 15,状态 2,过程 PROC_OUTPUTDEMO,第 7 行
必须声明标量变量 "@num"。
快被红字搞疯了啊...................
public DataTable ProcExcuteToDataTable(string procName, SqlParameter[] parameters)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
conn.Open();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = procName;
foreach (SqlParameter para in parameters)
{
SqlParameter p = (SqlParameter)para;
comm.Parameters.Add(p);
}
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
conn.Close();
}
return dt;
}
/*
在TOP后面使用变量
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2008-01-02 广东深圳)
*/
--SQL SERVER 2005 的写法
use adventureworks
go
DECLARE @Percentage int
SET @Percentage = 1
SELECT TOP (@Percentage) PERCENT
Name
FROM Production.Product
ORDER BY Name
/*
Name
----------------------
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
BB Ball Bearing
Bearing Ball
Bike Wash - Dissolver
(6 行受影响)
*/
-----------------------------------
--SQL SERVER 2000 的写法
create table a([id] [int])
insert into a(id) values(1)
insert into a(id) values(2)
insert into a(id) values(3)
insert into a(id) values(4)
insert into a(id) values(5)
declare @num as int
declare @sql as varchar(2000)
set @num = 2
set @sql = 'select top ' + cast(@num as char) + ' * from a'
exec(@sql)
drop table a
/*
id
-----------
1
2
*/
USE HOSPITAL
GO
CREATE PROCEDURE PROC_OUTPUTDEMO
@num int=10, --赋值是这样的
@id int=0,
@returnid int output
as
select top (@num) * from employee e inner join hospital h on e.hptid=h.hptid where e.id>@id order by e.id
select @returnid=max(id) from
(select top (@num) e.id from employee e inner join hospital h on e.hptid=h.hptid where e.id>@id order by e.id) as tab
go
2005+应该直接可以
CREATE PROCEDURE PROC_OUTPUTDEMO
@num int=10,
@id int=0,
@returnid int output
as
select top (@num) * from employee e inner join hospital h on e.hptid=h.hptid where e.id>@id order by e.id
select @returnid=max(id) from
(select top (@num) e.id from employee e inner join hospital h on e.hptid=h.hptid where e.id>@id order by e.id) as tab
go
USE HOSPITAL
GO
CREATE PROCEDURE PROC_OUTPUTDEMO
@num int =10,
@id int =0,
@returnid int output
as
select top (@num) * from employee e
inner join hospital h on e.hptid=h.hptid where e.id>@id order by e.id
select @returnid=max(id)
from
(select top (@num) e.id from employee e inner join hospital h on e.hptid=h.hptid where e.id>@id order by e.id) as tab
go