Help me with a SQL statement

SixPapaCharlie

SixPapaCharlie

May the force be with you
Joined
Aug 8, 2013
Messages
16,414
Display Name
Display name:
Sixer
I have 3 variables passed into a stored proc.
They are all optional and integers that are defaulted to 0 in the proc so I know if they were passed in.

A, B, C

I want to select data using only the positive (passed in) variables

select * from some table

where if A > 0 then Field1 = A
AND
If B > 0 then Field2 = B
AND
If C > then Field3 = C

So if the user doesn't pass in B, it will not constrain the selected data.

I can't seem to figure out that syntax in the where clause.
I was going to build dynamic SQL but I think that is sloppy.

What say the nerds?
 
Not a nerd, that all sounded Greek to me.

Disclaimer: Oh, nothing intended about Greeks, my favorite uncle was Greek.
 
For starters, Oracle, MySQL, SQLServer or something else?
If Oracle, I'm assuming PL/SQL since you state it's a stored proc.

Assuming numeric and PL/SQL, > is correct.

procedure Whatever (A IN NUMBER, B IN NUMBER, C IN NUMBER) (
begin
if A > 0 or A IS NULL then Field1 = A else Field1 = 0;
if B > 0 or B IS NULL then Field2 = B else Field2 = 0;
if C > 0 or C IS NULL then Field3 = C else Field3 = 0;
end
);

BTW my consulting fee is quite reasonable if you'd like to hire me on a contract basis.
 
This is the horrible way to do it, some SQL optimizers do well, some, not so much.
select foobar from baz where (A IS NULL OR A = 0 OR field1= A ) AND ( B IS NULL OR B=0 OR field2 = B ) .....

basically, if A is 0 then we don't care if field1 is a, etc. Obviously you don't need the A IS NULL stuff, but I usually do that instead of defaulting values.

And obviously if you only want positive ( NOT A > 0 OR....) would also work.
 
Last edited:
Oracle's optimizer uses short circuit - if the first clause in the IF statement is true, the OR part isn't executed. On the other hand, if the value A isn't passed in, it's assumed to be NULL and testing against zero doesn't work, so the second clause is needed.

Personally, I wouldn't assume numeric, I'd set up a routine to validate numeric before doing any other processing. I never believe the data is correct.
 
Can't you multi link the table with outer joins to get the data set you are looking to process?
 
SixPapaCharlie said:
I have 3 variables passed into a stored proc.
They are all optional and integers that are defaulted to 0 in the proc so I know if they were passed in.

A, B, C

I want to select data using only the positive (passed in) variables

select * from some table

where if A > 0 then Field1 = A
AND
If B > 0 then Field2 = B
AND
If C > then Field3 = C

So if the user doesn't pass in B, it will not constrain the selected data.

I can't seem to figure out that syntax in the where clause.
I was going to build dynamic SQL but I think that is sloppy.

What say the nerds?
Click to expand...

Maybe this is a quirk of the SQL syntax..... I've not touched it in about a decade now but with those AND s in there aren't you only assigning the values if all 3 variables are > 0 ?
 
Like any other SQL question, the answer is, "it depends".

Probably the simplest way to do this from a coding standpoint would be to write three conditions in the WHERE clause with an OR in them:

CREATE PROC BryanWithaY
(@VariableA int = 0, @VariableB int = 0, @VariableC int = 0)
AS
BEGIN
SELECT Column1, Column2, Column 3
FROM Table
WHERE (Column1 = @VariableA OR @VariableA= 0)
AND (Column2 = @VariableB OR @VariableB = 0)
AND (Column3 = @VariableC OR @VariableC = 0)
END

Howevah, this is asking for performance issues. It's very possible that the OR in the conditions will cause the compiler to scan the entire table. If your table only has 1000 rows of so, that's not an issue. If it has millions, it is a problem and you'll have to take a different approach. I ran into this in one of our queries that was written by a C# developer. What I wound up doing was to recode it so that for the most popular sets of parameters, I wrote a SELECT clause for each of them, and finally for any combination of passed parameters for which there wasn't a SELECT, it did something much like the code shown.

Also, I wouldn't default an unpassed parameter to zero, I'd default it to NULL. Zero is a valid value for an integer and someone might need to use it.

CREATE PROC MattyP
(@VariableA int = NULL, @VariableB int = NULL, @VariableC int = NULL)
AS
BEGIN
SELECT Column1, Column2, Column 3
FROM Table
WHERE (Column1 = @VariableA OR @VariableA IS NULL)
AND (Column2 = @VariableB OR @VariableB IS NULL)
AND (Column3 = @VariableC OR @VariableC IS NULL)
END

When looking for a value the is null, remember that you use the word IS and not the equals sign. Although SQL has no problem parsing "IF Column1 = NULL", it will never find a match. , it has to be "IF Column1 IS NULL".

There's a way to clean up the syntax on this statement using the ISNULL function, but I really try to avoid ding procs this way and can't remember if off the top of my head.
 
I'd probably write the variables with values to an array rather define them as variables. But that's just in case you want to reference them in a different procedure. I do agree the value you want to use is "Null" and not "zero" as zero is still a positive value, thus you won't get the results you're hoping for (assuming you want any value that is greater than zero.)
 
FormerHangie said:
Like any other SQL question, the answer is, "it depends".

Probably the simplest way to do this from a coding standpoint would be to write three conditions in the WHERE clause with an OR in them:

CREATE PROC BryanWithaY
(@VariableA int = 0, @VariableB int = 0, @VariableC int = 0)
AS
BEGIN
SELECT Column1, Column2, Column 3
FROM Table
WHERE (Column1 = @VariableA OR @VariableA= 0)
AND (Column2 = @VariableB OR @VariableB = 0)
AND (Column3 = @VariableC OR @VariableC = 0)
END

Howevah, this is asking for performance issues. It's very possible that the OR in the conditions will cause the compiler to scan the entire table. If your table only has 1000 rows of so, that's not an issue. If it has millions, it is a problem and you'll have to take a different approach. I ran into this in one of our queries that was written by a C# developer. What I wound up doing was to recode it so that for the most popular sets of parameters, I wrote a SELECT clause for each of them, and finally for any combination of passed parameters for which there wasn't a SELECT, it did something much like the code shown.

Also, I wouldn't default an unpassed parameter to zero, I'd default it to NULL. Zero is a valid value for an integer and someone might need to use it.

CREATE PROC MattyP
(@VariableA int = NULL, @VariableB int = NULL, @VariableC int = NULL)
AS
BEGIN
SELECT Column1, Column2, Column 3
FROM Table
WHERE (Column1 = @VariableA OR @VariableA IS NULL)
AND (Column2 = @VariableB OR @VariableB IS NULL)
AND (Column3 = @VariableC OR @VariableC IS NULL)
END

When looking for a value the is null, remember that you use the word IS and not the equals sign. Although SQL has no problem parsing "IF Column1 = NULL", it will never find a match. , it has to be "IF Column1 IS NULL".

There's a way to clean up the syntax on this statement using the ISNULL function, but I really try to avoid ding procs this way and can't remember if off the top of my head.
Click to expand...

There is a potential flaw with this approach. If none of the three parameters are passed in, it will return all rows - probably not what is expected. If it's not a huge amount of data, or speed is not a concern, I'd probably use union all as suggested above. If micro seconds count, and there is a lot of rows, I would optimize the code to run the exact query needed based on which parameters were passed in. There are only 8 combos. That's also assuming the columns are all indexed. If they aren't, then the union all will probably work just as well.
 
Salty said:
There is a potential flaw with this approach. If none of the three parameters are passed in, it will return all rows - probably not what is expected. If it's not a huge amount of data, or speed is not a concern, I'd probably use union all as suggested above. If micro seconds count, and there is a lot of rows, I would optimize the code to run the exact query needed based on which parameters were passed in. There are only 8 combos. That's also assuming the columns are all indexed. If they aren't, then the union all will probably work just as well.
Click to expand...
Actually, after a moments reflection, the original approach of FormerHangie's that I quoted with the simple addition of "and (variableA is not null or variableB is not null or variableC is not null) would solve the problem with a single query that will optimize well by the sql engine without returning all rows if all Params are empty.

CREATE PROC MattyP
(@VariableA int = NULL, @VariableB int = NULL, @VariableC int = NULL)
AS
BEGIN
SELECT Column1, Column2, Column 3
FROM Table
WHERE (Column1 = @VariableA OR @VariableA IS NULL)
AND (Column2 = @VariableB OR @VariableB IS NULL)
AND (Column3 = @VariableC OR @VariableC IS NULL)
AND (@VariableA IS NOT NULL OR @VariableB IS NOT NULL OR @VariableC IS NOT NULL)
END

The parenthesis are important
 
Last edited:
If your optional variables are NULL and there are no NULLs in the actual columns, then there is a slightly simpler syntax:

CREATE PROC SixPC
(@VariableA int = NULL, @VariableB int = NULL, @VariableC int = NULL)
AS
BEGIN
SELECT * FROM Table
WHERE COALESCE(Column1 = @VariableA, 1)
AND COALESCE(Column2 = @VariableB, 1)
AND COALESCE(Column3 = @VariableC, 1)
END



Salty said:
If none of the three parameters are passed in, it will return all rows - probably not what is expected.
Click to expand...

Or it may be expected, or it may need to RAISERROR - depends.

Either way, I'm sure the OP can figure out the appropriate action depending on what's needed - it's not like preflighting a Cirrus...
 
Last edited:
All variables are defaulted so they won't be null.
So this is the whole thing (psuedocode again)
All those ADNS in the ware clause would be comparing variables that would have been passed in w/ positive values or be defaulted to 0 if not passed in.


ALTER PROCEDURE [bravotango].[ADVANCED_SEARCH]
@MINX INT = 0,
@MAXX INT = 0,
@MINY INT = 0,
@MAXY INT = 0,
@STYLE INT = 0,
@X INT = 0,
@Y INT = 0,
@Z INT = 0,
@ID INT = 0

AS
BEGIN

SELECT A.Value1 AS Value1_ID,
A.Value2 AS Value2_ID,
AVG(A.Value3) AS Value3,
AVG(A.Value4) AS Value4,
AVG(A.Value5) AS Value5,
AVG(A.Value6) AS Value6
AVG(A.Value7) AS Value7,
AVG(A.Value8) AS Value8

FROM Table AS A

WHERE 1=1
AND A.Value4= @X
AND A.Value1BETWEEN @MINX AND @MAXX
AND A.Value2 BETWEEN @MINY AND @MAXY
AND A.Value3= @V
AND A.Value4= @Z
AND A.Value5= @STYLE
AND A.Value6= @ID

GROUP BY A.Value1, A.Value2

END

GO
 
deonb said:
If your optional variables are NULL and there are no NULLs in the actual columns, then there is a slightly simpler syntax:

CREATE PROC SixPC
(@VariableA int = NULL, @VariableB int = NULL, @VariableC int = NULL)
AS
BEGIN
SELECT * FROM Table
WHERE COALESCE(Column1 = @VariableA, 1)
AND COALESCE(Column2 = @VariableB, 1)
AND COALESCE(Column3 = @VariableC, 1)
END





Or it may be expected, or it may need to RAISERROR - depends.

Either way, I'm sure the OP can figure out the appropriate action depending on what's needed - it's not like preflighting a Cirrus...
Click to expand...
hmm. Maybe I should default them to null and go that route.
 
chartbundle said:
This is the horrible way to do it, some SQL optimizers do well, some, not so much.
select foobar from baz where (A IS NULL OR A = 0 OR field1= A ) AND ( B IS NULL OR B=0 OR field2 = B ) .....

basically, if A is 0 then we don't care if field1 is a, etc.
Click to expand...
Agreed, from plain SQL perspective, this is a huge no-no.
But if Brain-with-a-Y is interacting with a on optimizing SQL engine, it is a valid and useful syntax (given the condition). Just don't use this condition in a non-optimized engine.
 
I think the most trouble I ever had with SQL statements was with these: :D

"Cherokee 32258, San Carlos Ground, cleared to the Half Moon Bay Airport. On departure, fly runway heading until past the diamond-shaped waterway. Then turn right heading 120. Keep your turn within two miles of the airport, for radar vectors to Woodside, direct Tails, direct. Maintain VFR conditions at or below 1,100 until crossing the Oakland 165 radial. Then climb and maintain 2,100. Expect 5,000 five minutes after departure. Norcal Departure Control frequency 135.65. Squawk 4551."​
 
I can't get the suggestions to work for me.

How bad would it be to copy the table to a temp table and start deleting rows based on inputs and then select the leftover records.
Lets say the biggest this table would ever get is maybe 5-10k rows. and the table has 10 columns.
 
SixPapaCharlie said:
I can't get the suggestions to work for me.

How bad would it be to copy the table to a temp table and start deleting rows based on inputs and then select the leftover records.
Lets say the biggest this table would ever get is maybe 5-10k rows. and the table has 10 columns.
Click to expand...

If going this route, just export to a text or csv file, then open with Excel and filter your results, or build a pivot table to output your results.
 
SixPapaCharlie said:
I can't get the suggestions to work for me.

How bad would it be to copy the table to a temp table and start deleting rows based on inputs and then select the leftover records.
Lets say the biggest this table would ever get is maybe 5-10k rows. and the table has 10 columns.
Click to expand...

5-10k rows you can run O(n^2) operations on if you want and it wouldn't matter...

Not sure why the suggestions don't work - can you paste the 'CREATE TABLE' statement for your table?
 
Good lord, didn't know there were so many coders here.

Any ABAP coders ??
 
Scrabo said:
Good lord, didn't know there were so many coders here.

Any ABAP coders ??
Click to expand...

My Dad tried to get me into that when I was in college. I found it boring so I decided to go a different route. I frequently kick myself for not going that direction.
 
Salty said:
Actually, after a moments reflection, the original approach of FormerHangie's that I quoted with the simple addition of "and (variableA is not null or variableB is not null or variableC is not null) would solve the problem with a single query that will optimize well by the sql engine without returning all rows if all Params are empty.

CREATE PROC MattyP
(@VariableA int = NULL, @VariableB int = NULL, @VariableC int = NULL)
AS
BEGIN
SELECT Column1, Column2, Column 3
FROM Table
WHERE (Column1 = @VariableA OR @VariableA IS NULL)
AND (Column2 = @VariableB OR @VariableB IS NULL)
AND (Column3 = @VariableC OR @VariableC IS NULL)
AND (@VariableA IS NOT NULL OR @VariableB IS NOT NULL OR @VariableC IS NOT NULL)
END

The parenthesis are important
Click to expand...

The parenthesis are always important if you have an OR in your WHERE clause. If you leave one out or misplace it you'll get the entire table in your result set.

Also, if I'm writing a proc that has nullable parameters, I will put some parameter checking logic in before the SELECT statement, something like

IF @VariableA IS NULL AND @VariableB IS NULL AND @VariableC IS NULL
BEGIN
PRINT 'You must pass in at least one parameter'
RETURN (-100) -- or some other negative number
END

You can certainly add that condition to the select so that if all the parameters are null that nothing is returned. I prefer the parameter checking because it warns the developer that's calling the proc that something is wrong.

Let me log into work and write such a query, I'll get back here within the hour.
 
Awright, I'm back, I just wanted to get the car washed before it got dark.

I tried the code I suggested:

declare @CustomerID varchar(100) = NULL
declare @FirstName varchar(100) = NULL
declare @LastName varchar(100) = NULL

select * from dbo.Customer
WHERE (LastName = @LastName OR @LastName IS NULL)
AND (FirstName = @FirstName OR @FirstName IS NULL)
AND (CustomerID = @CustomerID OR @CustomerID IS NULL)

When I set the @LastName field to 'Hernandez', I got a large number of rows, all of which had a last name of Hernandez. Next up, I set the @FirstName field to 'Ana', and got a considerably smaller number of rows, all of whom were named Ana Hernandez. Then, I set the CustomerID to the customer ID of one of our Ana Hernandeses, and got back one row for that customer. Then I set the customer ID to the value of one of our customers who is not named Ana or Hernandez, and got back nothing, which is what I'd expect.

I did look at the execution plan, and just as I expected it did a table scan. This is not something I would deploy to a production environment. Typically what I'd do in this situation is write a couple of different SELECTS depending on the most likely usage, and fall back to this one if none of the more efficient ones matched the supplied parameters.

Also, we have no customers named Bryan Hernandez.
 
ABAP is for SAPs!

So sayeth the MS Dynamics AX Financials consultant. :)
 
SixPapaCharlie said:
All variables are defaulted so they won't be null.
So this is the whole thing (psuedocode again)
All those ADNS in the ware clause would be comparing variables that would have been passed in w/ positive values or be defaulted to 0 if not passed in.


ALTER PROCEDURE [bravotango].[ADVANCED_SEARCH]
@MINX INT = 0,
@MAXX INT = 0,
@MINY INT = 0,
@MAXY INT = 0,
@STYLE INT = 0,
@X INT = 0,
@Y INT = 0,
@Z INT = 0,
@ID INT = 0

AS
BEGIN

SELECT A.Value1 AS Value1_ID,
A.Value2 AS Value2_ID,
AVG(A.Value3) AS Value3,
AVG(A.Value4) AS Value4,
AVG(A.Value5) AS Value5,
AVG(A.Value6) AS Value6
AVG(A.Value7) AS Value7,
AVG(A.Value8) AS Value8

FROM Table AS A

WHERE 1=1
AND A.Value4= @X
AND A.Value1BETWEEN @MINX AND @MAXX
AND A.Value2 BETWEEN @MINY AND @MAXY
AND A.Value3= @V
AND A.Value4= @Z
AND A.Value5= @STYLE
AND A.Value6= @ID

GROUP BY A.Value1, A.Value2

END

GO
Click to expand...


This is very different from what you first asked about. I don't see anything in your select allowing for some of the parameters defaulting to zero, and you have the Value4 column having to equal both @X and @Z. Also, you don't need the 1=1.
 
You must log in or register to reply here.
Back
Top