DECLARE @PR棜楌No int
DECLARE @彜昳No int
DECLARE @屭媞No int
DECLARE @搊榐僗僞僢僼No int
DECLARE @峀崘No int
DECLARE @僾儘儌乕僔儑儞梊掕No int
DECLARE @僾儘儌乕僔儑儞幚愌No int
DECLARE @PR嬫暘No int
DECLARE @憤旓梡 int
DECLARE @懳徾彜昳憤悢 int
DECLARE @懳徾婛懚屭媞憤悢 int
DECLARE @桳岠擔悢撪偺憤攧忋 int
DECLARE @怴婯屭媞妉摼悢 int
DECLARE @尒崬屭媞妉摼悢 int
DECLARE @孈婲偟屭媞悢 int
DECLARE @峏怴僗僞僢僼No int
DECLARE @擭 int
DECLARE @擭寧 int
DECLARE @寧 int
DECLARE @R int
DECLARE @F int
DECLARE @M int
DECLARE @僐儞僞僋僩棜楌No int
DECLARE @僶僂儞僪No int
DECLARE @僣乕儖No int
DECLARE @僐儞僞僋僩僗僞僢僼No int
DECLARE @僗僞僢僼No int
DECLARE @栶怑No int
DECLARE @尃尷No int
DECLARE @夋柺No int
DECLARE @嬫暘 int
DECLARE @壜晄壜 int
DECLARE @惈暿 int
DECLARE @儊儖儅僈嫋戻 int
DECLARE @壠懓峔惉No int
DECLARE @壠懓嬫暘 int
DECLARE @曗懌柤No int
DECLARE @屭媞曗懌No int
DECLARE @峸擖嵪傒彜昳No1 int
DECLARE @峸擖嵪傒彜昳No2 int
DECLARE @峸擖嵪傒彜昳No3 int
DECLARE @RFM儔儞僋 int
DECLARE @RFM庬暿 int
DECLARE @PR柤 varchar
DECLARE @峀崘柤 varchar
DECLARE @撪梕 varchar
DECLARE @扴摉幰柤 varchar
DECLARE @TEL varchar
DECLARE @FAX varchar
DECLARE @儊乕儖傾僪儗僗 varchar
DECLARE @URL varchar
DECLARE @旛峫 varchar
DECLARE @憖嶌抂枛柤 varchar
DECLARE @憖嶌僗僞僢僼巵柤 varchar
DECLARE @憖嶌夋柺 varchar
DECLARE @憖嶌撪梕 varchar
DECLARE @審柤 varchar
DECLARE @杮暥 varchar
DECLARE @僗僞僢僼ID varchar
DECLARE @僷僗儚乕僪 varchar
DECLARE @巵柤 varchar
DECLARE @抣 varchar
DECLARE @屭媞ID varchar
DECLARE @巵柤僇僫 varchar
DECLARE @梄曋斣崋 varchar
DECLARE @廧強 varchar
DECLARE @曗懌撪梕 varchar
DECLARE @岞奐梊掕擔 datetime
DECLARE @岞奐幚巤擔 datetime
DECLARE @梊掕婜娫fr datetime
DECLARE @梊掕婜娫to datetime
DECLARE @岞奐梊掕擔fr datetime
DECLARE @岞奐梊掕擔to datetime
DECLARE @岞奐幚巤擔fr datetime
DECLARE @岞奐幚巤擔to datetime
DECLARE @幚巤婜娫fr datetime
DECLARE @幚巤婜娫to datetime
DECLARE @媥柊懳徾擔 datetime
DECLARE @斕攧擔帪fr datetime
DECLARE @斕攧擔帪to datetime
DECLARE @憖嶌擔fr datetime
DECLARE @憖嶌擔to datetime
DECLARE @僐儞僞僋僩擔帪 datetime
DECLARE @惗擭寧擔 datetime

