How to create dynamic parameters in SQL Server stored procedure

[darkmatter]

@M_Adams said:

'' = null leaves me with a cheap, dirty feeling.

neither is equal to anything.
and that's the fucking problem.
they can be is null or is not null though.
Fuck you Oracle.

[M_Adams]

@delfinom said:

What if my middle name is ''?

See below:
@darkmatter said:

they can be is null or is not null though. Fuck you Oracle.

Emphasis mine :)

And actually Fuck you SQL standards committee for foisting null on us in the first place as a "value" (snicker) you can "denote". Null has no place in a database! It should be reserved for the DBMS to be able to report: "What you talkin' about, Willis‽" That is, generated nulls due to pivots and outer joins ( where you've potentially "wandered off the reservation" as far as the relational theory is concerned). Yes, I'm a convert to 'Date-ism' ( C. J. Date, that is) :)

[cartman82]

@M_Adams said:

Null has no place in a database

Can you elaborate?

How would you refer to a 'missing' value in a row?

[Onyx]

Oh boy! Reruns!

/me gets popcorn

[delfinom]

@M_Adams said:

And actually Fuck you SQL standards committee for foisting null on us in the first place as a "value" (snicker) you can "denote". Null has no place in a database! It should be reserved for the DBMS to be able to report: "What you talkin' about, Willis‽" That is, generated nulls due to pivots and outer joins ( where you've potentially "wandered off the reservation" as far as the relational theory is concerned). Yes, I'm a convert to 'Date-ism' ( C. J. Date, that is) :)

I actually like NULL. I have test data which can have up to ~100 different unique results which end up being columns. The problem is I can't normalize that into separate tables. The test data comes from tests where all those results can be enabled/disabled on the fly depending on what is being tested. Incidentally I eliminated the original "normalization" when there were specific test types which ended up being a huge burden on test maintenance and flexibility. Right now,I just have the data inserted into columns, if it stays NULL then it clearly wasn't tested. And I am looking at ~100k records being generated a week so fuck doing EAV.

[M_Adams]

Whereof one cannot speak, thereof one must be silent. ---Ludwig Wittgenstein

[code]
--not necessarily the "best" way, but for expository purposes, it'll do...
create table customer (
id_code id_domain UNIQUE,
f_name text,
l_name text,
...
)
create table c_middle_i (
id_code REFERENCES customer.id_code,
initial text CONSTRAINT( initial =~ / \A \w | $space \z /ixms ),
)
[/code]
Now, the lack of an { id_code, initial } tuple in c_middle_i means "no info/don't know" because we have no info in the DB! An { id_code, initial } tuple where initial is a space character can be used to mean "customer has no middle initial"; because it's not null, it's a blank space.

[darkmatter]

@M_Adams said:

And actually Fuck you SQL standards committee for foisting null on us in the first place as a "value" (snicker) you can "denote". Null has no place in a database! It should be reserved for the DBMS to be able to report:

The fuck you is about an empty string being null, which Oracle is the only database (that I have used anyway) with such a shitty convention. Nulls themselves are fine.

[M_Adams]

@darkmatter said:

The fuck you is about an empty string being null,

Which is why I actually separated those statements (yours and mine). The emphasis mine was me agreeing with the above. The follow up statement, And actually..., was my own personal diatribe. Different paragraphs, different statements.

[cartman82]

@M_Adams said:

Now, the lack of an { id_code, initial } tuple in c_middle_i means "no info/don't know" because we have no info in the DB! An { id_code, initial } tuple where initial is a space character can be used to mean "customer has no middle initial"; because it's not null, it's a blank space.

OK, let's see if I understood.

Table customer only has rows that would be NOT NULL otherwise. Then for each column you want to be NULL-able, you add a separate table. Then when you want to get your data, you write a huuuge slow-ass join query and get something like this:

SELECT c.l_name, m.initial
FROM customer c
LEFT JOIN c_middle_i m ON c.id_code = m.id_code

l_name | middle
----------------------------------
"Jack" | "A."   <- we have initial for this record
"Mike" | ""     <- doesn't have one, empty string?
"Nick" | ???    <- not found, but what is here? NULL?

Do I have it right? And what about the third row?

[dkf]

I take it you've never encountered someone genuinely without a surname. I have. He removed his family name from his official name by deed poll (by all reports after a nasty bust-up with both parents who were going through an acrimonious divorce at the time). Caused no end of trouble for various systems at that university.

If you're going to go all relational-strict, you have to remember that virtually all information may be absent under some circumstances.

Filed under: You Have A Fine Model, Pity About Reality

[antiquarian]

@M_Adams said:

Null has no place in a database! It should be reserved for the DBMS to be able to report:

You can have my NULL when you pry it from my cold, dead fingers.

[M_Adams]

@dkf said:

I take it you've never encountered someone genuinely without a surname.

Nestor nmi Des Jardin. Army buddy.

@cartman82 said:

...

[code]
l_name | middle

"Jack" | "Mr." <- we have initial for this record ← first the constraint is one character in the set {\w, $space}, so you could enter "M"
"Mike" | "" <- doesnt have one, empty string? ← No. a one space string: "customer has no middle initial"; because its not null, its a blank space. "Nick" | ??? <- not found, but what is here? NULL? ← yes, a system generated null, only if you left outer join` otherwise this tuple will be absent.
[/code]

@cartman82 said:

a huuuge slow-ass join query

find a better DBMS.

[boomzilla]

@M_Adams said:

find a better DBMS.

Bullshit. Bullshit. Full of Shit.

Seriously arguing for fully normalized data is living in a dream world. Or at least in a world with really small databases.

[cartman82]

@M_Adams said:

explanation

I understand. The only way you can get NULL is in generated results. Everything else is a lookup table.

Advantages: Perhaps some DB size savings, cleaner data representation, no ambiguity in results
Disadvantages: Speed

Considering current RDBMS-s are memory and CPU bound, performance could be an issue. But I can see the elegance in this.

BTW, would you consider something like javascript approach, where you have your "standard null (null) and "we really really mean it this time null" (undefined). Eg:

SELECT c.name, p.number, p.hours
FROM customer c
LEFT JOIN phones p ON c.id = p.customer_id

name   | number | hours
----------------------------------
"Jack" | "123"  | "08-16"   <- Jack's job phone number, all data
"Jack" | "456"  | NULL      <- Jack's home number, missing 'hours' data
"Nick" | EMPTY  | EMPTY     <- No phone record found

Replace EMPTY with NONE, UNDEFINED or whatever else you like.

[M_Adams]

@boomzilla said:

Bullshit. Bullshit. Full of Shit.

Seriously arguing for fully normalized data is living in a dream world. Or at least in a world with really small databases.

I do joins all the time between tables of the size:
[code]
SQL> select count(*) from itm_trn;

COUNT(*)

59420663

SQL> select count(*) from so_ln;

COUNT(*)

26414678

SQL>
[/code]

In Oracle, and they're quite snappy...

@cartman82 said:

BTW, would you consider something like javascript approach, where you have your "standard null (null) and "we really really mean it this time null" (undefined). Eg:

That's essentially what we've manually done (conceptually above)...

[cartman82]

@M_Adams said:

That's essentially what we've manually done (conceptually above)...

Ok, I'd like something like SQL version of undefined.

But I don't think those multi-joins are workable in reality.

[boomzilla]

@M_Adams said:

I do joins all the time between tables of the size:

...

In Oracle, and they're quite snappy...

I do too, and even with smaller tables, they often aren't. It's not a problem with simple queries, but more complex stuff often baffles the planner into full table scans. Especially when there are lots of left joins, which you'd definitely need if you were overly normalizing.

[abarker]

@M_Adams said:

Null has no place in a database!

I think a better counter argument to this would be dates. I work on some software that needs to track task scheduling, and when the task was completed. Sure, we could have designed the system to interpret the minimum date value as 'unshceduled', or 'not completed', but that doesn't really make much sense since there is a date there. Makes more sense to make the field nullable and have a null value take those meanings. Also makes it easier to remember what to look for when searching for unscheduled or incomplete tasks.

[M_Adams]

[code]
update table set the_date=to_date('infinity') where...(item is incomplete)
[/code]
would be nice, if supported by more products...

[M_Adams]

Or even a date range type, so one could say:
[code]...set scheduled_completed='(2014-08-11,]'....[/code]

(again some DBMS do provide this)

[darkmatter]

@M_Adams said:

"Mike" | "" <- doesn`t have one, empty string? ← No. a one space string:

eh... it works in this one specific scenario, but what about any situation where you really could have a space for data? Now you're stuck again unable to tell nulls from reality.

[M_Adams]

Strawman. Give a concrete.

[darkmatter]

Seriously?
Why not just suggest that nulls should be the string "NULL"?

How do you represent null in an integer field? With a space?
How about a CHAR(9) field? 9 spaces?

[abarker]

@M_Adams said:

Or even a date range type, so one could say:
[code]...set scheduled_completed='(2014-08-11,]'....[/code]

(again some DBMS do provide this)

Sure, a date range would be great, but (in my case) this suggestion wouldn't work. Because a scheduled task could be scheduled for [2014-08-11, 2014-08-15], so you'd still need a separate field for the completion entry. Plus, if the date range didn't have an end specified, wouldn't the end time be represented by a null, meaning that your [2014-08-11,] is really just [2014-08-11,null]?

[darkmatter]

@M_Adams said:

Strawman. Give a concrete.

Easy, a bit field where a 0 means "NO", a 1 means "YES". A NULL means not answered.
A space isn't even a valid entry here.... Or do we have to redesign all our database schemas to work without NULL support?

Even Oracle wasn't stupid enough to try to use an actual value to represent null, at least they tried to go with the absence of value.

[Matches]

When dealing with users, the first rule is to get all information about the users OUT of the relevant sql data.

In other words, when you register a user, assign a system unique data point that you do joins on, and pretty much all other fields are nullable. Based on the information you get, you will return the user if they relate to your query. If the user doesn't supply that data, they aren't part of that query.

Yes, part of the business decision is important as to why that information might be missing, but it's important to understand that not everybody has a social, not everyone has a middle name, or last name, or even gender.

Create your own reference id, then append whatever happens to be available.

Also note: if your reference ID is going to be an NT login or company serial number, use those as data points, NOT your system id. Too many times i've seen a persons name change due to marriage/whatever and have their NT login change, or quit/rehire to get a change in serial number. A system provisioned ID will help you make sane those changing data points.

My tables when related to agents typically look something like...

ID | UserID | NTLogin | SerialNumber | (First/middle/last name/job title/business segment/etc/etc/etc/etc/etc/effective start date/effective end date/first record flag/last record flag) where typically logic against fields 3/4 results in tying to field 2, and field 1 is the true unique id for the row.

[M_Adams]

1. The use of a space in the discussion was in re Oracle's equivalence between the empty string and null. And in the domain of middle initials a space is a perfectly good representation of 'I know this individual doesn't have a middle initial'. A missing data point is a perfectly good representation of 'Didn't Ask / Didn't Tell / Don't Know'.
2. Really, you're going to stuff a null into a bit field‽
3. 'A space isn't even a valid...' of course not! It makes no sense in that data domain!

Remember, null in SQL can mean a minimum of the following:

• Not Applicable (N/A) — cows don't have feathers.
• Not Known, may be known later, or not at all, or luser forgot to enter the info.
• Not Present ( the guy is bald ).
• Just a filler for your outer join.

Filed under: Turning a strawman into the Scarcrow.

[darkmatter]

To be honest, I don't even know what you're arguing anymore. All I can gather is that you're for using a value instead of a null. Except that in plenty of circumstances there is no value to use, like say, any of the situations already listed.
It's almost like you've never used a database.

[M_Adams]

@abarker said:

Plus, if the date range didn't have an end specified, wouldn't the end time be represented by a null, meaning that your [2014-08-11,] is really just [2014-08-11,null]?

Postgresql actually turns [2014-08-11,] into [2014-08-11, ∞ ], where ∞ is a date-time guaranteed to be later than any possible date-time. ( tomorrow never comes ). And this particular range is fully closed (sic). Postgresql also supports half-open ranges { all of [], (], [), (), are supported }.

[darkmatter]

Better scenario from real world:
We have a table fed by 2 separate processes. The first inserts records with nulls for the fields it does not have data for. The second updates the nulls to values. Yes, some of those could be bit fields. No we are not going to pick a random bit value instead of null, because then it would look like the second process already happened.

[abarker]

It's great that postgre offers that functionality, but not every DBMS includes date ranges and infinite dates. Date ranges would be great to have in all DBMS, but good luck getting that to happen. As for the concept of an "infinite" date, null seems to fill the gap pretty well.

[M_Adams]

@darkmatter said:

I don't even know what you're arguing anymore.

Use proper domain specific values for "properly missing" data. Your bit strings 0_b = "no", 1_b = "yes", 3_b = "not answered"; just to pull something out of the air that could work. Yes, it means instead of a bit ( 1-bit per value), you now have a trybble ( 3-bits per value ).

@darkmatter said:

We have a table fed by 2 separate processes. The first inserts records with nulls for the fields it does not have data for. The second updates the nulls to values...No we are not going to pick a random bit value instead of null...

Yeah, now we're going into the EAV vs Normalization quagmire especially in re bit fields... and much of that (to my chagrin) does involve the question of 'how good is your DBMS at optimization and query rewrite'.

And once again, if the bit field itself either has a value or is missing: Ludwig Wittgenstein

Filed under: My milk-stool is now complete.

[EKoske]

I do not see the need for dynamic SQL here at all.

I'd use something more like this:

CREATE PROCEDURE dbo.Pr_customers(
	  @firstname VARCHAR(90)=NULL,     
	   @lastname VARCHAR(90)=NULL,     
	   @age INT=NULL,  
	  @city  VARCHAR(90)= NULL,
	  @country VARCHAR(90) = NULL,   
	  @phone INT=NULL
	  )     
AS     
BEGIN 
	  SELECT 
	  	  * -- Insert complete column list here.
	  FROM Customers
	  Where  Firstname = Coalesce(@Firstname,Firstname)
	  AND LastName = Coalesce(@LastName,LastName)
	  AND Age = Coalesce(@Age,Age)
	  AND City = Coalesce(@City,City)
	  AND Country = Coalesce(@Country,Country)
	  AND Phone = Coalesce(@Phone,Phone)    		   
END 

If a parameter isn't supplied, then the value in that column for each row is compared against itself, and therefore automatically matches. If a parameter IS supplied, it filters on that supplied value.

[darkmatter]

@M_Adams said:

Use proper domain specific values for "properly missing" data. Your bit strings 0b = "no", 1b = "yes", 3b = "not answered"; just to pull something out of the air that could work. Yes, it means instead of a bit ( 1-bit per value), you now have a trybble ( 3-bits per value ).

Seriously? Bit strings now instead of a simple bit/boolean field? Extra normalization joins just to avoid something that's built into every major database ever? (okay most db, I'm sure there's got to be one out there somewhere that doesn't support null.) I can only assume you're just trolling.
However, I would like to see you burn more time trolling on how to replace nulls for the remainder of the field types that exist in databases!

[Matches]

Why does NULL = NULL evaluate to false in SQL server

In SQL server if you have nullParam=NULL in a where clause, it always evaluates to false. This is counterintuitive and has caused me many errors. I do understand the IS NULL and IS NOT NULL keywor...

Nulls are magic. You would still need to wrap all of those in isnull(Age, defaultvalue), and because of how you're doing functions against where statements, you're completely wrecking index usage to the point where pretty much anything you do is a full table scan.

Changing things like ANSI_NULL (and associated functions) also impact cross server queries. Certain combinations prevent you from being able to do them.

Or, a more practical example:

declare @firstname varchar(15)
set @firstname = null
;with a as(
select null as Firstname
union all select 'bill' as Firstname) 
select * from a
where Firstname = Coalesce(@Firstname,Firstname)

Output: bill

;with a as(
select null as Firstname
union all select 'bill' as Firstname) 
select * from a
where isnull(Firstname, 'default') = Coalesce(@Firstname,isnull(Firstname, 'default'))

Output: NULL, bill

To clarify: I'm not saying it can't be done as a stored procedure. I'm saying that in terms of reporting, where it is potentially being used to generate millions of rows (I don't know about the ongoing examples, but tools I've worked with do) - You trade SQL plan compilation/reuse for far more efficient SQL index utilization which when working with hundreds of thousands, millions, or billions or rows can mean the difference between a 5 second query and a 5 hour query.

[chubertdev]

I honestly see the word "strawman" as "I've lost this argument."

[PJH]

@M_Adams said:

you now have a trybble ( 3-bits per value ).

Trit.

[Arantor]

Ah and the natural true/false/FILE_NOT_FOUND rears its ugly head.

[Onyx]

@PJH said:

@M_Adams said:

you now have a trybble ( 3-bits per value )

Trit.

[darkmatter]

KHAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAN!!!!!!!!! 

[M_Adams]

I'm home now and have a few moments before dashing off to "Monday Emergency" crap...

@PJH said:

Trit

Nah, I chose trybble based on : bit→byte⇉nybble. I wasn't suggesting a base 3 number but a 3-bit aggregate, still in base 2. Plus, well, of course...( @Onyx ) tribble :)

Also:

@darkmatter said:

I can only assume you're just trolling.

TBH, I didn't start out that way... but...

When I showed up for work today, our wonderful ERP vendor support personnel had de-normalized a table for performance reasons. Without updating the system generated reports (also their maintenance responsibility) to account for the nulls they introduced.

So my cry of 'Null has no place in a database!', came from a butt-hurt reaction to having just been bit in the ass by this.

And, when everyone went all foamy-at-the-mouth, since my work was on hold I thought 'Why the hell not...'

I realize that we have, and have to deal with nulls. And given the state of the art in DBMS, we'll need to for a long time. That reality however, does not change the fact that nulls, and duplicate tuples, cause severe violations of predicate logic as it relates to relational theory.

They reduce the wiggle room for query rewrite and optimization by removing the certainty of various transforms that, in the absence of those anomalies would always be correct (according to theory). They violate the closure property of the algebra and calculus for relational and set theory (relational theory being a subset of set theory): they can cause violations of the Information Principle and reduce the system's ability to do key inference, just for some examples.

Just as the "rule" is benchmark then optimize, for code; Normalize first and de-normalize later, for databases (just remember to f'n fix what you just broke when you do so. —yah, still bitter about a 2½ hr waste of my day).

Any way, here's some cookies in apology:

[boomzilla]

@M_Adams said:

So my cry of 'Null has no place in a database!', came from a butt-hurt reaction to having just been bit in the ass by this.

Regressions suck.

@M_Adams said:

That reality however, does not change the fact that nulls, and duplicate tuples, cause severe violations of predicate logic as it relates to relational theory.

That's true. But it's also true that strictly adhering to relational theory is a barrier to performance and sanity.

@M_Adams said:

Any way, here's some cookies in apology:

NOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!!

Don't show weakness.

[M_Adams]

@chubertdev said:

I honestly see the word "strawman" as "I've lost this argument."

In rhetoric, and in logic, it's a more polite shorthand for "put up or shut up". It is the responsibility of the accuser to present compelling evidence and facts. All it would've took was an example I couldn't account for, instead I got Cloud-Cuckoo-Land: here in reply to this.

What ever.

[chubertdev]

It also makes for a good drinking game.

[Matches]

I propose we call it tribbing, since that will yield more interesting google results.

DISCLAIMER: DO NOT GOOGLE THAT FROM WORK!

[M_Adams]

@boomzilla said:

NOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!!

Don't show weakness.

But I made them with these...

[Onyx]

@M_Adams said:

But I made them with these...

Evil ideas thread. Pick your own directions.

[abarker]

@M_Adams said:

But I made them with these...

Good thing I hadn't eaten any yet. I'll just save them for later ...

[M_Adams]

+1
A simple like did NOT suffice! I REALLY needed that laugh today! Thanks!

[boomzilla]

@abarker said:

Good thing I hadn't eaten any yet. I'll just save them for later ...

I'm currently avoiding carbs, so I wasn't in danger.