Populating a datagrid with dynamic SQL

[dquiles]

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)

[sknake]

You should build that query in code for a few reasons --
1) It is impossible for the sql server to cache an execution plan for a query using exec()
2) it is impossible to debug.

It is hard to tell you why that query isn't returning any results without sample data to work with. I would suggest you take another approach to building the query in code and see if you can get that to work.

Maybe you should also check for nulls. I ran this and it the query did not error, or return results:

declare @v varchar(10)
set @v = null
exec(@v)

Are you at least getting the columns in your grid? If you're not getting the columns then you may be looking at a null parameter

[Ramesh S]

You have ro check the value of the variable @sql and execute the SELECT query stored in it.

Pass necessary parameters to your stored procedure and print the @SQL variable from your query.

Put a print statement before EXEC as below

PRINT @SQL

This will print the content of @SQL in the output window in SQL Management Studio.

Copy that query, past it in a new Query Window and execute it.

Check whether it returns any result.

[dquiles]

No, it didn't even get blank columns back. I tried running it without using any page variables, only preset variables, but it still couldn't bring in anything. I'll have to find out if it's even possible to use the EXEC() command in an ADO SQL command object. I was hoping it was, otherwise I'll have to build it the sql query using the C# stringbuilder instead.

declare @lbox varchar(50) = 844128
declare @where varchar(max) = ''

if @lbox IS NOT NULL
BEGIN 
SET @where = ' WHERE lbox = ' +@lbox
END

declare @sql varchar(max) = 
'select controlID, controlJacket, total from BRControl' + @where

EXEC(@sql)

[dquiles]

Hi,

Thanks all. The problem ended up being the EXEC method. Admittedly, using dynamic SQL is not the most efficient method, the only reason I ended up going with this route is i needed a query that I could add filters to that would not impact performance heavily. By stringing together the sql statement first, there was minimum impact for added filters, although total performance is less then a well written statement for the same. I ended up constructing the stored procedure this way:

CREATE PROCEDURE [dbo].[JacketGrid]                     
                                    
                 @mintotal decimal(18,2) = -999999999999.99,
                 @maxtotal decimal(18,2) = 999999999999.99,
                 @ctype varchar(40) = 0,
                 @jacket varchar(50) = 0,
                 @lbox varchar(10) = 'choose lockbox',
                 @queue varchar(10) = 0,
                 @status varchar(10) = 3,
                 @ins varchar(4) = 'ALL ',
                 @mindepdate varchar(50) = '01/01/0001',
                 @maxdepdate varchar(50) = '12/30/9999'        
                AS                    
                                                                   
DECLARE @sql        nvarchar(4000),                                
        @paramlist  nvarchar(4000)                                 
                                                                   
SELECT @sql =                                                      
    '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
WHERE BRControl.lbox = @xlbox'  

IF @mintotal <> -999999999999.99 OR @maxtotal <> 999999999999.99
	SELECT @sql = @sql + ' AND BRControl.total BETWEEN @xmintotal AND @xmaxtotal'                                               
                                                                   
IF @ctype > 0                                            
   SELECT @sql = @sql + ' AND BRControl.controlTypeID = @xctype' 
   
IF @jacket <> 0
	SELECT @sql = @sql + ' and BRControl.controlJacket like @xjacket'
	
IF @queue <> 0
	SELECT @sql = @sql + ' and BRControl.controlID in (select controlID from BRTransCP where queueID = @xqueue)' 
	
IF @status = 1
	SELECT @sql = @sql + ' and dbo.BRControl.total - ISNULL(ALPHA.amount, 0) > 0 '
  
IF @status = 2
	SELECT @sql = @sql + ' and dbo.BRControl.total - ISNULL(ALPHA.amount, 0) = 0 '
	
IF @status = 4
	SELECT @sql = @sql + ' and ISNULL(ALPHA.amount, 0) = 0 '

IF @status = 5
	SELECT @sql = @sql + ' and dbo.BRControl.total - ISNULL(BRAVO.amount, 0) <> 0 and
 ISNULL(ALPHA.amount, 0) <> 0'

IF @status = 6
	SELECT @sql = @sql + ' and dbo.BRControl.total - ISNULL(BRAVO.amount, 0) < 0 '

IF @status = 7
	SELECT @sql = @sql + ' and dbo.BRControl.total - ISNULL(ALPHA.amount, 0) < 0 '
	
IF @ins <> 'ALL '
	SELECT @sql = @sql + ' and LEFT(dbo.BRControl.comments,4) LIKE @xins'
	
IF @mindepdate <> '01/01/0001' OR @maxdepdate <> '12/30/9999'
	SELECT @sql = @sql + ' and dbo.BRControl.depositDate BETWEEN CAST(@xmindepdate as varchar) and CAST(@xmaxdepdate as varchar)'

SELECT @sql = @sql + 
'
 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 
'                         
                                                                   
                                                   
                                                                   
SELECT @paramlist = '@xmintotal decimal(18,2),
					 @xmaxtotal decimal(18,2),
					 @xctype varchar(40),
					 @xjacket varchar(50),
					 @xlbox varchar(10),
					 @xqueue varchar(10),
					 @xstatus varchar(10),
					 @xins varchar(4),
					 @xmindepdate varchar(50),
					 @xmaxdepdate varchar(50)'                     
                                                                   
EXEC sp_executesql @sql, @paramlist, @mintotal, @maxtotal, @ctype,
				@jacket, @lbox, @queue, @status, @ins, @mindepdate, @maxdepdate                               
                                     

GO