34,594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @Name nvarchar(16), @SuperiorDeptName nvarchar(16)
SELECT @Name='1',@SuperiorDeptName=' '
declare @SuperiorDeptIndex smallint
declare @SelectStatement nvarchar(512)
if datalength(rtrim(ltrim(@SuperiorDeptName)))=0
set @SuperiorDeptIndex=null
else
set @SuperiorDeptIndex=isnull((select df_Index from udfdt_Department where df_Name=@SuperiorDeptName),0)
SELECT @SuperiorDeptIndex
set @SelectStatement='select df_Index 流水号,df_Name 名称,(select df_Name from udfdt_Department where df_Index='+isnull(ltrim(@SuperiorDeptIndex),'df_SuperiorDeptIndex')+') 上级部门
from udfdt_Department where df_Name='+isnull(''''+@Name+'''','df_name')+' and df_SuperiorDeptIndex='+isnull(ltrim(@SuperiorDeptIndex),'df_SuperiorDeptIndex')
PRINT @SelectStatement
--效果一樣
declare @num int
declare @t table(ID int identity(1,1),Num int)
insert @t select 1
insert @t select 2
insert @t select null
select @num=isnull(num,0) from @t where id=2
print @num
select @num=isnull((select num from @t where id=2),0)
print @num
/*
2
2
*/
create procedure udsp_SelectOperationEnterprise
@AreaName nvarchar(16)
as
declare @AreaIndex smallint
declare @SelectStatement nvarchar(4000)
IF @AreaName IS NOT NULL
select @AreaIndex=isnull(oeaf_Index,0) from udfdt_OperationEnterpriseArea where oeaf_Name=@AreaName
set @SelectStatement='select (select oeaf_Name from udfdt_OperationEnterpriseArea where oeaf_Index='+convert(nvarchar(10),@AreaIndex)+') 隶属地区
from udsdt_OperationEnterprise where oef_AreaIndex='+isnull(convert(nvarchar(10),@AreaIndex),'oef_AreaIndex')
execute(@SelectStatement)
--哦还有点问题
--这样?
create procedure udsp_SelectOperationEnterprise
@AreaName nvarchar(16)=null
as
declare @AreaIndex smallint
set @AreaIndex=isnull(@AreaName,null)
if (@AreaIndex is not null)
select @AreaIndex=isnull(oeaf_Index,0) from udfdt_OperationEnterpriseArea where oeaf_Name=@AreaName
select 隶属地区=oeaf_Name from udfdt_OperationEnterpriseArea where oeaf_Index=isnull(@AreaIndex ,oeaf_Index)
--这样?
create procedure udsp_SelectOperationEnterprise
@AreaName nvarchar(16)=null
as
declare @AreaIndex smallint
set @AreaIndex=isnull(@AreaName,null)
if (@AreaIndex<>null)
select @AreaIndex=isnull(oeaf_Index,0) from udfdt_OperationEnterpriseArea where oeaf_Name=@AreaName
select 隶属地区=oeaf_Name from udfdt_OperationEnterpriseArea where oeaf_Index=isnull(@AreaIndex ,oeaf_Index)