Contoh Store Procedure Dengan IF - SQL Server

USE [DATBASESNYA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetUploadMember]
       (@StartMemberNo Varchar (100)
       ,@EndMemberNo Varchar (100)
       ,@StartDate SmallDateTime
       ,@EndDate SmallDateTime)
AS

BEGIN

-- ini jika ingin ngetes ngejalanin SP nya
--set @StartMemberNo = NULL
--set @EndMemberNo = NULL
--set @StartDate = '2010-05-21'
--set @EndDate = '2011-06-30'

--Filter Jika StartMemberNo blank atau tidak diisi
If @StartMemberNo = ''
    Begin
        Set @StartMemberNo = NULL
    End
--Filter Jika EndMemberNo blank atau tidak diisi
If @EndMemberNo = ''
    Begin
        Set @EndMemberNo = NULL
    End

--Filter Jika semua parameter tidak di isi
If @StartMemberNo IS NULL and @EndMemberNo IS NULL and @StartDate IS NULL and @EndDate IS NULL
    Begin
        Set @StartDate= cast(getdate() as Date)
    End

--Filter jika Startmemberno <> '' dan EndMemberno NULL atau ''
If @StartMemberNo <> '' and @EndMemberNo IS NULL
    Begin
        Set @EndMemberNo=@StartMemberNo
    End

--Filter jika Startmemberno NULL atau '' dan EndMemberno <> ''
If @StartMemberNo IS NULL and @EndMemberNo <> ''
    Begin
        Set @StartMemberNo=@EndMemberNo
    End

--Filter jika StartDate <> '' dan EndDate NULL atau ''
If @StartDate <> '' and @EndDate IS NULL
    Begin
        Set @EndDate=@StartDate
    End

--Filter jika StartDate NULL atau '' dan EndDate <> ''
If @StartDate IS NULL and @EndDate <> ''
    Begin
        Set @StartDate=@EndDate
    End

    Select MSR.[Card Number]
          ,CT.[Name]
          ,CT.[E-Mail] [Email]
          ,CT.[Phone No_] [Mobile Phone]
          ,CT.[Address] + ' '+ CT.[Address 2] [Address]
          ,CT.[City]
          ,MSR.[Ending Date]
    From [Nyamnyung Store, PT$MSR Card Link Setup] MSR With (Nolock)
         INNER JOIN [Nyamnyung Store, PT$Contact] CT With (Nolock) on MSR.[Link No_] = CT.[No_]
    Where [Card Number] >= Coalesce(@StartMemberNo,[Card Number])
          AND [Card Number] <= Coalesce(@EndMemberNo,[Card Number])
          AND [Date Created] >= Coalesce(@StartDate,[Date Created])
          AND [Date Created] <= Coalesce(@EndDate,[Date Created])
    Order By [Card Number]

END
GO