Click here to Skip to main content
15,906,708 members
Home / Discussions / Database
   

Database

 
GeneralRe: TSQL, Order by case, and I need to order by version, [fixed] good enough for me, thanks Pin
jkirkerx21-Mar-23 13:17
professionaljkirkerx21-Mar-23 13:17 
GeneralRe: TSQL, Order by case, and I need to order by version, [fixed] good enough for me, thanks Pin
jsc4222-Mar-23 0:17
professionaljsc4222-Mar-23 0:17 
GeneralRe: TSQL, Order by case, and I need to order by version, [fixed] good enough for me, thanks Pin
jkirkerx22-Mar-23 6:42
professionaljkirkerx22-Mar-23 6:42 
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 
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions.

What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures.

This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum.

Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well.

I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient.

What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon.

something like this

select * from people
where if @fNameActive then fname like @fNameParam
and if @dobActive then dob = @dobParam

Otherwise name and dob should not be evaluated.

I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case.

Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well.

Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials.

So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner?

I can't be the only developer to run up against this wall. But it's so complicated that it's difficult to formulate an effective www search.

Could I have coded this? Just saw something similar on Stack Overflow.

SELECT * FROM people
WHERE
( @fNameActive = 1 AND fname like @fNameParam )
AND
( @dobActive = 1 AND dob = @dobParam)

It looks to me like the only way to get it to work is by omitting the column. I actually tried this on MySQL and it failed. If one of the () were false because of my simulated "activate test" I just coded 1=0 it returned no rows.

How about...

SELECT * FROM people
WHERE
( @nameactive = 1 AND name like @nameparam )
OR
( @dobActive = 1 AND dob = @dobParam)

Well it seemed to work but failed to narrow down to a specific row. Hypothetically if fnameparam were george and dobparam was 2010/2/30 it would return all georges and all people who were born on feb 30th, so no, it should return no rows if there are no georges born on feb 30th. Yes, by George I am joking about feb 30th.

I ask this question because I'm curious. I have no problem using parameterized concats on my home projects.
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 
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 

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.