ms sql存儲過程參數返回值
create table tabReProc
(
name varchar(30),
age integer,
primary key(name,age)
)
insert into tabReProc values('x7700',20)
insert into tabReProc values('x7711',21)
insert into tabReProc values('x7733',23)
insert into tabReProc values('x7755',25)
if exists(select *from dbo.[sysobjects] where [name]='proc1')
drop proc proc1
go
create procedure proc1(@parm1 integer,@parm2 varchar(30) output)
as
select @parm2='zhongguo'
select * from tabReProc where age=@parm1
return 100
go
declare @tmpa int
declare @param1 int ,@param2 varchar(30)
select @param1=20
execute @tmpa=proc1 @param1,@param2 output
select @tmpa,@param2
相关文档：
On BULK COLLECT
By Steven Feuerstein Oracle ACE
Best practices for knowing your LIMIT and kicking %NOTFOUND
I have started using BULK COLLECT whenever I need to fetch large volumes of data. This has caused me some trouble with my DBA, however. He is complaining that although my programs mig ......
DECLARE @temp TABLE(
id INT,
[name] VARCHAR(50),
class VARCHAR(50)
)
INSERT INTO @temp
SELECT 1,'a','A'
UNION ALL SELECT 2,'b','C'
UNION ALL SELECT 3,'c','B'
UNION ALL SELECT 4,'d','C'
UNION ALL SELECT 5,'e','B'
UNION ALL SELECT 6,'f','A'
SELECT * from @temp AS _temp WHERE [name] IN
(
......
1.多where，少having
where用来过滤行，having用来过滤组
2.多union all，少union
union删除了重复的行，因此花费了一些时间
3.多Exists，少in
Exists只检查存在性，性能比in强很多，有些朋友不会用Exists，就举个例子
例，想要得到有电话号码的人的基本信息，table2有冗余信息
select * from table1;--(id,n ......
1．SQL并行查询
alter session enable parallel dml execute immediate 'alter session enable parallel dml'; --修改会话并行DML select /*+parallel(a,4)*/ * from table_name a select /*+parallel(a,8)*/ * from table_name a &nbs ......
