I have a stored procedure into which I want to pass variables and depending on the value of the variable passed into I want to be able to change the Where clause.
I keep getting an error:
[red]Incorrect syntax near the keyword 'WHERE'[/red]
Any help would be much appreciated
Thanks very much
David
I keep getting an error:
[red]Incorrect syntax near the keyword 'WHERE'[/red]
Any help would be much appreciated
Code:
@InvoiceStatus AS Int,
@CAADiv AS varChar(15) = NULL,
@RegNo AS varChar(8) = NULL,
@PolicyHolder AS varChar(150) = NULL,
@Repairer AS varChar(150) = NULL
AS
SET NOCOUNT ON;
BEGIN
DECLARE @Where AS varChar(500);
SET @Where = 'R.dCompleted IS NOT NULL'
--AND (@CAADiv = '0' OR RN.FormRef LIKE @CAADiv + '%')
IF @CAADiv = '1'
SET @Where = @Where + 'AND RN.FormRef LIKE CAA' + '%'
ELSE
IF @CAADiv = '2'
SET @Where = @Where + 'AND RN.FormRef LIKE ENS' + '%' + ' OR RN.FormRef LIKE ETP'+ '%'
ELSE
IF @CAADiv = '3'
SET @Where = @Where + 'AND RN.FormRef LIKE MIT' + '%'
ELSE
IF @CAADiv = '4'
SET @Where = @Where + 'AND RN.FormRef LIKE EVR' + '%'
ELSE
IF @CAADiv = '5'
SET @Where = @Where + 'AND RN.FormRef LIKE FLT' + '%'
ELSE
IF @CAADiv = '6'
SET @Where = @Where + 'AND RN.FormRef LIKE MCM' + '%'
ELSE
IF @CAADiv = '7'
SET @Where = @Where + 'AND RN.FormRef LIKE SUM' + '%'
ELSE
IF @CAADiv = '8'
SET @Where = @Where + 'AND RN.FormRef LIKE ASL' + '%'
ELSE
IF @CAADiv = '9'
SET @Where = @Where + 'AND RN.FormRef LIKE APD' + '%'
ELSE
SET @Where = @Where + 'AND RN.FormRef = 0'
--All Option selected
IF @InvoiceStatus = '0'
SET @Where = @Where + 'AND rac.FullyAllocated = 0 OR rac.FullyAllocated = 1 OR rac.FullyAllocated IS NULL '
--SET @InvoiceStatus = ''
ELSE
--History Option selected
IF @InvoiceStatus = '2'
SET @Where = @Where + 'AND rac.FullyAllocated = 1 '
--SET @InvoiceStatus = '1'
ELSE
--Open Option selected
SET @Where = @Where + 'AND rac.FullyAllocated = 0 OR rac.FullyAllocated IS NULL '
SELECT R.ID AS RepairID,RN.FormRef AS Reference, V.RegNo,Cust.Name AS PolicyHolder,
Repairer.Name AS Repairer, R.CurrentStatus,
(Sum(RAC.TotalValue) - Isnull(Sum(RAC2RAI.Allocated) ,0)) AS TotalInvAwaiting,
Isnull(Sum(RAC2RAI.Allocated) ,0) as TotalInvReceived,
min(CASE WHEN (rac.FullyAllocated IS NULL OR rac.FullyAllocated = 0) THEN Rac.dDue ELSE NULL END) AS dDue
FROM RepairAssociatedCost RAC INNER JOIN Repair R ON R.ID = RAC.RepairID
INNER JOIN RepairNotification RN ON RN.ID = R.RepairNotificationID
INNER JOIN Vehicle V ON V.ID = R.VehicleID
LEFT JOIN Notification N ON N.ID = R.NotificationID
LEFT JOIN Company Cust ON (Cust.ID = N.CustomerID OR Cust.ID = RN.CustomerID)
INNER JOIN Company Repairer On Repairer.ID = R.RepairerCompanyID
LEFT JOIN (Select RACostID, Sum(Amount) AS Allocated
FROM RAC2RAI GROUP BY RACostID) RAC2RAI ON RAC2RAI.RACostID = RAC.ID
SET @Where = @Where + 'AND (@RegNo = '' OR RegNo = @RegNo) '
SET @Where = @Where + 'AND (@PolicyHolder = 0 OR Cust.Name = @PolicyHolder) '
SET @Where = @Where + 'AND (@Repairer = 0 OR Repairer.Name = @Repairer)'
[highlight]WHERE @Where = @Where[/highlight]
GROUP BY R.ID , RN.FormRef, V.RegNo,Cust.Name,Repairer.Name,R.CurrentStatus
ORDER BY RN.FormRef
ENDThanks very much
David