Best way to code if exists - 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)

Best way to code if exists

Author

Topic

cafmike
Starting Member

2 Posts

Posted - 2011-03-03 : 12:06:40

I need to search a product code field, if nothing is found i want to search the description field like

select id from table where productcode = '1234'
if empty then select id from table where description like '%1234%'

what would be the best way to do this in a stored procedure?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-03 : 12:37:42

One way, maybe not the best:

DECLARE @found TABLE(ID int not null)
INSERT @found SELECT id FROM table WHERE productcode='1234'
INSERT @found SELECT id FROM table WHERE NOT EXISTS(SELECT * FROM @found) AND description LIKE '%1234%'

It the first query returns results then the 2nd query won't run. An alternate version of the last line:

IF NOT EXISTS(SELECT * FROM @found) INSERT @found SELECT id FROM table WHERE description LIKE '%1234%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources