Last post Jun 25, 2015 08:21 AM by peterthegreat
Jun 16, 2015 12:19 PM|peterthegreat|LINK
How do you include a Where clause in Dynamic string that reads in filter variables?
I keep obtain error saying Invalid column name: NAME ?
ALTER PROCEDURE dbo.GetEmployeesSubsetSorted
@startRowIndex int =null,
@maximumRows int =null
-- Issue query
DECLARE @sql nvarchar(4000)
DECLARE @NAME nvarchar(100) = 'FD,FD2,FD3,FD4'
SET @sql = 'SELECT [ProductID],[CategoryID],[Category]
(SELECT [ProductID], [CategoryID],[Category]
,[NAME], ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM Products WHERE NAME IN (' + @NAME +') )as EmpInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'
Jun 16, 2015 12:56 PM|PatriceSc|LINK
You are calling the same stored procedure from within itself as you are using EXEC GetEmployeesSubsetSorted @sql
More likely you wanted to type EXEC sp_executesql @Sql ! ;-)
Jun 17, 2015 07:12 AM|peterthegreat|LINK
I have amended my question with that in consideration!
The problem I experience is now with the IN(LIST) operator.
It will work with the = operator ,though not the IN type ?
Jun 17, 2015 07:32 AM|PatriceSc|LINK
Correct. What you are doing is:
WHERE NAME IN ('FD,FD2,FD3,FD4') rather than WHERE NAME IN ('FD','FD2','FD3','FD4') that is the @NAME parameter allows to replace a SINGLE literal value (not a list of values).
Possible options are to create a "splitting" function and you could use WHERE NAME IN (SELECT Value FROM dbo.Split(@NAME)) for example.
If SQL Server 2008 or later see http://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net that
is you can pass a table parameter and then you'll be able to use WHERE NAME IN (SELECT Value FROM @Name)
Jun 17, 2015 12:23 PM|peterthegreat|LINK
The table parameter seems complex so I shall look into the split method instead!
Otherwise, do you know an alternative procedure design approach that can accomplish the same task for sorting and paging through Listview using Objectdatasource
that include filtering?
Jun 25, 2015 08:21 AM|peterthegreat|LINK
Can you tell me why the following statement returns unexpected results ? I am calling another procedure using the same parameters and query as in current procedure yet it throws exception message regards the parameter values. If I keep the problematic Query
in current procedure it does not throw exception ?
dbo.GetEmployeesSubsetSorted @sortExpression, @startRowIndex, @maximumRows