Check multiple tables for title

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 2008 Forums

Transact-SQL (2008)

Check multiple tables for title

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-06-03 : 09:50:36

Hi, I want to check 3 tables for a title

So we got:
table 1
id
title
description

table 2
id
title
description

table 3
id
title
description

The sp should return true if the @title matches a record in one of the three tables.
How would I do that?

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-06-03 : 09:55:43

Right now, I've got:

@Title nvarchar(255)
AS

select case when EXISTS(
select * from dbo.Table1 where LOWER(Title)=LOWER(@Title)
) then 1 else 0 end

but it should be extended to 2 more tables.

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-06-06 : 06:53:38

No-one's got a suggestion?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-06 : 08:11:29

Your approach of using "exists" is the best approach in this case. To extend it to other tables, I would use the low-tech approach such as this:


@Title	nvarchar(255),
@found  int = null OUTPUT
AS

set @found = 0;

if exists (select * from dbo.Table1 where LOWER(Title)=LOWER(@Title))
   set @found = 1;
if @found = 1
   return;

if exists (select * from dbo.Table2 where LOWER(Title)=LOWER(@Title))
   set @found = 1;
if @found = 1
   return;

if exists (select * from dbo.Table3 where LOWER(Title)=LOWER(@Title))
   set @found = 1;

return;

There may be some other slight efficiencies that can be found, for example, if your collation is known to be case-insensitive, you may not need to convert to lower. Other than that, this is probably the most efficient.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-06 : 13:33:36

Normalized tables are your FRIENDS!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-06 : 14:09:38

couldn't agree more with Don! if your database does not have normalized well thought out tables and relationships you will be doing cirque du soleil code and sprocs.

it's like trying to use gum to patch up a leaking submarine. yes it will work for a wee bit

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources