improvement in the given query with null compariso

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)

improvement in the given query with null compariso

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-10-11 : 06:21:34

Alter Procedure [dbo].[USP_CFB_GetAllClientViaClientRegistrationFileId] @FkTemplateId INT
, @FkClientRegisterationFileInfoId INT
As
Begin
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Set NOCOUNT On;

If( @FkClientRegisterationFileInfoId Is Null )
Begin
Select dbo.TableOne.FkUserId
, dbo.TableOne.IsLinkSent
, dbo.TableTwo.MatterId
, dbo.TableOne.TableOneId
, u.[Name]
, u.Email
From dbo.TableOne
Inner Join uim.[User] u
On u.UserId = TableOne.FkUserId
Left Join dbo.TableTwo
On dbo.TableOne.TableOneId = dbo.TableTwo.FkTableOneId
Where TableOne.FkTemplateId = @FkTemplateId
And dbo.TableOne.FkClientRegisterationFileInfoId IS NULL
End
Else
Begin
Select dbo.TableOne.FkUserId
, dbo.TableOne.IsLinkSent
, dbo.TableTwo.MatterId
, dbo.TableOne.TableOneId
, u.[Name]
, u.Email
From dbo.TableOne
Inner Join uim.[User] u
On u.UserId = TableOne.FkUserId
Left Join dbo.TableTwo
On dbo.TableOne.TableOneId = dbo.TableTwo.FkTableOneId
Where TableOne.FkTemplateId = @FkTemplateId
And dbo.TableOne.FkClientRegisterationFileInfoId = @FkClientRegisterationFileInfoId
End
End

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-11 : 07:30:16

One shortcut is

Select dbo.TableOne.FkUserId
, dbo.TableOne.IsLinkSent
, dbo.TableTwo.MatterId
, dbo.TableOne.TableOneId
, u.[Name]
, u.Email
From dbo.TableOne
Inner Join uim.[User] u
On u.UserId = TableOne.FkUserId
Left Join dbo.TableTwo
On dbo.TableOne.TableOneId = dbo.TableTwo.FkTableOneId
Where TableOne.FkTemplateId = @FkTemplateId
And (dbo.TableOne.FkClientRegisterationFileInfoId = @FkClientRegisterationFileInfoId or @FkClientRegisterationFileInfoId IS NULL)

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-10-11 : 07:31:42

My question is: what does the user want if he sets @FkClientRegisterationFileInfoId to NULL?

Maybe user wants NO filter on FkClientRegisterationFileInfoId so that in this case your
"And dbo.TableOne.FkClientRegisterationFileInfoId IS NULL"
is wrong?

Too old to Rock'n'Roll too young to die.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-10-11 : 07:35:43

in my case FkClientRegisterationFileInfoId is a nullable field.
In some case FkTemplateId has value and FkClientRegisterationFileInfoId not while for other business
FkTemplateId and FkClientRegisterationFileInfoId both has values.
They comprises of different sort of business data so i can't put or in the clasue
And (dbo.TableOne.FkClientRegisterationFileInfoId = @FkClientRegisterationFileInfoId or @FkClientRegisterationFileInfoId IS NULL)

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-22 : 09:18:37

Maybe you can check if a dynamic query performs better in your case.

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources