locked
Assigning a Stored Procedure to a FilterClause RRS feed

  • Question

  • I have the need for a complex filter on my provisioning scope. Currently the filterclause contains up to three nested selected statements which is impacting the sync process greatly. Is it possible to assign a stored procedure in the filterclause similar to: 

        string spDefinition = "exec spTest @ID"; 
                    eventScopeServerTemplate.Tables["Products"].AddFilterColumn("ID");
                    eventScopeServerTemplate.Tables["Products"].FilterClause = "[side].[ID] IN (" + spDefinition + ")";
                    SqlParameter param = new SqlParameter("@ID", SqlDbType.Int);
                    eventScopeServerTemplate.Tables["Products"].FilterParameters.Add(param);

    -f

    Tuesday, May 15, 2012 11:31 AM

All replies

  • If anyone is interested? Here is the solution I came up with. Use a table defined function instead of a SP.

        string spDefinition = "Select ID FROM dbo.testFunction(@ID)";

                    eventScopeServerTemplate.Tables["Products"].AddFilterColumn("ID");
                    eventScopeServerTemplate.Tables["Products"].FilterClause = "[side].[ID] IN (" + spDefinition + ")";

                    SqlParameter param = new SqlParameter("@ID", SqlDbType.Int);
                    eventScopeServerTemplate.Tables["Products"].FilterParameters.Add(param);

    -f


    Felix Fuentes

    • Proposed as answer by JuneT Wednesday, May 16, 2012 11:28 AM
    Tuesday, May 15, 2012 1:10 PM