CREATE PROCEDURE PROCdormreport @NO varchar(10),@dept varchar(10),@louhao varchar(10),
@fanghao varchar(10),@zufang varchar(10)
AS
set xact_abort on
begin tran
if rtrim(ltrim(@NO))<>''
select * into #temp1 from tbldorm
where [no]=@no
else
select * into #temp1 from tbldorm if rtrim(ltrim(@dept))<>''
select * into #temp2 from #temp1
where dept=@dept
else
select * into #temp2 from #temp1 drop table #temp1 if rtrim(ltrim(@louhao))<>''
select * into #temp3 from #temp2
where louhao=@louhao
else
select * into #temp3 from #temp2 drop table #temp2 if rtrim(ltrim(@fanghao))<>''
select * into #temp4 from #temp3
where fanghao=@fanghao
else
select * into #temp4 from #temp3
drop table #temp3 if rtrim(ltrim(@zufang))<>''
select * into #temp5 from temp4
where zufang=@zufang
else
select * into #temp5 from temp4 drop table #temp4
select * from #temp5
commit tran
当我检查语法准备存储这个存储过程时,sql server 老是提示说数据库空已经存在上面的临时表,不知道是什么原因?
@fanghao varchar(10),@zufang varchar(10)
AS
set xact_abort on
begin tran
if rtrim(ltrim(@NO))<>''
select * into #temp1 from tbldorm
where [no]=@no
else
select * into #temp1 from tbldorm if rtrim(ltrim(@dept))<>''
select * into #temp2 from #temp1
where dept=@dept
else
select * into #temp2 from #temp1 drop table #temp1 if rtrim(ltrim(@louhao))<>''
select * into #temp3 from #temp2
where louhao=@louhao
else
select * into #temp3 from #temp2 drop table #temp2 if rtrim(ltrim(@fanghao))<>''
select * into #temp4 from #temp3
where fanghao=@fanghao
else
select * into #temp4 from #temp3
drop table #temp3 if rtrim(ltrim(@zufang))<>''
select * into #temp5 from temp4
where zufang=@zufang
else
select * into #temp5 from temp4 drop table #temp4
select * from #temp5
commit tran
当我检查语法准备存储这个存储过程时,sql server 老是提示说数据库空已经存在上面的临时表,不知道是什么原因?
解决方案 »
- 关于sysobjects,syscolumns
- 这个不会写啊 求一个sql语句
- 数据库触发器问题
- 报错:异类查询要求为连接设置 ANSI_NULLS 和 ANSI_WARNINGS 选项。...............
- 一个简单的sqlserver问题
- sql sever将查询结果以xml格式保存到本地机器上
- 一简单的数据库设计问题,讨论者有分!!
- 更改SQL SERVER Database的owner
- 一个sybase的问题
- 求大神帮助,写一条SQL语句,实现筛选符合条件的记录
- 总结了一下自己在数据检索上的心得,与大家共享. 顺便感谢大家在那个贴子里给出的鼓励和建议散分.
- 如何在表的特定行后面添加新的数据行?
if rtrim(ltrim(@NO))<>''
select * into #temp1 from tbldorm
where [no]=@no
else
select * into #temp1 from tbldorm
CREATE PROCEDURE PROCdormreport @NO varchar(10),@dept varchar(10),@louhao varchar(10),
@fanghao varchar(10),@zufang varchar(10)
AS
declare @s nvarchar(4000)
set @s = '
set xact_abort on
begin tran'
if rtrim(ltrim(@NO))<>''
set @s=@s + N'
select * into #temp1 from tbldorm
where [no]=@no'
else
set @s=@s + N'
select * into #temp1 from tbldorm'if rtrim(ltrim(@dept))<>''
set @s=@s + N'
select * into #temp2 from #temp1
where dept=@dept'
else
set @s=@s + N'
select * into #temp2 from #temp1' set @s=@s + N'
drop table #temp1'if rtrim(ltrim(@louhao))<>''
set @s=@s + N'
select * into #temp3 from #temp2
where louhao=@louhao'
else
set @s=@s + N'
select * into #temp3 from #temp2' set @s=@s + N'
drop table #temp2'if rtrim(ltrim(@fanghao))<>''
set @s=@s + N'
select * into #temp4 from #temp3
where fanghao=@fanghao'
else
set @s=@s + N'
select * into #temp4 from #temp3' set @s=@s + N'
drop table #temp3'if rtrim(ltrim(@zufang))<>''
set @s=@s + N'
select * into #temp5 from temp4
where zufang=@zufang'
else
set @s=@s + N'
select * into #temp5 from temp4' set @s=@s + N'
drop table #temp4
select * from #temp5
commit tran'
exec sp_executesql @s, N'
@NO varchar(10),@dept varchar(10),@louhao varchar(10),
@fanghao varchar(10),@zufang varchar(10)',
@NO, @dept, @louhao, @fanghao, @zufang
CREATE PROCEDURE PROCdormreport @NO varchar(10),@dept varchar(10),@louhao varchar(10),
@fanghao varchar(10),@zufang varchar(10)
AS
set xact_abort on
begin tran
select * into #temp1 from tbldorm
where ([no]=@no and @No <> '') or @No = ''
select * into #temp2 from #temp1
where (dept=@dept and @dept <> '') or @dept = '' select * into #temp3 from #temp2
where (louhao=@louhao and @louhao <> '') or @louhao = '' select * into #temp4 from #temp3
where (fanghao=@fanghao and @fanghao <> '') or @fanghao = ''
select * into #temp5 from temp4
where (zufang=@zufang and @zufang <> '') or @zufang = ''
select * from #temp5 drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4
drop table #temp5commit tran
CREATE PROCEDURE PROCdormreport @NO varchar(10),@dept varchar(10),@louhao varchar(10),
@fanghao varchar(10),@zufang varchar(10)
AS
select * from tbldorm
where (@NO = '' OR [no]=@no)
AND (@dept = '' OR dept=@dept)
AND (@louhao = '' OR louhao=@louhao)
AND (@fanghao = '' OR fanghao=@fanghao)
AND (@zufang = '' OR zufang=@zufang)