create proc zcf_f1
@prodId int
as
select prodName,prodId,bomNum
from prod
where prodId = @prodId
@prodId int
as
select prodName,prodId,bomNum
from prod
where prodId = @prodId
解决方案 »
- 在.DBF文件插入数据
- 一条SQL语句的优化,现在如果有几千条记录的时候都要5.6秒,能不能优化到只有2、3秒的查询?
- 【新问题】如何实现分组统计的时候,没有的显示为0?
- 请问analysis servers中的数据源中没有新增数据源选项是怎么回事呢?
- 在数据库里存入公式然后Select的时候调用怎么搞?
- SQL Server2005内存大问题
- 如何实现SQL Server的异地同步镜像!!!!
- 新手求助啊!
- 全文检索
- 怎样设置触发不在同一个库的表(sql server 7.0)
- 用JDBC直接连接SQLServer时提示错误:Error establishing socket
- 帮我看一下这句case语句为什么错??
Create Table tb1(
id1 int,
sname varchar(10),
nlevel int
)
create table tb2(
childid int,
fid int
)
--------------------------
Create Table tb3(vk int null)
insert into tb1 values( 1, 'A', -1)
insert into tb1 values(2 , 'B', -1)
insert into tb1 values(3 , 'A1', 1)
insert into tb1 values(4, 'B1', 1)
insert into tb1 values(5 , 'A1-1', 2)insert into tb2 values( 3, 1)
insert into tb2 values(3 , 2)
insert into tb2 values(4 , 2)
insert into tb2 values(5 , 3)select * from tb1select * from tb2
-----------------------------------下面是存過程 --------------------------------
If Exists(select * from sysobjects where id=object_id(N'[dbo].[tb1_p]') and objectproperty(id,N'isProcedure')=1 )
Drop Procedure [DBO].[tb1_p]
go
Create Procedure tb1_p
@i int,
@p int
as
Declare @k int
Declare @@s int
if not exists(select * from tb3 where vk>=@p)
select * from tb1 where id1=@i
Declare kk cursor local for
select childid from tb2 where fid=@i and childid<=@p
open kk
fetch next from kk into @k
while @@fetch_status=0
begin
exec tb1_p @k,@p
if not exists(select * from tb3 where vk>=@p)
update tb3 set vk=@k
fetch next from kk into @k
end
close kk
deallocate kk
----------------------------------------下面是第二個過程 ,調用第一個過程----------------
If Exists(select * from sysobjects where id=object_id(N'[dbo].[tb1_p1]') and objectproperty(id,N'isProcedure')=1 )
Drop Procedure [DBO].[tb1_p1]
go
Create Procedure tb1_p1
@i int,
@p int
as
Delete from tb3
insert into tb3 values(0)
exec tb1_p @i,@p
-------------------------------------------------結束-------------------------------------------------------------------
-- tb1_p1 2,5 "這是執行語句 查結果
--------------------下面是建表結構-----------------------
Create Table tb1(
id1 int,
sname varchar(10),
nlevel int
)
create table tb2(
childid int,
fid int
)
--------------------------
Create Table tb3(vk int null)
insert into tb1 values( 1, 'A', -1)
insert into tb1 values(2 , 'B', -1)
insert into tb1 values(3 , 'A1', 1)
insert into tb1 values(4, 'B1', 1)
insert into tb1 values(5 , 'A1-1', 2)insert into tb2 values( 3, 1)
insert into tb2 values(3 , 2)
insert into tb2 values(4 , 2)
insert into tb2 values(5 , 3)select * from tb1select * from tb2
-----------------------------------下面是存過程 --------------------------------
If Exists(select * from sysobjects where id=object_id(N'[dbo].[tb1_p]') and objectproperty(id,N'isProcedure')=1 )
Drop Procedure [DBO].[tb1_p]
go
Create Procedure tb1_p
@i int,
@p int
as
Declare @k int
Declare @@s int
if not exists(select * from tb3 where vk>=@p)
select * from tb1 where id1=@i
Declare kk cursor local for
select childid from tb2 where fid=@i and childid<=@p
open kk
fetch next from kk into @k
while @@fetch_status=0
begin
exec tb1_p @k,@p
if not exists(select * from tb3 where vk>=@p)
update tb3 set vk=@k
fetch next from kk into @k
end
close kk
deallocate kk
If Exists(select * from sysobjects where id=object_id(N'[dbo].[tb1_p1]') and objectproperty(id,N'isProcedure')=1 )
Drop Procedure [DBO].[tb1_p1]
go
Create Procedure tb1_p1
@i int,
@p int
as
Delete from tb3
insert into tb3 values(0)
exec tb1_p @i,@p
Delete from tb3
insert into tb3 values(0)
exec tb1_p @i,@p因为是多人同时访问(访问量大)会不会出现问题?
示例3:
exec spclass(2,3)
得到结果
id sname nlevel
2 B -1
3 A1 1
應用 tb1_p1 2,3
會是你要的結果 ,開如點是2,子級是3時就會斷開
insert into tb3 values(0)
exec tb1_p @i,@p因为是多人同时访问(访问量大)会不会出现问题?
@rootid int,
@childid int
)returns table
as
return(
select a.*
from tb1 a
where a.id=@rootid
or a.id=@childid
or a.id=(
select a.childid
from tb2 a,tb2 b
where a.Fid=@rootid
and b.childid=@childid
and a.childid=b.Fid))
go
--调用
select * from f_spclass(1,5)select * from f_spclass(2,5)select * from f_spclass(2,3)
--测试数据
create table tb1(id int,sname nvarchar(10),nlevel int)
insert tb1 select 1,'A' ,-1
union all select 2,'B' ,-1
union all select 3,'A1' ,1
union all select 4,'B1' ,1
union all select 5,'A1-1',2create table tb2(childid int,Fid int)
insert tb2 select 3,1
union all select 3,2
union all select 4,2
union all select 5,3
go--自定义函数
create function f_spclass(
@rootid int,
@childid int
)returns table
as
return(
select a.*
from tb1 a
where a.id=@rootid
or a.id=@childid
or a.id=(
select a.childid
from tb2 a,tb2 b
where a.Fid=@rootid
and b.childid=@childid
and a.childid=b.Fid))
go--调用
select * from f_spclass(1,5)select * from f_spclass(2,5)select * from f_spclass(2,3)
go--删除测试
drop table tb1,tb2
drop function f_spclass/*--测试结果id sname nlevel
----------- ---------- -----------
1 A -1
3 A1 1
5 A1-1 2(所影响的行数为 3 行)id sname nlevel
----------- ---------- -----------
2 B -1
3 A1 1
5 A1-1 2(所影响的行数为 3 行)id sname nlevel
----------- ---------- -----------
2 B -1
3 A1 1(所影响的行数为 2 行)
--*/
@rootid int,
@childid int
)returns table
as
return(
select a.*
from tb1 a,(
select a.childid
from tb2 a,tb2 b
where a.Fid=@rootid
and b.childid=@childid
and a.childid=b.Fid
union select @rootid
union select @childid
)b where a.id=b.childid)