stored procedure based on 2 conditions

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)

stored procedure based on 2 conditions

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 03:59:03

Hi All

I am following your example to replace the IF ELSE with an OR, which is working excellently.

However now I need to write another condition which I cannot manage to do.

I have 2 vars @StatusNew and @StatusUpdated. If @StatusNew = 1, then I have to attach (AND status = 1). If @StatusUpdated = 1, then I have to attach (AND status=2). If both of them are 1, then I have to attach (AND status = 1 OR status = 2). How can I achive that?

At the moment I am doing something like this:-

AND ((@StatusNew = 0) or (@StatusNew <> 0 AND StatusId = 1))
AND ((@StatusUpdated = 0) or (@StatusUpdated <> 0 AND StatusId = 2))

OR ((@StatusNew = 1 AND StatusId = 1) AND (@StatusUpdated=1 OR StatusId = 2))

The first 2 parts are working, where there is the AND, however the last part, where there is the OR, is taking ages to run the query and not returning results.

Thanks for your help and time

Johann

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 04:20:23

Build a truth table first, and then rewrite your WHERE clause.

WHERE	(@StatusNew = 0 AND @StatusUpdated = 0)
	OR (@StatusNew <> 0 AND Status = 1)
	OR (@StatusUpdates <> 0 AND Status = 2)
	OR (@StatusNew = 1 AND Status = 1 AND @StatusUpdates = 1 AND Status = 2)

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 04:25:32

Hi Peso

How to construct the where clause?

Sorry I am a newbie in SQL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 04:32:08

First, before answering any other question, did the new WHERE clause I posted work for you?

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 04:37:40

no it did not

I have already a WHERE clause before that, so I did

AND (@StatusNew = 0 AND @StatusUpdated = 0)
OR (@StatusNew <> 0 AND Status = 1)
OR (@StatusUpdated<> 0 AND Status = 2)
OR (@StatusNew = 1 AND Status = 1 AND @StatusUpdated= 1 AND Status = 2)

But did not work

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-23 : 04:42:23

quote:
Originally posted by monfu

no it did not

I have already a WHERE clause before that, so I did

AND (@StatusNew = 0 AND @StatusUpdated = 0)
OR (@StatusNew <> 0 AND Status = 1)
OR (@StatusUpdated<> 0 AND Status = 2)
OR (@StatusNew = 1 AND Status = 1 AND @StatusUpdated= 1 AND Status = 2)

But did not work

Dont append this to your where clause. Use this fully as shown by Peso

Madhivanan

Failing to plan is Planning to fail

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 04:43:44

but I already have another where clause in the statement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 04:44:56

Please post FULL query!

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 04:52:15

Hi Peso

Here it is

SELECT DISTINCT p.pageId, ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum,
MAX(er.fk_StatusId) as StatusId,
FROM pages as p,resources as er
WHERE p.fk_resourceid=er.resourceid
--AND ((@StatusNew = 0) or (@StatusNew <> 0 AND StatusId = 1))
--AND ((@StatusUpdated = 0) or (@StatusUpdated <> 0 AND StatusId = 2))
--OR ((@StatusNew = 1 AND StatusId = 1) AND (@StatusUpdated=1 OR StatusId = 2))
AND (@StatusNew = 0 AND @StatusUpdated = 0)
OR (@StatusNew <> 0 AND fk_textLocStatusId = 1)
OR (@StatusUpdated <> 0 AND fk_textLocStatusId = 2)
OR (@StatusNew = 1 AND fk_textLocStatusId = 1 AND @StatusUpdated = 1 AND fk_textLocStatusId = 2)

GROUP BY p.pageid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 04:55:15

quote:
Originally posted by monfu

Hi Peso

Here it is

SELECT DISTINCT p.pageId, ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum,
MAX(er.fk_StatusId) as StatusId,
FROM pages as p,resources as er
WHERE p.fk_resourceid=er.resourceid
--AND ((@StatusNew = 0) or (@StatusNew <> 0 AND StatusId = 1))
--AND ((@StatusUpdated = 0) or (@StatusUpdated <> 0 AND StatusId = 2))
--OR ((@StatusNew = 1 AND StatusId = 1) AND (@StatusUpdated=1 OR StatusId = 2))
AND ((@StatusNew = 0 AND @StatusUpdated = 0)
OR (@StatusNew <> 0 AND fk_textLocStatusId = 1)
OR (@StatusUpdated <> 0 AND fk_textLocStatusId = 2)
OR (@StatusNew = 1 AND fk_textLocStatusId = 1 AND @StatusUpdated = 1 AND fk_textLocStatusId = 2))

GROUP BY p.pageid

i think you need to put the different combinations inside a braces

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:00:40

And if you learn to write proper ANSI joins, you wouldn't have had this problem

SELECT		p.pageId,
		ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum,
		MAX(er.fk_StatusId) as StatusId,
FROM		pages as p
INNER JOIN	resources as er on er.resourceid = p.fk_resourceid
WHERE		(@StatusNew = 0 AND @StatusUpdated = 0)
		OR (@StatusNew <> 0 AND fk_textLocStatusId = 1)
		OR (@StatusUpdated <> 0 AND fk_textLocStatusId = 2)
		OR (@StatusNew = 1 AND fk_textLocStatusId = 1 AND @StatusUpdated = 1 AND fk_textLocStatusId = 2)
GROUP BY	p.pageid

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 05:10:15

Hi peso

Sorry but I am new to SQL, that's why I did the joins like that.

So if I need to add another WHERE clause, for example

WHERE p.fk_projectid=@projectId

how can I do that?

Thanks for all your help

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 05:18:23

hey peso

managed to do it, with the new inner joins!

Just 1 thing, when both statuses are set to 0, I want to retreive everything. At the moment its just retreiving all the statuses which are not 1 or 2. However, when both are 0, I want to retreive all statuses, inclusive of 1 and 2.

Can you show me how to do it ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:30:47

[code]SELECT p.pageId,
ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum,
MAX(er.fk_StatusId) as StatusId,
FROM resources as er
INNER JOIN pages as p on p.fk_resourceid = er.resourceid
AND p.fk_projectid = @projectId
WHERE (@StatusNew = 0 AND @StatusUpdated = 0)
OR (@StatusNew <> 0 AND fk_textLocStatusId = 1)
OR (@StatusUpdated <> 0 AND fk_textLocStatusId = 2)
OR (@StatusNew = 1 AND fk_textLocStatusId = 1 AND @StatusUpdated = 1 AND fk_textLocStatusId = 2)
GROUP BY p.pageid[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:33:39

Or keep your original query and add a outer clause like this

SELECT DISTINCT p.pageId, ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum, 
MAX(er.fk_StatusId) as StatusId, 
FROM pages as p,resources as er
WHERE p.fk_resourceid=er.resourceid 
and p.fk_projectid=@projectId
AND (
		(@StatusNew = 0 AND @StatusUpdated = 0)
		OR (@StatusNew <> 0 AND fk_textLocStatusId = 1)
		OR (@StatusUpdated <> 0 AND fk_textLocStatusId = 2)
		OR (@StatusNew = 1 AND fk_textLocStatusId = 1 AND @StatusUpdated = 1 AND fk_textLocStatusId = 2)
	)
GROUP BY p.pageid

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 05:44:22

Hi Peso

Still returning only statuses > 2 when StatusNew = 0 and StatusUpdated = 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:49:53

If @StatusNew = 0 and @StatusUpdated = 0 then all records are fetched because there is no filter on an existing column.

Are you sure you are not in conflict with the MAX(er.fk_StatusId) as StatusId SELECT statement?
Even if there are other status values, you only return max value!

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-23 : 05:56:08

Hi Peso

you are right! The MAX was causing the error

Thanks a million! Finally its working!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources