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"。
快被红字搞疯了啊...................
@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
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
@num int ,
@id int ,
@returnid int output---
你既然设置了参数,那么其值应该是传入进去的.例如:
exec PROC_OUTPUTDEMO 10 , 0
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
@id int 0,
要有等於,默認值
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
/*
在TOP后面使用变量
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2008-01-02 广东深圳)
*/--SQL SERVER 2005 的写法
use adventureworks
goDECLARE @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
*/
{
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;
}
我要怎么修改才能把datatable 和 存储过程的输出参数 一起返回出去呢?