Hi,
I wrote a query statement in SQL server to take paramaters, and generate then run a sql statement based on which criteria were provided. The tables got pretty massive so I need the query only filter when the value to filter on was provided.
When I tried to bring the code up in a datagrid, by populating the datasource with SQL, it didn't bring up any results (which it does have results when you run the query from SQL). Likewise, running it through a stored procedure doesn't give me any results.
If anyone can tell me how to populate a datagrid off this code, I'd appreciate it.
DECLARE @mintotal decimal(18,2) = -999999999999.99
DECLARE @maxtotal decimal(18,2) = 999999999999.99
declare @ctype varchar(40) = 0
declare @jacket varchar(50)
declare @lbox varchar(10) = 844128
declare @queue varchar(10) = 0
declare @status varchar(10) = 3
declare @ins varchar(4) = 'ALL '
declare @mindepdate varchar(50) = '01/01/0001'
declare @maxdepdate varchar(50) = '12/30/9999'
declare @where1 varchar(max) = 'where lbox = '+@lbox
declare @filter1 varchar(max) = ''
declare @filter2 varchar(max) = ''
declare @filter3 varchar(max) = ''
declare @filter4 varchar(max) = ''
declare @filter5 varchar(max) = ''
declare @filter6 varchar(max) = ''
declare @filter7 varchar(max) = ''
declare @where2 varchar(max) = ''
IF @mintotal <> -999999999999.99 OR @maxtotal <> 999999999999.99
BEGIN
SET @filter1 = ' and BRControl.total Between '+ @mintotal+ ' and '+@maxtotal
END
IF @ctype <> 0
BEGIN
SET @filter2 = ' and BRControl.controlTypeID = ' + @ctype
END
IF @jacket IS NOT NULL
BEGIN
SET @filter3 = ' and BRControl.controlJacket like ' + @jacket
END
IF @queue <> 0
BEGIN
SET @filter4 = ' and BRControl.controlID in (select controlID from BRTransCP where queueID = ' + @queue + ')'
END
IF @status = 1
BEGIN
SET @filter5 = ' and dbo.BRControl.total - ISNULL(ALPHA.amount, 0) > 0 '
END
IF @status = 2
BEGIN
SET @filter5 = ' and dbo.BRControl.total - ISNULL(ALPHA.amount, 0) = 0 '
END
IF @status = 4
BEGIN
SET @filter5 = ' and ISNULL(ALPHA.amount, 0) = 0 '
END
IF @status = 5
BEGIN
SET @filter5 = ' and dbo.BRControl.total - ISNULL(BRAVO.amount, 0) <> 0 and
ISNULL(ALPHA.amount, 0) <> 0'
END
IF @status = 6
BEGIN
SET @filter5 = ' and dbo.BRControl.total - ISNULL(BRAVO.amount, 0) < 0 '
END
IF @status = 7
BEGIN
SET @filter5 = ' and dbo.BRControl.total - ISNULL(ALPHA.amount, 0) < 0 '
END
IF @ins <> 'ALL '
BEGIN
SET @filter6 = ' and LEFT(dbo.BRControl.comments,4) LIKE ''' + @ins + ''''
END
IF @mindepdate <> '01/01/0001' OR @maxdepdate <> '12/30/9999'
BEGIN
SET @filter7 = ' and dbo.BRControl.depositDate BETWEEN ''' + CAST(@mindepdate as varchar)
+ '''' + ' and ' + '''' + CAST(@maxdepdate as varchar) + ''''
END
IF @filter1 <> '' OR @filter2 <> '' OR @filter3 <> '' OR @filter4 <> '' OR @filter5 <> ''
OR @filter6 <> '' OR @filter7 <> ''
BEGIN
SET @where2 = @where1 + @filter1 + @filter2 + @filter3 + @filter4 + @filter5 +@filter6
+@filter7
END
declare @sql varchar(max) =
'
SELECT
dbo.BRControl.controlID as ControlID,
dbo.BRControl.controlJacket AS Jacket,
dbo.BRLUControlType.controlTypeName AS Type,
dbo.BRControl.depositDate AS [Deposit Date],
dbo.BRControl.total as Total,
ISNULL(ALPHA.amount, 0) AS Posted,
ISNULL(BRAVO.amount, 0) AS Allocated,
dbo.BRControl.total - ISNULL(ALPHA.amount, 0) AS Unposted,
dbo.BRControl.total - ISNULL(BRAVO.amount, 0) AS Unallocated,
dbo.BRControl.eftNo as [EFT No],
dbo.BRControl.reconciled as Reconciled,
dbo.BRControl.printed as Printed,
dbo.BRControl.lbox as Lbox,
dbo.BRControl.comments as Comments
FROM
dbo.BRControl
LEFT JOIN
dbo.BRLUControlType ON dbo.BRControl.controlTypeID = dbo.BRLUControlType.controlTypeID
LEFT JOIN
(SELECT controlID, SUM(ISNULL(amount, 0)) AS amount
FROM dbo.BRTransCP WHERE (batchID IS NOT NULL)
GROUP BY controlID)ALPHA
ON dbo.BRControl.controlID = ALPHA.controlID
LEFT JOIN
(SELECT controlID, SUM(ISNULL(amount, 0)) AS amount
FROM dbo.BRTransCP
GROUP BY controlID)BRAVO
ON dbo.BRControl.controlID = BRAVO.controlID
' + @where2 +
'
GROUP BY
dbo.BRControl.controlID, dbo.BRControl.controlJacket, ALPHA.amount, BRAVO.amount,
dbo.BRLUControlType.controlTypeName, dbo.BRControl.depositDate,
dbo.BRControl.total, dbo.BRControl.eftNo, dbo.BRControl.reconciled,
dbo.BRControl.printed, dbo.BRControl.lbox, dbo.BRControl.comments
ORDER BY REPLICATE(0,25-LEN(BRControl.controlJacket))+BRControl.controlJacket,
depositDate
'
EXEC(@sql)