Contoh Store Procedure Dengan Dynamic Query - SQL Server

USE [DATABASENYA]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDataMember]
     (
      @Status Varchar (max)
      ,@StartJoin datetime
      ,@EndJoin datetime
      )
AS
BEGIN
SET NOCOUNT ON;
declare @Query varchar(max)   
declare @filter varchar(max)

if @Status=''set @filter=null


if @Status <>''
begin   
    if @Status='Not Active'
    begin
        set @filter='And [Ending Date]<getdate()'
    end
    else
    if @Status='Active'
    begin
        set @filter='And [Ending Date]>=getdate()'
    end
   
end
else
begin
    set @filter=''
end
   

set @Query= 
    'SELECT
    [CardNumber],
    [Name],
    [Address],
    [City],
    [Post Code],
    [Phone No_],
    [Date Created],
    [Ending Date],
    [status]
FROM
(
    SELECT
    b.[Card Number] as [CardNumber],
    a.[Name],
    a.[Address],
    a.[City],
    a.[Post Code],
    a.[Phone No_],
    a.[Date Created],
    b.[Ending Date],
    (case
        when b.[Ending Date] >= GETDATE() then ''Active''
        else ''Not Active'' 
    end) as [status]
    FROM [Nyamnyung Store, PT$MSR Card Link Setup] b WITH(NOLOCK)
    left join [Nyamnyung Store, PT$Contact] a WITH(NOLOCK)
        on a.[No_]=b.[Link No_]
    where convert(date,a.[Date Created]) >= '''+ convert(varchar(10),@StartJoin,111)+'''
    and convert (date,a.[Date Created]) <='''+convert(varchar(10),@EndJoin,111)+'''
    '+@filter+'
) X
'
--print(@Query)
Exec(@Query);
END

GO