Query parameters support

[ErikEJ]

I use SQL CE Toolbox to write and test queries to be used in reporting (I use telerik reporting and Dev Express, but this applies to most .net reporting tools).

In my reports the user supply parameters that translates to SQL parameters. For example if I have a report that displays sales details by period, I will have a query like this:
SELECT * FROM Sales
WHERE SaleDate >= @DateFrom AND SaleDate <= @Dateto

I always use SQL CE Toolbox to write the query without parameters, and I add the parameters once I take the query to my reporting tool.
It will be useful to get the tool ask for parameter values when running the query, or may be having some proprietary syntax to declare parameters (similar to SQL Server declare).

comments

ErikEJ wrote Sep 12 at 5:28 PM

Great idea, that would be a very useful feature.
How about having a permanent list of parameters with name, data type, size, and value, and then let the query execution engine match any it can find in the supplied sql?

Having a proprietary syntax - not so sure...

ErikEJ wrote Sep 13 at 10:02 AM

The parameters grid would contain the following columns: Name, Data type, Size, Value

Ordered by name when first opened

For example:
@StartDate, DateTime, 8, 2015-09-13

(The default size will be based on the data type)
https://msdn.microsoft.com/en-us/library/system.data.common.dbparameter(v=vs.100).aspx
(Similar to the Table designer)

The available DbType values would be based on the possible SQL Compact data types only.
https://msdn.microsoft.com/en-us/library/system.data.dbtype(v=vs.100).aspx

The list will be permanently stored in the same store as the connection is used for.

The parameter names and values used with be listed in the result message

wrote Sep 13 at 10:03 AM

HishamKedjour wrote Sep 13 at 11:21 AM

Just a thought,

I think we can easily parse the type from the query. Usually parameters are used in WHERE clause with some operators (= <, BETWEEN…). The other operand is usually a column.

In the first implementation, we can get the other operand (a column name). From there we can check the database for data type and size. There is no need at first to get the exact table. We just use the first match.

ErikEJ wrote Sep 13 at 4:43 PM

Thanks for the design feedback - I think " easily parse the type from the Query" will be a pit of failure. Also "guessing" parameter sizes it never a good idea.
How about adding a third pane (optional) above the Query text and the results, which contains the defined parameters with values?

HishamKedjour wrote Sep 13 at 6:06 PM

Let me summaries how I saw things. There is two scenarios:

1 – Using a parameters pane:

As you said, adding a third pane that contains the defined parameters as a small grid. The user will fill the parameters information (name, type, size, and value).
The tool can also detect parameters, and fill the grid as the user is typing. In this case, the user will supply the type, the size, and the value.

As a step further, the tool can guess the parameter type and size, in this case the user will only supply the value. If the tool guessed wrong, the user will correct it.
What I put as a first implementation, is a quick algorithm for guessing. It will work perfectly for users who have a consistent naming convention in their database.

Saving the query: Actually, the query is saved in plain text. If we add the parameters, we need an easier way to store them with the query. I didn’t understand what you meant by “stored in the same store as the connection is used for”. In the worst scenario, they can be stored in a file with a same name and a different extension as the query.

2 – Using some syntax (I’m avoiding the word proprietary)

In this case we don’t need a new pane. We need only a syntax that can be used to define parameters. This syntax can even be put as comment. For example:
/_Parameters:
@StartDate, DateTime, 4, '01/13/2015'
@ToTal, int, 4, 23
*/
In this example the tool will just parse the comment that start with /_Parameters: and extract a CSV list of parameters and theirs values.

Saving the query: In this scenario saving the query need no additional effort.

I hope that you get my ideas this time.

ErikEJ wrote Sep 13 at 7:49 PM

Thanks again. I really like your parameters a comments idea, it will even be possible to add a button to insert a parameters header, so the feature is discoverable.
I think the order should be: Name, datatype, value, optional size

HishamKedjour wrote Sep 13 at 10:21 PM

Me too, I prefer the comments. That way I can store them with the query within my reports (I always keep one version of the query).

Thanks a lot.

ErikEJ wrote Sep 14 at 5:35 PM

How about:
DECLARE @DateFrom datetime = '2015-09-15'
DECLARE @Dateto datetime = '2015-10-15'
SELECT * FROM Sales
WHERE SaleDate >= @DateFrom AND SaleDate <= @Dateto

HishamKedjour wrote Sep 14 at 11:31 PM

That will be perfect, it’s similar to SQL Server. There is no need to learn new syntax.

HishamKedjour wrote Sep 15 at 10:29 PM

Another thought.

Since we are not prompting the user for parameters values, and we are not using prepared queries. We can just implement them as constant (kind of C++ #define preprocessor). The tool will just replace the string before execution. Something like:
SET @DateFrom = '2015-09-15'
-- #define @DateFrom '2015-09-15'
SET @Dateto = '2015-with error, the tool will not care!'
SELECT * FROM Sales
WHERE SaleDate >= @DateFrom AND SaleDate <= @Dateto
The tool will just parse the query, remove line that set parameters value and run a string.Replace().
In our example: string.Replace(@DateFrom, "'2015-10-15'")

The tool will get this query that can send to execution:
SELECT * FROM Sales
WHERE SaleDate >= '2015-09-15' AND SaleDate <= '2015-with error, the tool will not care!'

It will be easy to implement, and it will give the same result. We don’t need to know the type or the size of parameters.

[ErikEJ]

Not adding any real value...