Click here to Skip to main content
15,867,999 members
Home / Discussions / Database
   

Database

 
GeneralRe: TSQL, Order by case, and I need to order by version Pin
jkirkerx21-Mar-23 8:46
professionaljkirkerx21-Mar-23 8:46 
GeneralRe: TSQL, Order by case, and I need to order by version Pin
Ron Nicholson21-Mar-23 11:17
professionalRon Nicholson21-Mar-23 11:17 
AnswerRe: TSQL, Order by case, and I need to order by version Pin
jschell23-Mar-23 6:26
jschell23-Mar-23 6:26 
GeneralRe: TSQL, Order by case, and I need to order by version Pin
jkirkerx23-Mar-23 9:03
professionaljkirkerx23-Mar-23 9:03 
QuestionSQL where clause variability up against coding standards Pin
Brian L Hughes10-Mar-23 15:59
Brian L Hughes10-Mar-23 15:59 
AnswerRe: SQL where clause variability up against coding standards Pin
Dave Kreskowiak11-Mar-23 5:37
mveDave Kreskowiak11-Mar-23 5:37 
AnswerRe: SQL where clause variability up against coding standards Pin
Eddy Vluggen11-Mar-23 9:00
professionalEddy Vluggen11-Mar-23 9:00 
AnswerRe: SQL where clause variability up against coding standards Pin
jschell13-Mar-23 6:56
jschell13-Mar-23 6:56 
Brian L Hughes wrote:
I had an app that users could query tables. The app form contained many text boxes


That is pretty indefinite.

Especially as it says 'tables' plural.
Also it is not clear how the users build there statements. For example is it only 'and' or does it allow 'or' clauses also.

-------------------------------------------------------
There are in fact many tables and each table only has a few columns. Also there are no joins.

But 'many' actually means not all that many. So perhaps 10 tables and 3 columns each.

Then if it was me I would generate (not manually code) the variations to produce the procs. This would happen during the build not at run time. There would be a process to determine whether changes were made so that it would not require updating the procs every release. For example there would be a separate build that only runs when something is known to have changed. Such as adding a new table.

-------------------------------------------------------
But lets presume you had one table with a lot of columns.

First the UI should limit the number of clauses that can be created. That should be true regardless of any other solution. Nothing in a computer should be unlimited and in a case like this at some point one reaches diminishing returns.


Solutions.

1. Any process should have a process for exceptions. If it does not then the process itself is flawed (I spent 15 years in process control groups, principal process author and sometimes sole process controller along with 5 years in security process.) So either use the exception process or insist that one is added. Process control exceptions should of course be documented. Probably more so than any other process control step. So that would need to be done also.

2. Pass an array to the proc. The proc builds dynamic SQL and then executes it. There will be a limit to the size of the array. This of course is just a variation of your solution but moving where the code executes.

3. Create the procs dynamically. I would go ballistic on this solution if there was more than about 30 procs (per table). Even with generation at some point this becomes a maintenance problem.
AnswerRe: SQL where clause variability up against coding standards Pin
jsc4223-Mar-23 7:32
professionaljsc4223-Mar-23 7:32 
GeneralRe: SQL where clause variability up against coding standards Pin
Brian L Hughes23-Mar-23 14:52
Brian L Hughes23-Mar-23 14:52 
GeneralRe: SQL where clause variability up against coding standards Pin
jsc4227-Mar-23 23:00
professionaljsc4227-Mar-23 23:00 
GeneralRe: SQL where clause variability up against coding standards Pin
jschell28-Mar-23 6:23
jschell28-Mar-23 6:23 
QuestionCode: 1292 SQL State: 22007 --- Incorrect datetime value Pin
Siavash.BRY6-Mar-23 20:56
Siavash.BRY6-Mar-23 20:56 
AnswerRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value Pin
Richard MacCutchan6-Mar-23 21:26
mveRichard MacCutchan6-Mar-23 21:26 
GeneralRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value Pin
Siavash.BRY6-Mar-23 21:52
Siavash.BRY6-Mar-23 21:52 
GeneralRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value PinPopular
Richard MacCutchan6-Mar-23 22:09
mveRichard MacCutchan6-Mar-23 22:09 
AnswerRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value Pin
jschell8-Mar-23 7:03
jschell8-Mar-23 7:03 
AnswerRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value Pin
RedDk8-Mar-23 8:30
RedDk8-Mar-23 8:30 
QuestionEF Core 6 Question Pin
Kevin Marois1-Feb-23 16:24
professionalKevin Marois1-Feb-23 16:24 
AnswerRe: EF Core 6 Question Pin
Dave Kreskowiak2-Feb-23 2:34
mveDave Kreskowiak2-Feb-23 2:34 
GeneralRe: EF Core 6 Question Pin
Kevin Marois2-Feb-23 5:45
professionalKevin Marois2-Feb-23 5:45 
Questionexecute an update  using --> "Exec sp_executesql @Sql1" Pin
Member 115338921-Feb-23 4:55
Member 115338921-Feb-23 4:55 
AnswerRe: execute an update  using --> "Exec sp_executesql @Sql1" Pin
Victor Nijegorodov1-Feb-23 5:48
Victor Nijegorodov1-Feb-23 5:48 
AnswerRe: execute an update  using --> "Exec sp_executesql @Sql1" Pin
David Mujica2-Feb-23 4:31
David Mujica2-Feb-23 4:31 
AnswerRe: execute an update  using --> "Exec sp_executesql @Sql1" Pin
Eddy Vluggen10-Mar-23 12:17
professionalEddy Vluggen10-Mar-23 12:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.