i have a query...
<pre>ALTER PROCEDURE [dbo].[SpWeighTicketReport]
@WeighTicketCode VARCHAR(50) = '',
@StartDate AS SMALLDATETIME = NULL,
@EndDate AS SMALLDATETIME = NULL,
@StrSearch AS VARCHAR(2000) = NULL
AS
SET NOCOUNT ON
DECLARE @StrQuery VARCHAR(8000) = ''
BEGIN
SET @StrQuery = 'SELECT
(SELECT CompanyName FROM CompanyMaster) AS CompanyName, WeighTicketCode,
VehicleCode, CustomerName, ProductCode, ProductName, TransporterName, ContractNo, DONo,
PL3No, TransactionNote, DriverName, ModifyBy AS WeighBy, WeighDate1, WeighDate2,
Weigh1, R1, Weigh2, R2, FFA, Mouisture, Dirt, Broken,
(CASE WHEN R1 > Weigh1 THEN R1 - Weigh1 ELSE Weigh1 - R1 END) AS V1,
(CASE WHEN R2 > Weigh2 THEN R2 - Weigh2 ELSE Weigh2 - R2 END) AS V2,
(CASE WHEN Weigh1 > Weigh2 THEN Weigh1 - Weigh2 ELSE Weigh2 - Weigh1 END) AS Netto,
(CASE WHEN R1 > R2 THEN R1 - R2 ELSE R2 - R1 END) AS RNetto,
(CASE WHEN R1 > R2 THEN R1 - R2 ELSE R2 - R1 END)
-
(CASE WHEN Weigh1 > Weigh2 THEN Weigh1 - Weigh2 ELSE Weigh2 - Weigh1 END) AS VNetto,
AdjustWeigh, WeighNet, UOM1, Ext, Potongan,
' + QUOTENAME(CONVERT(VARCHAR, ISNULL(@StartDate, GETDATE()), 106) + ' s.d. ' + CONVERT(VARCHAR, ISNULL(@EndDate, GETDATE()), 106), '''') + ' AS Periode
FROM
VWeighTicket '
IF LEN(@WeighTicketCode) > 0
BEGIN
SET @StrQuery = @StrQuery + ' WHERE WeighTicketCode = ' + QUOTENAME(@WeighTicketCode, '''')
END
ELSE IF LEN(@StrSearch) > 0
BEGIN
SET @StrQuery = @StrQuery + ' ' + @StrSearch + ' AND CONVERT(VARCHAR, WeighDate1, 112) BETWEEN CONVERT(VARCHAR, CAST(' + QUOTENAME(@StartDate, '''') + ' AS DATE), 112) AND CONVERT(VARCHAR, CAST(' + QUOTENAME(@EndDate, '''') + ' AS DATE), 112) '
END
ELSE
BEGIN
SET @StrQuery = @StrQuery + ' WHERE CONVERT(VARCHAR, WeighDate1, 112) BETWEEN CONVERT(VARCHAR, CAST(' + QUOTENAME(@StartDate, '''') + ' AS DATE), 112) AND CONVERT(VARCHAR, CAST(' + QUOTENAME(@EndDate, '''') + ' AS DATE), 112) '
END
SET @StrQuery = @StrQuery + ' ORDER BY ID ASC'
END
EXEC (@StrQuery)
and i get error...
Subquery returned more than 1 value...
please advise... :(
What I have tried:
i change '=' to 'IN'
but there is still the same errors...