이것은 목록 상자에서 입력을 받고 선택한 항목과 관련된 레코드를 표시하는 저장 프로 시저입니다. 그러나 목록 상자에서 아무것도 선택하지 않으면 발생하지 않은 모든 레코드를 표시해야합니다.
이것은 내 저장 프로 시저입니다.
USE [MyDb]
GO
/****** Object: StoredProcedure [dbo].[usp_SearchCAMAFunctionalObsolescence] Script Date: 10/18/2016 12:30:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_SearchCAMAFunctionalObsolescence]
@section as nvarchar(max),
@quality as nvarchar(max),
@style as nvarchar(max)
As
Begin
set nocount on;
Declare @Where as varchar(max)
Declare @Select as varchar(max)
Set @Select = ' Distinct vi.struct_no as structure,a.assesmt_no as assessment,a.parcel_no as parcel, o.own_last+'' , ''+o.own_first as taxpayer, id.year_built as built, id.effect_age as age, id.mkt_adj as fo, vi.aprais_val as mktvalue
From assessments a
inner join parcel p on a.parcel_no = p.parcel_no
inner join valueimp vi on vi.assesmt_no = a.assesmt_no
inner join owner o on o.id = a.owner_id
inner join imp_details id on id.improvementId = vi.id and (id.isdeleted is null or id.isdeleted = 0)
inner join quality_details qd on qd.quality_id = id.quality_id
inner join section_details sd on sd.section_id = id.section_id
inner join style_details stdl on stdl.style_id = id.style_id'
Set @Where = ' where (' + @section + ' is null or sd.section_id = ' + @section + ') and (' + @quality + ' is null or qd.quality_id = ' + @quality + ') and (' + @style + ' is null or stdl.style_id = ' + @style + ')'
DECLARE @QUERY NVARCHAR(MAX)
SET @QUERY= 'Select '+ @SELECT + @WHERE
print @QUERY
EXEC sp_executesql @QUERY , N'@section as int ,@quality as int,@style as int' ,@section ,@quality,@style
END
if i execute stored procedure in this way
// EXEC usp_SearchCAMAFunctionalObsolescence 'null','null','null'
it display all records.
but i need to execute stored procedure in this way
// EXEC usp_SearchCAMAFunctionalObsolescence null,null,null
and it not display anything
이 목적을 위해 동적 SQL이 필요하지 않습니다. mysql을 사용하는 경우 문에 IFNULL 을 사용하십시오 WHERE. ( ISNULL 을 SQL Server사용 하고 oracle의 경우 대신 NVL을 사용하십시오.)
입력 변수가 null이면 스크립트는 실제 열 값을 반환합니다.
USE [MyDb]
GO
/****** Object: StoredProcedure [dbo].[usp_SearchCAMAFunctionalObsolescence] Script Date: 10/18/2016 12:30:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_SearchCAMAFunctionalObsolescence]
@section as nvarchar(max),
@quality as nvarchar(max),
@style as nvarchar(max)
As
Begin
set nocount on;
SELECT Distinct vi.struct_no as structure,a.assesmt_no as assessment,a.parcel_no as parcel, o.own_last+'' , ''+o.own_first as taxpayer, id.year_built as built, id.effect_age as age, id.mkt_adj as fo, vi.aprais_val as mktvalue
From assessments a
inner join parcel p on a.parcel_no = p.parcel_no
inner join valueimp vi on vi.assesmt_no = a.assesmt_no
inner join owner o on o.id = a.owner_id
inner join imp_details id on id.improvementId = vi.id and (id.isdeleted is null or id.isdeleted = 0)
inner join quality_details qd on qd.quality_id = id.quality_id
inner join section_details sd on sd.section_id = id.section_id
inner join style_details stdl on stdl.style_id = id.style_id
WHERE sd.section_id =IFNULL(@section,sd.section_id)
AND qd.quality_id = IFNULL(@quality,qd.quality_id)
AND stdl.style_id = IFNULL(@style,stdl.style_id )
END
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다