USE [SIAS] >GO >/****** Object: StoredProcedure [dbo].[ReceiptsSelectByDynamic] Script Date: 05/29/2012 16:15:50 ******/ >SET ANSI_NULLS OFF >GO >SET QUOTED_IDENTIFIER ON >GO >-- ============================================= >-- Author: <Author,,Name> >-- Create date: <Create Date,,> >-- Description: <Description,,> >-- ============================================= >ALTER PROCEDURE [dbo].[ReceiptsSelectByDynamic] > @ReceiptNumber varchar(10), > @ClientName varchar(50), > @PolicyNumber varchar(20) >AS > SET NOCOUNT ON; >BEGIN > > DECLARE @sSql nvarchar(4000) > DECLARE @sWhereClause nvarchar(4000) > DECLARE @sJoinClause nvarchar(4000) > DECLARE @ParmDefinition nvarchar(4000) > DECLARE @NewLine nvarchar(4000) > > SET @NewLine = CHAR(13) + CHAR(10) > > SET @sWhereClause = 'WHERE 1 = 1' -- Initialise > > SET @sJoinClause = '' -- Initialise > > SET @sSql = 'SELECT [dbo].[Receipts].[PolicyHeaderFK], > [ReceiptAmount], > [ReceiptDate], > [ReceiptNumber], > [ReceiptPK], > [ClientFK], > [PlanFK], > [PolicyNumber], > [PolicyPK], > [Code], > [Description], > [ClientNumber], > [CompanyName], > [FirstName], > [MiddleName], > [LastName] > FROM [dbo].[Receipts] > INNER JOIN [dbo].[Policyheaders] ON [dbo].[Receipts].[PolicyHeaderFK] = [PolicyHeaderPK] > INNER JOIN [dbo].[Policies] ON [PolicyHeaderPK] = [dbo].[Policies].[PolicyHeaderFK] > INNER JOIN (SELECT [PolicyHeaderFK], MAX(EffectiveDate) as EffectiveDate > FROM [dbo].[Policies] > GROUP BY [PolicyHeaderFK]) subquery ON [Policies].[PolicyHeaderFk] = subquery.policyHeaderFK AND Policies.EffectiveDate = subquery.EffectiveDate > inner join [dbo].[Plans] on [PlanFK] = [PlanPK] > inner join [dbo].[Clients] on [ClientFK] = [ClientPK] ' + @NewLine > > > IF @PolicyNumber != '' > BEGIN > SET @sWhereClause = @sWhereClause + @NewLine + ' AND ([PolicyNumber] LIKE @PolicyNumber)' > END > > IF @ClientName != '' > SET @sWhereClause = @sWhereClause + @NewLine + ' AND ([LastName] LIKE @ClientName OR [CompanyName] LIKE @ClientName)' > > IF @ReceiptNumber != '' > SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ReceiptNumber] LIKE @ReceiptNumber' > > SET @ParmDefinition = '@PolicyNumber nvarchar(20),' + @NewLine > + ' @ClientName nvarchar(50),' + @NewLine > + ' @ReceiptNumber nvarchar(20)' > > SET @sSql = @sSql + @sJoinClause + @sWhereClause > > EXEC sp_executesql @sSql, @ParmDefinition, > @PolicyNumber=@PolicyNumber, > @ClientName=@ClientName, > @ReceiptNumber=@ReceiptNumber > >END >>