Find duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find duplicate records

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-05-25 : 08:47:43

create table TestTable (ID INT IDENTITY, StartKms NUMERIC(18,2), EndKms NUMERIC(18,2))

INSERT INTO TestTable(StartKms, EndKms )

SELECT 6, 8 UNION ALL
SELECT 10, 20 UNION ALL
SELECT 20, 25

--Iam getting 'Duplicate' it is correct(OK)
SELECT * from dbo.ISDUPLICATE(10, 20)

--Should show 'Duplicate' but is should gives 'NotDuplicate'
SELECT * from dbo.ISDUPLICATE(8, 9)
--Should show 'Duplicate' but is should gives 'NotDuplicate'
SELECT * from dbo.ISDUPLICATE(8, 10)
--Should show 'Duplicate' but is should gives 'NotDuplicate'
SELECT * from dbo.ISDUPLICATE(8, 11)

--data that we can insert like this 0 - 2 - 6 - 8 - 10 - 20 - 25

DROP FUNCTION dbo.ISDUPLICATE

drop table TestTable

Function created

CREATE FUNCTION dbo.ISDUPLICATE (@StartKMs INT,
@EndKms INT)
RETURNS VARCHAR(20) AS
BEGIN
DECLARE @ISDuplicateKms VARCHAR(20),
@ReturnMsg VARCHAR(20)

SELECT @ISDuplicateKms = Id
FROM TestTable
WHERE StartKMs BETWEEN @StartKMs AND @EndKms
OR EndKms BETWEEN @StartKMs AND @EndKms

IF ISNULL( @ISDuplicateKms, 'NotDuplicate') = 'NotDuplicate'
BEGIN
SET @ReturnMsg = 'NotDuplicate'
END
ELSE
BEGIN
SET @ReturnMsg = 'Duplicate'
END

RETURN @ReturnMsg
END
GO

Thanks in advance,
Babu Kumarasamy

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 09:46:57

Probably due to comparing int with numeric.
Shouldn't but it's the only thing I can think of.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 09:51:20

Umm - on second thoughts
You are calling a table valued function but have created a scalar.
The function is schema vehicle and you are calling it with schema dbo.

You aren't calling this function - probably another one is hanging around.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

baburk
Posting Yak Master

108 Posts

Posted - 2009-05-26 : 00:41:54

quote:
Originally posted by nr

Umm - on second thoughts
You are calling a table valued function but have created a scalar.
The function is schema vehicle and you are calling it with schema dbo.

You aren't calling this function - probably another one is hanging around.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I had changed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources