Compare Row with stored proc params

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 Development (2000)

Compare Row with stored proc params

Author

Topic

matrixr
Starting Member

26 Posts

Posted - 2005-07-29 : 19:49:00

i need to compare a row with supplied stored proc parameters
eg:
@address1, @adddress2, @suburb, @city <--supplied to stored proc

select address1, address2, suburb, city from addressbase where id=x

i need to compare the stored proc params with select query columns and if there is a difference insert the existing row to a history table

is there a way of doing this without lots of IF (@param =@select_colum) ...

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-29 : 20:28:18

There is a lot of ways to accomplish this but you can't avoid making the column value to parameter value comparison.
However, you could have sql do the comparison for you by way of a union statement. ie:


set nocount on
declare @addressbase table 
	(id int
	,address1 varchar(20)
	,address2 varchar(20)
	,suburb varchar(20)
	,city varchar(20))

insert @addressbase (id, address1, address2, suburb, city)
select	1
	,'123'
	,'someroad'
	,'sqlteam estates'
	,'unionville'

-----------------------------------------------------------
--input parameters
declare @address1 varchar(20)
	,@address2 varchar(20)
	,@suburb varchar(20)
	,@city varchar(20)

select	@address1 = '987'
	,@address2 = 'someroad'
	,@suburb = 'sqlteam estates'
	,@city = 'unionville'


--table variable to hold either 1 or 2 rows (depending on whether values match or not)
declare @tb table 
	(rowid int identity(1,1)
	,id int
	,address1 varchar(20)
	,address2 varchar(20)
	,suburb varchar(20)
	,city varchar(20))

--Only the distinct values will be selected from this union'd statement
--so if everything matches then only one row will be inserted into @tb
insert @tb (id, address1, address2, suburb, city)
select	id, address1, address2, suburb, city 
from	@addressbase
where	id=1
union --all
select	1, @address1, @address2, @suburb, @city 

--insert historyTable (id, address1, address2, suburb, city)
select	id, address1, address2, suburb, city
from	@tb
where	rowid > 1

EDIT:
although this is a heck of a lot easier:


select	id, address1, address2, suburb, city 
from	@addressbase
where	id=1
and	(address1 <> @address1
	or address2 <> @address2
	or suburb <> @suburb
	or city <> @city)

Be One with the Optimizer
TG

matrixr
Starting Member

26 Posts

Posted - 2005-07-29 : 22:13:16

awesome, that did the trick.

thanks TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources