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.
--
Frank.
Frank Cazabon
_______________________________________________
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.
