This way of writing a multi-filter SQL Query is very nice and Works well for tables with a few hundred, or maybe a few thousand, records. Where it fails is when you have millions of records. Each "or @filterField is null" will match very record and Thus will result in a table scan, which you don't want.
Thus comes the dynamic SQL into living. Either you do i tthis way or you do it using temporary tables, which have their own set of problems.
But the notes about SQL injection is valid, and you should look into doing parametrised dynamic SQL.
Somebody mentions that sp_executeSQL parametrised vierything, but it wonøt Work when the parameters is inserted into dynamic SQL.
So: Beware: Here be dragons.
dsckeld
@dsckeld
Best posts made by dsckeld
-
RE: How to create dynamic parameters in SQL Server stored procedure
Latest posts made by dsckeld
-
RE: From a single INSERT statement
All I'm trying to do is get to the root where it's actually referencing tables, so I can... um... replace one of the tables with an indexed view.
select name from sysobjects inner join syscomments on sysobjects.id = syscomments.id where xtype='v' and text like '%TheNameImLookingFor%'
is probably much better on the eyes in that environment. Note that if the views can be very long you have to concatenate view definitions from syscomments based on the order they appear in, ruled by the "number" column.
-
RE: How to create dynamic parameters in SQL Server stored procedure
This way of writing a multi-filter SQL Query is very nice and Works well for tables with a few hundred, or maybe a few thousand, records. Where it fails is when you have millions of records. Each "or @filterField is null" will match very record and Thus will result in a table scan, which you don't want.
Thus comes the dynamic SQL into living. Either you do i tthis way or you do it using temporary tables, which have their own set of problems.
But the notes about SQL injection is valid, and you should look into doing parametrised dynamic SQL.
Somebody mentions that sp_executeSQL parametrised vierything, but it wonøt Work when the parameters is inserted into dynamic SQL.
So: Beware: Here be dragons. -
RE: REVERSE(SUBSTRING(wtf))
@theflin said:
My current favorite stored procedure in our code base.
SELECT WBSID, REVERSE(SUBSTRING(REVERSE(level1 + '.' + level2 + '.' + level3 + '.' + <br> level4 + '.' + level5 + '.' + level6), <br> PATINDEX('%[^.]%', REVERSE(level1 + '.' + level2 + '.' + level3 + '.' + <br> level4 + '.' + level5 + '.' + level6)), 25)) + ' | ' + description AS MasterWBS <br> FROM WBSMasterTbl<br> WHERE Display = 'True'<br> ORDER BY MasterWBS<br></blockquote> </p><p mce_keep="true">To me it looks like a fine way to do string maching from the right of the string. One thing that SQL servers doesn't seem to be too good at out of the box.</p><p mce_keep="true">Reverse the string and reverse the search pattern and Bob's your uncle.</p><p mce_keep="true">Otherwise: What Ronald said.</p>
-
RE: Are the MS Dynamics team on crack?
@SilentRunner said:
What the hell is a "sproc"?
Shorthand for Stored Procedure on database servers. Probably coined to make SQL people look more important than they are.
SQLShark
-
RE: Why? Why would they do this?
@woodywood245 said:
I found two WTFs. 1. It's too small to read comfortably, and using zoom on the browser doesn't work. 2. Every time I turn a page, it either won't turn all the way, or when it does, it thinks I want to turn it back. Even when I let go of the mouse button.
It's a previve. hence the bad image and now real zoom. But did you perchance happen to try double-clicking in the corners? Hint: Both top and bottom corners are active. Et Voila! Accessibility for people with pens, pressure sensitive screens and other strange I/Fs.
But why didn't they not just take one of the established readers instead? Too expensive?
-
RE: I am a genius.
@Wiguyy said:
Actually, there was reason to use many colors. In brief, each color represented a "province" on a map, so the more "provinces", the more colors needed. Possibly not the best system, but this was reasonably flexible for its purpose.
And I thought that some mapping theory had shown us all long time ago that whatever map with areas (such as provinces) you can conjure up, you can always do it with just 5 colors. Except when you are going to draw a map on the back of yur Möbius headband, you will need no more than 6.
So, we might ask agian, why the multitude of colors? I could have understood the need if you were doing some gridding with population density or such. But still, it would be a stretch since people would be unable to accurately distinguish the colors.
/Me :)
-
RE: An error was unable to be described usefully
@Zemm said:
@Master Chief said:
I can go to walmart right now and buy a 16 gig flash drive for 20 dollars or a terrabyte hard drive for 100. Those kinds of arguments don't hold water anymore.
Just because you can, doesn't mean everyone can. And compression is more for transportation than storage. The fatest Internet upload speed we can get in our office is 384kbps, which is about 140MB an hour. If a file is 2GB smaller using a tarball instead of a zip it will save half a day!
(Though I checked, around here a 1TB drive is $59 and 2TB for $99 - the Flash drives are slightly more expensive than yours) It can be quicker to drive to the store, buy the drive, copy data onto it, and courier it to the destination than to upload it over the Internet.
I will agree on the "save data amounts for transport" any day. Which is why I don't care much for XML files used for transporting data to portable devices. I would often rather build a lean, slick, binary format.
Regarding getting a drive: Way back, my Communications Theory teacher told us never to underestimate the bandwidth of a truck full of backup tapes. Even when driving for days it can be very high. When using todays much larger disks, I bet the rate from the US east- to the west coast could be staggering.
-
RE: KMPlayer threatening me :-D
@nexekho said:
Erm, what graphics card have you got to NOT have all h.264 decoded on-GPU? VLC supports this. WMP supports this. Can't speak for iTunes. Flash supports this.
Erm, wouldn't that be any graphics card older than a couple of years. I don't think any of my 4 laptops can do this.
-
RE: League of Legends text wtf... and the viral installer.
@astonerbum said:
O and the installer is actually a virus, it created an invisible process on windows vista which could not be found in process explorer which used up 100% of the hard drive access capabilities until the machine was turned off. (ok not 100% but basically opening windows explorer took a minute)
Strangely enough I have a lot of those programs floating on my PC. They are even clever enough to tell System Idle to report very close to 100%.
Mainly those programs are called "excessive disk activity" and can come from anything reading large portions of a disk. Like, y'know, installers installing a couple of gigabytes. -
RE: "Lord of the Web" 2.0
Hmm, when I look in the mirror, I certainly don't see khaki pants, coffee mug and "CTPs". And I once were an MVP. It didn't even take more than my being helpful to become an MVP.
Maybe the worst part to admit is that I were an MVP (embedded) because of a mediocre knowledge of VBScript as used on early Windows CE PDAs.
(Let the bashing begin)