Dynamic value assing in ON 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)

Dynamic value assing in ON clause

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-17 : 02:56:38

Hi,

The thing is that if the @Tags IS NULL don't condider the userID by assinging 1 = 1

if @Tags IS NOT NULL then assign Users.UserID = Items.UserID

but the Items.UserID is having some other value.

DECLARE @Tags VARCHAR(10)
DECLARE @UserNames VARCHAR(10)
BEGIN
SET @UserNames = 'babu'
SET @Tags = NULL
SELECT Items.DateAdded,
Items.Item ,
ItemTags.Tag
FROM dbo.Items
INNER JOIN dbo.ItemTags
ON Items.ItemID = ItemTags.ItemID
-- INNER JOIN dbo.Users ON Users.UserID = Items.UserID
INNER JOIN dbo.Users
ON
CASE
WHEN @Tags IS NOT NULL
THEN Users.UserID
ELSE 1
END = Items.UserID
WHERE ','+@UserNames+',' LIKE '%,'+Users.UserName+',%'
END

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 03:15:17

Is this what you want?

DECLARE @Tags VARCHAR(10)
DECLARE @UserNames VARCHAR(10)
BEGIN
SET @UserNames = 'babu'
SET @Tags = NULL
SELECT Items.DateAdded,
Items.Item ,
ItemTags.Tag
FROM dbo.Items
INNER JOIN dbo.ItemTags
ON Items.ItemID = ItemTags.ItemID
-- INNER JOIN dbo.Users ON Users.UserID = Items.UserID
INNER JOIN dbo.Users
ON Users.UserID= Items.UserID
OR @Tags IS NULL
WHERE ','+@UserNames+',' LIKE '%,'+Users.UserName+',%'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources