Help With a dynamic Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help With a dynamic Query

Author

Topic

MangoSkin
Starting Member

22 Posts

Posted - 2011-01-17 : 12:28:20

I am trying to create a query that can do sorting on a column based on a parameter.
This is the query that I have
Declare @sortExpression nvarchar(100);
Declare @vendorName varchar(50)=null;
Declare @startRowIndex int=1;
Declare @maximumRows int=5;
SET @sortExpression = 'InvoiceDate DESC';
DECLARE @sql nvarchar(max);
Set @sql='
Select DocumentID,
DocumentTitle,
VendorId,
VendorName,
DateScanned FROM
(Select distinct v.DocumentID ,
v.DocumentTitle ,
v.VendorId ,
v.VendorName ,
v.DateScanned ,
ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowRank

from
DMViewUnverifiedInvoices v left outer join DMAP_ACC_OTHER_INVOICES_DIST i on
v.invoicenumber = i.invoice_num
left outer join lkpProject p on i.project_number=p.projectid
left outer join tblunit u on u.accountingunitnumber=i.unit_number
where
(v.vendorname like %'+@vendorName+'% or '+@vendorName+' is null)
) as ResultWithRowNumber
WHERE RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ');'

EXEC sp_executesql @sql

The problem is in this filter
where
(v.vendorname like %'+@vendorName+'% or '+@vendorName+' is null)

If i remove that part I will get some results. But if the filter is applied query runs successfully but no results. But there should be some results.

Can someone please figure out the mistake I am making.

Thank You very much.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-01-17 : 12:43:42

At first glance I would say


or '+@vendorName+' is null

is the issue.

You want to evaluate against the field value being null.


or v.VendorName is null

You'll need to code that into your query. Use a CASE to see if the variable is null and iff so then add the v.VendorName is NULL to your where clause.

===
http://www.ElementalSQL.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-18 : 08:54:36

Print @sql and post the result

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources