This broker allows you to create parameterized SmartObjects from SQL queries. It is a bit like creating a stored procedure in SQL server, then creating a smartobject from that. Where no parameters are specified then it is more like creating smartobjects from a view.

It can also be used for updating or inserting new rows.

So why would you want to use this rather than creating views or stored procedures and making smartobjects from that? Well, one answer is that when the project is packaged using P&D then the query is packaged along with it, so when deploying you don?t need to have a series of sql scripts which you have to remember to deploy first before deploying the solution.

Another reason is that it?s quicker and simpler. Here to create a parameterized SELECT from joined tables you just need to add a query such as:

SELECT * FROM Test_AllTypes, SwitchControl where Test_AllTypes.ID = SwitchControl.ID AND IntField > @ipval

This example would create a SmartObject with all the return parameters from the 2 tables and an input parameter of the correct type (in this case same ttype as IntField). When executed would pass in the parameter passed in the smo to the query and return either a single result (Get First) or a list (Get List).

For a stored procedure you would need to declare the input parameter and add the surrounding syntax with correct types etc. This lets Sql Server determine the appriate type from the context of the query and create an appropriate SMO to have the correct parameter types and fields.

Another use case is where you want to create a simple dropdown of unique values from a table, let's say you are selecting items of clothing and you want to filter by colour and type (shorts, trousers, jersey etc). First populate the type dropdown with all types used in the table. Just making a normal view on that table and using the type in a dropdown would create multiple entries; K2 has no way to tell the dropdown that you only want distinct entries. So instead of having to create a view and then generating smartobjects from it you can use this broker to quickly create the list you're looking for:

SELECT DISTINCT chothestypes FROM clothesstock

Once you have selected the type, you then want to populate a second dropdown of colours available for that particular item, but again only one of each colour used, so you create the object:

SELECT DISTINCT colour FROM clothesstock WHERE chothestypes = @ipchothestypes

passing in the type selected from the first dropdown as the input parameter to this one.

When you create a new stored procedure or view you need to refresh the SQL Server Service instance before you can select the item to create a smartobject for it, which can take quite a long time when there are a lot of tables.

Sometimes you may want to force a particular input to be a particular type, when SqlServer may not be able to infer exactly what you need it to be. The type assignment of undeclared variables is explained here. You can easily get round this by setting a declared variable of the particular type and assigning the undeclared variable to it as a prefixt to the query:

select * FROM CM_People_Icons WHERE (Username IS NOT NULL OR @ipusersonly = 'false');

Here the @ipuseronly would be interpreted as a string, whereas in a filter I want to assign it to a checkbox. So if I declare a 'bit' field and assign @ipuseronly to it then it becomes a YesNo parameter in the SmartObject:

declare @usersonlyval bit = @ipusersonly; select * FROM CM_People_Icons WHERE (Username IS NOT NULL OR @usersonlyval = 'false');

You may need to do this if you intend to use the same input parameter more than once in the query, eg comparing the input parameter to more than one value or column. SqlServer sp_describe_undeclared_parameters() returns an error if an undescrbied variable is used more than once in a query. Again, you declare a variable whcih is used twice or more in the query (OK to use declared variables more than once) and assign the undeclared input parameter to the declared variable.

declare @genderval varchar(20) = @ipgender; select * FROM CM_People_Icons WHERE (Gender = @genderval OR @genderval = 'All')

In this query the gender is being checked (for a list view) and a filter dropdown has All/Male/Female options. Where all is selected then we return all rows, otherwise return the type selected, so the gender is tested twice. The @genderval is a declared type so can be used twice, the @ipgender is turned into the SmartObject input parameter of type Text.