Efficient way to do this? - 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

Transact-SQL (2000)

Efficient way to do this?

Author

Topic

Aerathi
Starting Member

14 Posts

Posted - 2005-08-30 : 10:52:05

Hi, I'm rewriting some procedures that were just not very well thought out when they were written. I've run into some very odd ways of doing things, and while streamlining I ran into one that I'm not sure is possible to do any better, but wanted your input.

Basically, I'm selecting two values from a table. Depending on whether the vaule is null, I have to attempt another select varying the values of the where clause. This happens approximately seven times.

It looks like this:
SELECT @Amount = Amount, @Amount2 = Amount2 FROM Table WHERE ClientBranch = @ClientBranch AND State = @State AND ProdType = @ProdType

IF @Amount IS NULL
SELECT @Amount = Amount, @Amount2 = Amount2 FROM Table WHERE ClientBranch = @ClientID AND State = @State AND ProdType = @ProdType

IF @Amount IS NULL
SELECT @Amount = Amount, @Amount2 = Amount2 FROM Table WHERE ClientBranch = @ClientBranch AND State = 'ALL' AND ProdType = @ProdType

IF @Amount IS NULL
SELECT @Amount = Amount, @Amount2 = Amount2 FROM Table WHERE ClientBranch = @ClientID AND State = 'ALL' AND ProdType = @ProdType

IF @Amount IS NULL
SELECT @Amount = Amount, @Amount2 = Amount2 FROM Table WHERE ClientBranch = 'All' AND State = @State AND ProdType = @ProdType

So on and so forth. All parameters will have values, it's just that using them in the where clause will not neccesarily return a recordset. I know it's poor table design, but I have what they give me to work with.

Any ideas on how to make that mess of logic more efficient and smaller, or do I basically follow the same kind of pattern?

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-30 : 11:41:52

Does this work? If not, post the DDL as well as DML (insert statements of sample data) and desired results based on your DDL/DML


select	top 1 
	@Amount = Amount
	,@Amount2 = Amount2
from	(
	select	Amount
		,Amount2
		,ord = case
			when ClientBranch = @ClientBranch and State = @State then 1
			when ClientBranch = @ClientID and State = @State then 2
			when ClientBranch = @ClientID and State = @State then 3
			when ClientBranch = @ClientBranch and State = 'All' then 4
			--etc...
			end
	from	myTable
	where	ClientBranch in (@ClientBranch, 'All')
	and	State in (@State, 'All')
	and	ProdType = @ProdType
	) a
order by ord

Be One with the Optimizer
TG

Aerathi
Starting Member

14 Posts

Posted - 2005-08-30 : 12:50:16

Beautiful. Just added the @ClientID in the 'ClientBranch IN' clause. Works perfect.

I'll remember this one for simliar queries.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources