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

Transact-SQL (2005)

dynamic where clause

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-07-23 : 10:49:59

Hi

I have the following select statement that returns the country_id:

SELECT	   DISTINCT Candidate.Country_id 
FROM         dbo.Candidate_121 INNER JOIN
                      dbo.Candidate ON dbo.Candidate_121.Candidate_id = dbo.Candidate.Candidate_id
			 INNER JOIN
                      dbo.Countries ON dbo.Candidate.Country_id = dbo.Countries.Country_id
WHERE     (dbo.Candidate.Sub_status = 30) 
	   AND (dbo.Candidate_121.IIB_decision_yes = 1) 
	   AND (dbo.Candidate_121.Candidate_decision_yes = 1) 
	       AND (dbo.Candidate_121.date_email_sent IS NULL) AND 
            (dbo.Candidate_121.Admin_Address is NULL)
	    AND (dbo.Candidate.Status <> @Status_Close)

The sp doesn't get any parameters. I now have to modify the code so that if the country_id=2 or 37 i mustn't include the conditions
AND (dbo.Candidate_121.IIB_decision_yes = 1)
AND (dbo.Candidate_121.Candidate_decision_yes = 1)
in the where clause.

How can i accomplish it?

thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-23 : 11:11:39

SELECT DISTINCT Candidate.Country_id
FROM dbo.Candidate_121
INNER JOIN
dbo.Candidate
ON dbo.Candidate_121.Candidate_id = dbo.Candidate.Candidate_id
INNER JOIN
dbo.Countries
ON dbo.Candidate.Country_id = dbo.Countries.Country_id

WHERE
( country_id not in (2,37)
and (dbo.Candidate.Sub_status = 30)
AND (dbo.Candidate_121.IIB_decision_yes = 1)
AND (dbo.Candidate_121.Candidate_decision_yes = 1)
AND (dbo.Candidate_121.date_email_sent IS NULL) AND
(dbo.Candidate_121.Admin_Address is NULL)
AND (dbo.Candidate.Status <> @Status_Close) )

OR

( (dbo.Candidate.Sub_status = 30)
AND (dbo.Candidate_121.date_email_sent IS NULL) AND
(dbo.Candidate_121.Admin_Address is NULL)
AND (dbo.Candidate.Status <> @Status_Close)
and country_id in (2,37) )

Jim

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-07-24 : 02:35:11

Silly me :-) What a simple solution. Thanks alot for the help.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

MalaSuerte
Starting Member

1 Post

Posted - 2007-07-30 : 13:16:25

quote:
Originally posted by collie

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

I'm so sorry for you and your pal Whisky. The only thing that makes living through times like that with your dog tolerable is Faith and all the good times you two had together.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-09-16 : 12:46:59

MalaSuerte I only saw your post now. Thanks for the message. It's very hard. I think about him everyday.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 01:41:03

http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-09-17 : 03:49:16

Thanks for the link.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources