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 >>>>>>
>>DECLARE @Test TABLE (Fld1 CHAR(50)) >>INSERT INTO @Test VALUES ('WLIF20120047') >>DECLARE @LikePerc nvarchar(50) >>DECLARE @LikeWoPerc nvarchar(50) >>SET @LikePerc = 'WLIF20120047%' >>SET @LikeWoPerc = 'WLIF20120047' >>SELECT * FROM @Test WHERE Fld1 LIKE @LikePerc >>SELECT * FROM @Test WHERE Fld1 LIKE @LikeWoPerc >>>>
>>DECLARE @Test TABLE (Fld1 CHAR(50)) >>INSERT INTO @Test VALUES ('WLIF20120047') >>SELECT * FROM @Test WHERE Fld1 LIKE 'WLIF20120047%' >>SELECT * FROM @Test WHERE Fld1 LIKE 'WLIF20120047' >>>