sorry I forgot the [NF] Frank.
Frank Cazabon On 30/05/2012 07:34 AM, Frank Cazabon wrote: > Hi I have this Stored Procedure which is used to search for receipts: > > 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: > -- Create date: > -- 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 > > > > If I pass in a full PolicyNumber like 'WLIF20120047' (the other > parameters are passed as empty strings) I get no matches. > > If I pass in 'WLIF2012004%' I get matches with the policynumber > 'WLIF20120047' > > Likewise if I pass in 'WLIF20120047%'. > > What is wrong with my code? > > I have another almost identical SP which works fine. > _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
