Ορίστε σε μορφή Stored Procedure αυτό που σας έλεγα νωρίτερα και βασίζεται στην τεχνική του Σωτήρη:
/****** Object: Stored Procedure dbo.pr_InvoicesView_SelectAllWLogicAndFilters Script Date: 15/6/2005 1:41:47 ìì ******/
CREATE PROCEDURE [dbo].[pr_InvoicesView_SelectAllWLogicAndFilters]
@SerialNo varchar(50),
@opSerialNo varchar(11),
@DateFrom datetime,
@opDateFrom varchar(11),
@DateTo datetime,
@opDateTo varchar(11),
@Amount decimal(12, 2),
@opAmount varchar(11),
@PayedUp bit,
@opPayedUp varchar(11),
@iErrorCode int OUTPUT
AS
SELECT [InvoiceID],
[CustomerID],
[SerialNo],
[DocumentTypeID],
[InvoiceTypeDescription],
[IssueDate],
[InterestEffectiveDate],
[DueDays],
[GracePeriod],
[Amount],
[PayedUp],
[InterestRate],
[PaymentID],
[PaymentSerialNo]
FROM [dbo].[InvoicesView]
WHERE CASE @opSerialNo
WHEN 'all' THEN 1
WHEN 'equal' THEN CASE WHEN [SerialNo] LIKE @SerialNo THEN 1
ELSE 0
END
WHEN 'beginswith' THEN CASE WHEN [SerialNo] LIKE @SerialNo + '%' THEN 1
ELSE 0
END
WHEN 'endswith' THEN CASE WHEN [SerialNo] LIKE '%' + @SerialNo THEN 1
ELSE 0
END
WHEN 'contains' THEN CASE WHEN [SerialNo] LIKE '%' + @SerialNo + '%' THEN 1
ELSE 0
END
WHEN 'notequal' THEN CASE WHEN [SerialNo] NOT LIKE @SerialNo THEN 1
ELSE 0
END
END = 1
AND CASE @opDateFrom
WHEN 'all' THEN 1
WHEN 'equal' THEN CASE WHEN [IssueDate] = @DateFrom THEN 1
ELSE 0
END
WHEN 'after' THEN CASE WHEN [IssueDate] > @DateFrom THEN 1
ELSE 0
END
WHEN 'before' THEN CASE WHEN [IssueDate] < @DateFrom THEN 1
ELSE 0
END
WHEN 'notequal' THEN CASE WHEN [IssueDate] <> @DateFrom THEN 1
ELSE 0
END
END = 1
AND CASE @opDateTo
WHEN 'all' THEN 1
WHEN 'equal' THEN CASE WHEN [IssueDate] = @DateTo THEN 1
ELSE 0
END
WHEN 'after' THEN CASE WHEN [IssueDate] > @DateTo THEN 1
ELSE 0
END
WHEN 'before' THEN CASE WHEN [IssueDate] < @DateTo THEN 1
ELSE 0
END
WHEN 'notequal' THEN CASE WHEN [IssueDate] <> @DateTo THEN 1
ELSE 0
END
END = 1
AND CASE @opAmount
WHEN 'all' THEN 1
WHEN 'equal' THEN CASE WHEN [Amount] = @Amount THEN 1
ELSE 0
END
WHEN 'greaterthan' THEN CASE WHEN [Amount] > @Amount THEN 1
ELSE 0
END
WHEN 'lowerthan' THEN CASE WHEN [Amount] < @Amount THEN 1
ELSE 0
END
WHEN 'notequal' THEN CASE WHEN [Amount] <> @Amount THEN 1
ELSE 0
END
END = 1
AND CASE @opPayedUp
WHEN 'all' THEN 1
WHEN 'true' THEN CASE WHEN [PayedUp] = 1 THEN 1
ELSE 0
END
WHEN 'false' THEN CASE WHEN [PayedUp] = 0 THEN 1
ELSE 0
END
END = 1
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode = @@ERROR
Vir prudens non contra ventum mingit