Building a dynamic where clause in a stored procedure command

Question

I have a stored procedure where the arguments looks like this

Create Procedure [dbo].[myStoredProcedure]
    @TaskId int = 0
,   @FileName varchar(200) =''
,   @DataDtFrom     smalldatetime = '01/01/1900'
,   @DataDtTo       smalldatetime = '01/01/1900'
,   @OFFSET INT = 0
,   @FETCH INT = 2000
,   @WhereClauseString varchar(5000) = ''

SELECT 
         DataDt
        ,EffDt
        ,LoanNumber     
        ,UploadDate
        ,UploadedFileName       
  FROM dbo.myFileTable u
    WHERE  
        (@DataDtTo = '01/01/1900' or DataDt between @DataDtFrom and @DataDtTo)      
        and (@TaskId = 0 or TaskId = @TaskId)
        and (@FileName = '' or UploadedFileName like '%' + @FileName + '%')

        **Where ??? = @WhereClauseString**

        ORDER BY u.UploadDate
        OFFSET @OFFSET ROWS 
        FETCH NEXT @FETCH ROWS ONLY

I initialize this in C#

 var whereClauseString = "LoanNum in(111,222,444) and TaskId in (123,456,789)";

 using (var conn = new MyEntities().Database.Connection)
                    {
                        conn.Open();
                        var cmd = conn.CreateCommand();
                        cmd.CommandTimeout = 1800;
                        cmd.CommandText = model.UploadStoredProcedure;
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@TaskId", Convert.ToInt64(model.TaskId)));
                        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FileName", model.FileName ?? string.Empty));

                        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DataDtFrom", DateTime.Parse(model.adjFromDataDt.ToShortDateString()) <= DateTime.Parse(basicDate.ToShortDateString()) ? basicDate : model.adjFromDataDt.Date));
                        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DataDtTo", DateTime.Parse(model.adjToDataDt.ToShortDateString()) <= DateTime.Parse(basicDate.ToShortDateString()) ? basicDate : model.adjToDataDt.Date));                       
                        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@OFFSET", model.Page));
                        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FETCH", model.PageSize));  

    **Dynamic Where clause** -->> System.Data.SqlClient.SqlParameter("@WhereClause", whereClauseString));                      
                        var da = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)cmd);
                        da.Fill(ds);
                    }

My question is it possible to build a dynamic where clause and pass it to the stored procedure and sort on the columns that are being referenced in the where clause? How would I know which columns I need in the stored procedure that are being referenced in the where clause?

Would such a thing be possible with this stored Procedure and Entity Framework?

Answer 1

Anyway, if your DB is far from external attacks with SQL Injection you still can think in an easy solution based in Dinamic SQL, very often this way can solve quickly very complex problems otherwise:

CREATE Procedure [dbo].[myStoredProcedure]
    @TaskId int = 0
,   @FileName varchar(200) =''
,   @DataDtFrom     smalldatetime = '01/01/1900'
,   @DataDtTo       smalldatetime = '01/01/1900'
,   @OFFSET INT = 0
,   @FETCH INT = 2000
,   @WhereClauseString varchar(5000) = ''
AS
BEGIN
DECLARE @SQL AS NVARCHAR(MAX);
SELECT @SQL = '
SELECT 
         DataDt
        ,EffDt
        ,LoanNumber     
        ,UploadDate
        ,UploadedFileName       
  FROM dbo.myFileTable u
    WHERE  
        ('''+CONVERT(CHAR(10), @DataDtTo, 112)+''' = ''01/01/1900'' or DataDt between '''+CONVERT(CHAR(10), @DataDtFrom, 112)+''' and '''+CONVERT(CHAR(10), @DataDtTo, 112)+''')      
        and ('+CONVERT(CHAR(10), @TaskId)+' = 0 or TaskId = '+CONVERT(CHAR(10), @TaskId)+')
        and ('+RTRIM(CONVERT(CHAR(100), @FileName))+' = '''' or UploadedFileName like ''%'+RTRIM(CONVERT(CHAR(100), @FileName))+'%'')
        ' +CONVERT(CHAR(100), @WhereClauseString)+'
        ORDER BY u.UploadDate
        OFFSET '+CONVERT(CHAR(10), @OFFSET)+' ROWS 
        FETCH NEXT '+CONVERT(CHAR(10), @FETCH)+' ROWS ONLY;
        '
PRINT @SQL;
EXEC sp_ExecuteSQL @SQL;
END;