SET @PR棜楌No = 1
SET @彜昳No = 1
SET @屭媞No = 1
SET @搊榐僗僞僢僼No = 1
SET @峀崘No = 1
SET @僾儘儌乕僔儑儞梊掕No = 1
SET @僾儘儌乕僔儑儞幚愌No = 1
SET @PR嬫暘No = 1
SET @憤旓梡 = 1
SET @懳徾彜昳憤悢 = 1
SET @懳徾婛懚屭媞憤悢 = 1
SET @桳岠擔悢撪偺憤攧忋 = 1
SET @怴婯屭媞妉摼悢 = 1
SET @尒崬屭媞妉摼悢 = 1
SET @孈婲偟屭媞悢 = 1
SET @峏怴僗僞僢僼No = 1
SET @擭 = 1
SET @擭寧 = 1
SET @寧 = 1
SET @R = 1
SET @F = 1
SET @M = 1
SET @僐儞僞僋僩棜楌No = 1
SET @僶僂儞僪No = 1
SET @僣乕儖No = 1
SET @僐儞僞僋僩僗僞僢僼No = 1
SET @僗僞僢僼No = 1
SET @栶怑No = 1
SET @尃尷No = 1
SET @夋柺No = 1
SET @嬫暘 = 1
SET @壜晄壜 = 1
SET @惈暿 = 1
SET @儊儖儅僈嫋戻 = 1
SET @壠懓峔惉No = 1
SET @壠懓嬫暘 = 1
SET @曗懌柤No = 1
SET @屭媞曗懌No = 1
SET @峸擖嵪傒彜昳No1 = 1
SET @峸擖嵪傒彜昳No2 = 1
SET @峸擖嵪傒彜昳No3 = 1
SET @RFM儔儞僋 = 1
SET @RFM庬暿 = 1
SET @PR柤 = 'a'
SET @峀崘柤 = 'a'
SET @撪梕 = 'a'
SET @扴摉幰柤 = 'a'
SET @TEL = 'a'
SET @FAX = 'a'
SET @儊乕儖傾僪儗僗 = 'a'
SET @URL = 'a'
SET @旛峫 = 'a'
SET @憖嶌抂枛柤 = 'a'
SET @憖嶌僗僞僢僼巵柤 = 'a'
SET @憖嶌夋柺 = 'a'
SET @憖嶌撪梕 = 'a'
SET @審柤 = 'a'
SET @杮暥 = 'a'
SET @僗僞僢僼ID = 'a'
SET @僷僗儚乕僪 = 'a'
SET @巵柤 = 'a'
SET @抣 = 'a'
SET @屭媞ID = 'a'
SET @巵柤僇僫 = 'a'
SET @梄曋斣崋 = 'a'
SET @廧強 = 'a'
SET @岞奐梊掕擔 = '2002/1/11'
SET @岞奐幚巤擔 = '2002/1/11'
SET @幚巤婜娫fr = '2002/1/11'
SET @幚巤婜娫to = '2003/7/11'
SET @媥柊懳徾擔 = '2001/7/15'
SET @梊掕婜娫fr = '2001/7/15'
SET @梊掕婜娫to = '2001/7/15'
SET @岞奐梊掕擔fr = '2002/1/11'
SET @岞奐梊掕擔to = '2002/1/11'
SET @岞奐幚巤擔fr = '2002/1/11'
SET @岞奐幚巤擔to = '2002/1/11'
SET @斕攧擔帪fr = '2002/1/11'
SET @斕攧擔帪to = '2002/1/11'
SET @憖嶌擔fr = '2002/1/11'
SET @憖嶌擔to = '2002/1/11'
SET @僐儞僞僋僩擔帪 = '2002/1/11'
SET @惗擭寧擔 = '2002/1/11'



---- Fill
--SELECT
--	0 AS 怴婯, 
--	0 AS 曄峏, 
--	0 AS 嶍彍, 
--	M03屭媞.屭媞No, 
--	M03屭媞.屭媞ID, 
--	M03屭媞.巵柤, 
--	M03屭媞.巵柤僇僫, 
--	M01屌掕懏惈.抣 AS 惈暿, 
--	M03屭媞.惗擭寧擔, 
--	M03屭媞.梄曋斣崋, 
--	M03屭媞.廧強, 
--	M03屭媞.TEL, 
--	M03屭媞.儊乕儖傾僪儗僗, 
--	M03屭媞.儊儖儅僈嫋戻, 
--	M01屌掕懏惈_1.抣 AS 儊儖儅僈嫋戻柤, 
--	M03屭媞.旛峫
--FROM
--	M03屭媞 LEFT OUTER JOIN
--	M01屌掕懏惈 AS M01屌掕懏惈_1 ON 
--	M03屭媞.儊儖儅僈嫋戻 = M01屌掕懏惈_1.屌掕懏惈No LEFT OUTER JOIN
--	M01屌掕懏惈 ON M03屭媞.惈暿 = M01屌掕懏惈.屌掕懏惈No
--WHERE
--	慻怐No = @慻怐No
SELECT
	0 AS 怴婯,
	0 AS 曄峏,
	0 AS 嶍彍,
	M03屭媞.屭媞No,
	M03屭媞.屭媞ID, 
	M03屭媞.巵柤,
	M03屭媞.巵柤僇僫,
	M04懏惈.抣 AS 惈暿,
	M03屭媞.惗擭寧擔, 
	M03屭媞.梄曋斣崋,
	M03屭媞.廧強,
	M03屭媞.TEL,
	M03屭媞.儊乕儖傾僪儗僗, 
	M03屭媞.儊儖儅僈嫋戻,
	M04懏惈_1.抣 AS 儊儖儅僈嫋戻柤, 
	M03屭媞.旛峫
FROM
	M03屭媞 LEFT OUTER JOIN
	M04懏惈 AS M04懏惈_1 ON 
	M03屭媞.儊儖儅僈嫋戻 = M04懏惈_1.懏惈No LEFT OUTER JOIN
	M04懏惈 ON M03屭媞.惈暿 = M04懏惈.懏惈No


---- FillBy専嶕
--SELECT DISTINCT 
--	M03屭媞.屭媞No, 
--	M03屭媞.屭媞ID, 
--	M03屭媞.巵柤, 
--	M03屭媞.巵柤僇僫, 
--	M01屌掕懏惈_1.抣 AS 惈暿, 
--	M03屭媞.惗擭寧擔, 
--	M03屭媞.梄曋斣崋, 
--	M03屭媞.廧強,
--	M03屭媞.TEL, 
--	M03屭媞.儊儖儅僈嫋戻, 
--	M03屭媞.儊乕儖傾僪儗僗, 
--	M03屭媞.旛峫, 
--	M01屌掕懏惈.抣 AS 儊儖儅僈嫋戻柤
--FROM
--	M03屭媞 LEFT OUTER JOIN
--	M01屌掕懏惈 ON M03屭媞.儊儖儅僈嫋戻 = M01屌掕懏惈.屌掕懏惈No LEFT OUTER JOIN
--	H01斕攧棜楌 ON M03屭媞.屭媞No = H01斕攧棜楌.屭媞No LEFT OUTER JOIN
--	M01屌掕懏惈 AS M01屌掕懏惈_1 ON M03屭媞.惈暿 = M01屌掕懏惈_1.屌掕懏惈No
--WHERE
--	M03屭媞.慻怐No = @慻怐No AND
--	((@屭媞ID IS NULL) OR (M03屭媞.屭媞ID LIKE '%' + @屭媞ID + '%')) AND 
--	((@巵柤 IS NULL) OR (M03屭媞.巵柤 LIKE '%' + @巵柤 + '%')) AND 
--	((@巵柤僇僫 IS NULL) OR (M03屭媞.巵柤僇僫 LIKE '%' + @巵柤僇僫 + '%')) AND 
--	((@惈暿 IS NULL) OR (M03屭媞.惈暿 = @惈暿)) AND 
--	((@廧強 IS NULL) OR (M03屭媞.廧強 LIKE '' + @廧強 + '%')) AND 
--	((@梄曋斣崋 IS NULL) OR (M03屭媞.梄曋斣崋 LIKE '%' + @梄曋斣崋 + '%')) AND 
--	((@TEL IS NULL) OR (M03屭媞.TEL LIKE '%' + @TEL + '%')) AND 
--	((@儊乕儖傾僪儗僗 IS NULL) OR (M03屭媞.儊乕儖傾僪儗僗 LIKE '%' + @儊乕儖傾僪儗僗 + '%')) AND 
--	(@峸擖嵪傒彜昳No1 IS NULL AND 
--	@峸擖嵪傒彜昳No2 IS NULL AND 
--	@峸擖嵪傒彜昳No3 IS NULL OR	@峸擖嵪傒彜昳No1 IS NOT NULL AND 
--	@峸擖嵪傒彜昳No2 IS NULL AND 
--	@峸擖嵪傒彜昳No3 IS NULL AND 
--	H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No1 OR @峸擖嵪傒彜昳No1 IS NOT NULL AND 
--	@峸擖嵪傒彜昳No2 IS NOT NULL AND 
--	@峸擖嵪傒彜昳No3 IS NULL AND 
--	H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No1 OR
--	H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No2 OR
--	@峸擖嵪傒彜昳No1 IS NOT NULL AND 
--	@峸擖嵪傒彜昳No2 IS NOT NULL AND 
--	@峸擖嵪傒彜昳No3 IS NOT NULL AND 
--	H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No1 OR
--	H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No2 OR
--	H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No3) AND 
--	(@惗擭寧擔 IS NULL OR M03屭媞.惗擭寧擔 = @惗擭寧擔) AND 
--	(@儊儖儅僈嫋戻 IS NULL OR M03屭媞.儊儖儅僈嫋戻 = @儊儖儅僈嫋戻)
SELECT DISTINCT 
	M03屭媞.屭媞No,
	M03屭媞.屭媞ID,
	M03屭媞.巵柤,
	M03屭媞.巵柤僇僫, 
	M04懏惈_1.抣 AS 惈暿,
	M03屭媞.惗擭寧擔,
	M03屭媞.梄曋斣崋,
	M03屭媞.廧強,
	M03屭媞.TEL,
	M03屭媞.儊儖儅僈嫋戻,
	M03屭媞.儊乕儖傾僪儗僗, 
	M03屭媞.旛峫,
	M04懏惈.抣 AS 儊儖儅僈嫋戻柤
FROM
	M03屭媞 LEFT OUTER JOIN
	M04懏惈 ON M03屭媞.儊儖儅僈嫋戻 = M04懏惈.懏惈No LEFT OUTER JOIN
	H01斕攧棜楌 ON M03屭媞.屭媞No = H01斕攧棜楌.屭媞No LEFT OUTER JOIN
	M04懏惈 AS M04懏惈_1 ON M03屭媞.惈暿 = M04懏惈_1.懏惈No
WHERE
	(@屭媞ID IS NULL OR M03屭媞.屭媞ID LIKE '%' + @屭媞ID + '%') AND
	(@巵柤 IS NULL OR M03屭媞.巵柤 LIKE '%' + @巵柤 + '%') AND
	(@巵柤僇僫 IS NULL OR M03屭媞.巵柤僇僫 LIKE '%' + @巵柤僇僫 + '%') AND
	(@惈暿 IS NULL OR M03屭媞.惈暿 = @惈暿) AND
	(@廧強 IS NULL OR M03屭媞.廧強 LIKE '' + @廧強 + '%') AND
	(@梄曋斣崋 IS NULL OR M03屭媞.梄曋斣崋 LIKE '%' + @梄曋斣崋 + '%') AND
	(@TEL IS NULL OR M03屭媞.TEL LIKE '%' + @TEL + '%') AND
	(@儊乕儖傾僪儗僗 IS NULL OR M03屭媞.儊乕儖傾僪儗僗 LIKE '%' + @儊乕儖傾僪儗僗 + '%') AND 
	(@峸擖嵪傒彜昳No1 IS NULL AND
	 @峸擖嵪傒彜昳No2 IS NULL AND
	 @峸擖嵪傒彜昳No3 IS NULL OR @峸擖嵪傒彜昳No1 IS NOT NULL AND
	 @峸擖嵪傒彜昳No2 IS NULL AND
	 @峸擖嵪傒彜昳No3 IS NULL AND
	 H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No1 OR @峸擖嵪傒彜昳No1 IS NOT NULL AND
	 @峸擖嵪傒彜昳No2 IS NOT NULL AND 
	 @峸擖嵪傒彜昳No3 IS NULL AND
	 H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No1 OR H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No2 OR @峸擖嵪傒彜昳No1 IS NOT NULL AND
	 @峸擖嵪傒彜昳No2 IS NOT NULL AND
	 @峸擖嵪傒彜昳No3 IS NOT NULL AND 
	 H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No1 OR H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No2 OR H01斕攧棜楌.彜昳No = @峸擖嵪傒彜昳No3) AND
	(@惗擭寧擔 IS NULL OR M03屭媞.惗擭寧擔 = @惗擭寧擔) AND
	(@儊儖儅僈嫋戻 IS NULL OR M03屭媞.儊儖儅僈嫋戻 = @儊儖儅僈嫋戻)
ORDER BY
	M03屭媞.屭媞ID DESC


---- FillBy屭媞No庢摼
--SELECT 
--	屭媞No 
--FROM 
--	M03屭媞 
--WHERE
--	慻怐No = @慻怐No AND
--	屭媞ID = @屭媞ID
SELECT
	屭媞No
FROM
	M03屭媞
WHERE
	(屭媞ID = @屭媞ID)


---- FillBy屭媞摦岦昞
--SELECT
--	M03屭媞.屭媞No, 
--	M03屭媞.屭媞ID, 
--	M03屭媞.巵柤, 
--	M03屭媞.巵柤僇僫, 
--	M01屌掕懏惈.抣 AS 惈暿, 
--	M03屭媞.惗擭寧擔, 
--	M03屭媞.梄曋斣崋, 
--	M03屭媞.廧強, 
--	M03屭媞.TEL, 
--	M03屭媞.儊乕儖傾僪儗僗, 
--	M01屌掕懏惈_1.抣 AS 儊儖儅僈嫋戻柤, 
--	M03屭媞.旛峫
--FROM
--	M01屌掕懏惈 AS M01屌掕懏惈_1 RIGHT OUTER JOIN
--	M03屭媞 ON M01屌掕懏惈_1.屌掕懏惈No = M03屭媞.儊儖儅僈嫋戻 LEFT OUTER JOIN
--	M01屌掕懏惈 ON M03屭媞.惈暿 = M01屌掕懏惈.屌掕懏惈No LEFT OUTER JOIN
--	C06屭媞摦岦昞 RIGHT OUTER JOIN
--	C07屭媞摦岦昞_屭媞 ON C06屭媞摦岦昞.摦岦No = C07屭媞摦岦昞_屭媞.摦岦No ON 
--	M03屭媞.屭媞No = C07屭媞摦岦昞_屭媞.屭媞No
--WHERE
--	M03屭媞.慻怐No = @慻怐No AND
--	C06屭媞摦岦昞.擭 = @擭 AND 
--	C06屭媞摦岦昞.寧 = @寧 AND 
--	C06屭媞摦岦昞.RFM庬暿 = @RFM庬暿 AND 
--	C06屭媞摦岦昞.RFM儔儞僋 = @RFM儔儞僋
--ORDER BY
--	M03屭媞.屭媞ID
SELECT
	M03屭媞.屭媞No,
	M03屭媞.屭媞ID,
	M03屭媞.巵柤,
	M03屭媞.巵柤僇僫, 
	M04懏惈.抣 AS 惈暿,
	M03屭媞.惗擭寧擔,
	M03屭媞.梄曋斣崋,
	M03屭媞.廧強, 
	M03屭媞.TEL,
	M03屭媞.儊乕儖傾僪儗僗,
	M04懏惈_1.抣 AS 儊儖儅僈嫋戻柤, 
	M03屭媞.旛峫
FROM
	M04懏惈 AS M04懏惈_1 RIGHT OUTER JOIN
	M03屭媞 ON M04懏惈_1.懏惈No = M03屭媞.儊儖儅僈嫋戻 LEFT OUTER JOIN
	M04懏惈 ON M03屭媞.惈暿 = M04懏惈.懏惈No LEFT OUTER JOIN
	C06屭媞摦岦昞 RIGHT OUTER JOIN
	C07屭媞摦岦昞_屭媞 ON 
	C06屭媞摦岦昞.摦岦No = C07屭媞摦岦昞_屭媞.摦岦No ON 
	M03屭媞.屭媞No = C07屭媞摦岦昞_屭媞.屭媞No
WHERE
	(C06屭媞摦岦昞.擭 = @擭) AND
	(C06屭媞摦岦昞.寧 = @寧) AND 
	(C06屭媞摦岦昞.RFM庬暿 = @RFM庬暿) AND 
	(C06屭媞摦岦昞.RFM儔儞僋 = @RFM儔儞僋)
ORDER BY
	M03屭媞.屭媞ID


---- FillBy摨堦審悢_屭媞ID
--SELECT
--	COUNT(屭媞ID) AS 屭媞cnt
--FROM
--	M03屭媞
--WHERE
--	慻怐No = @慻怐No AND
--	屭媞ID = @屭媞ID
SELECT
	COUNT(屭媞ID) AS Expr1
FROM
	M03屭媞
WHERE
	(屭媞ID = @屭媞ID)


---- Scalar屭媞Cnt
--SELECT
--	count(屭媞No) as 屭媞cnt
--FROM
--	M03屭媞
--WHERE
--	慻怐No = @慻怐No
SELECT
	count(屭媞No) as cnt
FROM
	M03屭媞

---- Scalar屭媞ID偑枹搊榐偺屭媞Cnt
SELECT
	count(屭媞No) as cnt
FROM
	M03屭媞
WHERE
	屭媞ID = '' OR
	屭媞ID IS NULL
