Dynamic where clause - 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)

Dynamic where clause

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-22 : 10:07:47

Hi Guru's

Is the only possible way to do this in a stored procedure using dynamic sql?

SELECT name, (SELECT COUNT(id) FROM families WHERE
IF len(name) = 8
name = 'This name'
ESLE
name = 'That name'
) FROM names

I know this isn't the best demo code. Basically I am trying to change the where clause in the count statement depending on the length of the 'name' in the first column!

madhivanan
Premature Yak Congratulator

22864 Posts

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

Some like this

SELECT name,
(SELECT COUNT(id) FROM families
WHERE name = case when len(name) = 8 then 'this name' ELSE 'that name' end) as NameCount
FROM names

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-22 : 10:33:52

Doesn't make sense...follow the instruction in the hint link below....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-22 : 10:35:39

Thanks madhivanan,

How about the following?

SELECT name, (SELECT COUNT(id) FROM families WHERE
IF len(name) = 8
this = that
ESLE
that = this
) FROM names

I think the only way is a dynamic query. Would you agree?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-22 : 10:40:38

It is difficult to give solution until you give more information

Post the table structures, sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-23 : 02:22:20

Sorry!

I have solved the problem! Thanks I will follow these guidelines in future!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-23 : 02:36:02

quote:
Originally posted by John Sourcer

Sorry!

I have solved the problem! Thanks I will follow these guidelines in future!

Can you show us the query you used?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources