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
Go to Top of Page

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.
Go to Top of Page

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)

Go to Top of Page

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
Go to Top of Page
   

- Advertisement -