SQL stored Procedure - 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 2000 Forums

SQL Server Administration (2000)

SQL stored Procedure

Author

Topic

sr_aneesh
Starting Member

17 Posts

Posted - 2005-01-22 : 08:28:10

i want to make a standard stored procedure to check the duplication of records in anytable.
my code is as follows wich gives the error : Err 137 ; Must declare the variable @tblname

CREATE PROCEDURE sp_checkdup
@tblname varchar(25),
@fldname varchar(25),
@fldval varchar(25),
@spname varchar(25)
AS
if exists (select @fldname from @tblname where @fldname=@fldval)
return 555
else
execute @spname
GO

what could be the problem .....

thanx in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-22 : 08:32:42

In order for it to work with ANY table, it would have to be:

a. extremely complicated and impractical, or
b. written in dynamic sql, or
c. both

A BETTER thing to do is: design your tables to PREVENT duplicates in the first place. Primary keys, unique constraints, even triggers can help you accomplish this, and it's part of a proper database design anyway. Then you don't even need a duplicate finder.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-22 : 09:44:08

Everything rob says.
If you have autonumbers,uniqueidentifiers and such as as pk,
then You are now in the position of considering unique indexes on the "natural" keys of your tables.

This will find dups, (and in some cases nondups, but that has low propbability)

select * from tablename
where binary_checksum(*) in(
	select bcs
	from (select binary_checksum(*) as bcs from tablename) cs
	group by bcs
	having count(*) > 1)

You could tweak the above to use columnlist iof *, and exclude uniqueidentifiers,timestamp,identity columns.

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-24 : 14:22:06

[code]
I can't agree with robvolk more.
However, I also can't resist these little challenges.

The reason your original post generated errors, sr_aneesh, is that you can't say:
Select * from @var

You have to say: (dynamic sql statement)
exec('Select * from ' + @var)

Here is an SP that will check for dupes in any table by whatever
columns you want to pass in. As you can see the bulk of the work
is validating the input parameters. The actual checking for dupes
is a pretty simple dynamic statment.

Here is a sample call for the compiled sp (below):

declare @retCode int
exec @retCode = MrsDupeCheck
@tbName = '<anyTable>'
,@colList = '<comma seperated list of columns>' --NULL will use All columns in table
SElect @retCode

--For those of you fortunate enough to not be poisoned by american television
--disregard the name of this sp...
If Object_ID('dbo.MrsDupeCheck') > 0
Drop Proc dbo.MrsDupeCheck
GO
create proc MrsDupeCheck
@tbName varChar(50)
,@colList varchar(8000) = null
as

declare @errMsg varchar(256)
,@col varchar(8000)
,@pos int
,@missingCols varChar(8000)

declare @cols Table (col varChar(50))
Create Table #dupes (n int)
--======================================================
--validate parameters

--make sure the table provided is valid
if Object_ID(@tbName) is null
Begin
set @errMsg = 'The table ''' + @tbName + ''' does not exist.'
raiserror(@errMsg, 17, 1)
return -1
End

--Make sure the column list provided is valid
if @colList is NOT NULL
Begin
--Parse the comma seperated List of columns and add them to @cols table
--(this parsing process is a good candidate for a function)
Select @pos = 1
,@col = ''

while @pos <= len(@ColList)
Begin
if subString(@ColList, @pos, 1) = ','
begin
-- this is a seperator so add column and reset @col
insert @cols values (@col)
set @col = ''
end
else
-- add current character to @col
set @col = @col + subString(@ColList, @pos, 1)
set @pos = @pos + 1
End

--insert last or only column
insert @Cols values (@col)

--make sure all columns passed exist in @tbName
Select @missingCols = coalesce(@missingCols + ', ' + c.col, c.col)
From @cols c
Left JOIN sysColumns sc
ON sc.id = Object_ID(@tbName)
AND sc.name = c.col
where sc.name is NULL

If len(@missingCols) > 0
Begin
set @errMsg = 'The table ''' + @tbName + ''' does not contain the following column(s): (' + @missingCols + ')'
raiserror(@errMsg, 17, 1)
return -1
End
End

--======================================================
--if no column(s) provided, use all columns in table
--This is a trick (well documented in these forums) for csv-ing records to string
if @colList is NULL
Select @collist = coalesce(@collist + ',' + name, name)
from sysColumns
where id = Object_ID(@tbName)

--======================================================
--Just get the first dupe because we just need to know if any exist
set rowcount 1

insert #dupes (n)
exec('
Select 1 from ' + @tbName + '
Group by ' + @collist + '
having count(*) > 1
')

--don't forget to reset the rowcount
set rowcount 0

if Exists(Select * from #dupes)
Return 555

return 0

GO
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources