question about dynamic WHERE clause

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)

question about dynamic WHERE clause

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-07 : 11:20:12

Hi,

Currently I have a query written (thx to peso! ) as seen below.

I know that dynamic WHERE clauses are a no-no usually in SQL, but I think this case is different.

As you can see in the query below, the WHERE clause essentially depends on what information is populated into the temp table 'friends'

I would like to have a few different ways of populating this table (different select statements, that query different tables)

Is it ok to pass a parameter to this SPROC, that will determine how to fill the tables? There will be about 5 different options.

I'm fully preparded to make it just 5 different queries, if it will mean better execution plan and performance..... That's what Im used to doing, so thought I would double check here first...

any thoughts appreciated !

thanks again!
Mike123

CREATE PROCEDURE dbo.uspGetUserActivityList
(
@userID INT
)
AS

SET NOCOUNT ON

DECLARE @Friends TABLE
(
friendID INT PRIMARY KEY CLUSTERED,
nameOnline VARCHAR(15)
)

INSERT @Friends
(
friendID,
nameOnline
)
SELECT f.friendID,
u.nameOnline
FROM Friends AS f
INNER JOIN Users AS u ON u.userID = f.friendID
WHERE f.userID = @UserID

-- Present the resultset
SELECT f.nameOnline + ' added a new photo ' + CONVERT(CHAR(19), ep.maxDate, 120) AS msg,
ep.MaxDate AS theDate,
1 AS theType
FROM @Friends AS f
INNER JOIN (
SELECT userID,
MAX(photoDate) AS maxDate
FROM ExtraPhotos
GROUP BY userID
) AS ep ON ep.userID = f.friendID

UNION ALL

SELECT f.nameOnline + ' added a new friend ' + CONVERT(CHAR(19), u.maxDate, 120) AS msg,
u.MaxDate AS theDate,
2 AS theType
FROM @Friends AS f
INNER JOIN (
SELECT userID,
MAX(dateAdded) AS maxDate
FROM Friends
GROUP BY userID
) AS u ON u.userID = f.friendID

ORDER BY theType,
theDate DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 11:56:58

which WHERE clause you're talking about? i can see only single WHERE clause but didnt understand what you mean by dynamicity in it. can you explain?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-07 : 12:07:12

Hi Visakh15,

I guess my topic title is misguiding. It's not really a dynamic where clause, as its not just the WHERE clause thats changing, its the whole select statement as seen above. ( i just bolded it)

I would like to have a few options for this select statement ( selecting from diff tables etc) This effectively controls my where clause as its later used in an INNER JOIN.

Thanks for your help!
Mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:20:01

seems like what you want is set of if ...else statements and based on condition you need INSERT..SELECT inside each with tables varying based on situation.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-07 : 12:22:25

quote:
Originally posted by visakh16

seems like what you want is set of if ...else statements and based on condition you need INSERT..SELECT inside each with tables varying based on situation.

yes thats what I want, but my question is whether this is ok for performance reasons ? I am worried about the execution plan.

Thanks again!,
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:26:22

whats the basic difference between queries for various conditions/ is it just one of table that chnages?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-07 : 12:32:49

Well, I might not be 100% on the money here but.....

You could either have a IF / THEN / ELSE section with different SELECTS / INSERTS etc. depending on your inputs. If you did this then I think it would be advisable to create the procedure with the hint (WITH RECOMPILE) as your second run may have no use for the execution plan delivered by the first.

OR

Use an if then else section and inside each logical block call another sp to do the work. That way that child sp would have a consistent execution plan. Something like


IF @condition1 = 1 EXEC myStoredProcA
IF @condition1 = 2 EXEC myStoredProcb
ELSE EXEC myStoredProcc

That way I think you could still benefit from a properly tuned plan. I might be being naive and if I am I'm sure someone will be able to point it out! :)

-------------
Charlie

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-07 : 12:36:59

quote:
Originally posted by visakh16

whats the basic difference between queries for various conditions/ is it just one of table that chnages?

yes, just populating the table from different sources..

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-07 : 12:37:50

perhaps I'm just better writing a few different SPROCS ? It's not the end of the world if I do have to ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 13:04:38

quote:
Originally posted by mike123

perhaps I'm just better writing a few different SPROCS ? It's not the end of the world if I do have to ...

that might prove to be the best solution considering the maintainability aspect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources