|
저는 사용자 정의 함수를 작성 중 입니다.. 일단 사용자 정의 함수를 보면서 설명 드리겠씁니다.
set ANSI_NULLS ONset QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[saleDay]
(
@date varchar(50)
)
RETURNS @TempCal TABLE
(
OncOneSale bigint NOT NULL,
OncOneSum bigint NOT NULL
)
AS
BEGIN
DECLARE @OncOneSale int;
DECLARE @OncOneSum int;
SELECT @OncOneSale = COUNT(ONCENTLEV_LEVGATE) FROM ONCENTLEV
WHERE ONCENTLEV_LEVDATE BETWEEN @date +' 00:00:00' AND @date + ' 23:59:59'
AND ONCENTLEV_LEVGATE = 'MainOncLevGate' AND ONCENTLEV_SALETYPE = 1
AND ONCENTLEV_SALEFEE <> 0 AND ONCENTLEV_LEVFEE <> 0;
SELECT @OncOneSum = SUM(ONCENTLEV_TOTALFEE) FROM ONCENTLEV
WHERE ONCENTLEV_LEVDATE BETWEEN @date + ' 00:00:00'
AND @date+ ' 23:59:59' AND ONCENTLEV_LEVGATE = 'MainOncLevGate' AND
ONCENTLEV_SALETYPE = 1 AND ONCENTLEV_SALEFEE <> 0 AND ONCENTLEV_TOTALFEE <> 0;
IF @OncOneSale IS NULL SET @OncOneSale = 0;
IF @OncOneSum IS NULL SET @OncOneSum = 0;
INSERT INTO @TempCal( OncOneSale, OncOneSum) values( @OncOneSale , @OncOneSum );
RETURNEND
go
use parking
go
select * from saleDay('2008-07-21')
go
이렇게 함수를 실행시켰더니, 0이란 숫자 밖에 값이 안 나오내용..^^
SELECT COUNT(ONCENTLEV_LEVGATE) FROM ONCENTLEV WHERE ONCENTLEV_LEVDATE BETWEEN '2008-
07-21 00:00:00' AND '2008-07-21 23:59:59' AND ONCENTLEV_LEVGATE = 'MainOncLevGate' and
ONCENTLEV_SALETYPE = 1 AND ONCENTLEV_SALEFEE <> 0 AND ONCENTLEV_LEVFEE <> 0;
SELECT SUM(ONCENTLEV_TOTALFEE) FROM ONCENTLEV WHERE ONCENTLEV_LEVDATE BETWEEN '2008-
07-21 00:00:00' AND '2008-07-21 23:59:59' AND ONCENTLEV_LEVGATE = 'MainOncLevGate' AND
ONCENTLEV_SALETYPE = 1 AND ONCENTLEV_SALEFEE <> 0 AND ONCENTLEV_TOTALFEE <> 0;
두 쿼리를 실핼시킬때는 값이 나오는 함수내에서의 두 select구문은 0이라는 값이 나오는 이유를 모르겠습니다.혹시 아시는
분은 답변좀 갈켜주세용..^^
|
