September 4, 2014 at 9:00 am
CELKO (9/3/2014)
3. A column is a scalar value drawn from a domain.
Can you support this statement with something that is not in your books?
3. Some operations apply to the rows within a table. You insert, delete and update by the row, not by the column.
If you teach that you update by the row, people won't change their procedural programming thinking.
Any splitting function will convert a delimited list into a nice normalized table.
No, they do not. :satisfied: Did you see my articles in which I listed all the errors I found in writing a parser in SQL. When you pass a parameter to a procedure, you get a lot of smarts and error messages. In theory, you could write splitter to do the same thing. Want to try it?
This is why DB2 and Oracle use the long parameter list internally for huge lists. They cannot afford errors at that level.
As I told you, I tried to read your articles but couldn't focus on them when I read store procedures. That just shows me the lack of attention to detail and that you're not really analyzing what you write. Long parameter lists are just procedural programming and a waste of memory space. You want to parse the parameters within a delimited string? It's easy, we have parsing functions that will do the job and exception handlers to work around errors. Last but not least, if you're letting your users to write all the parameters, then you're screwed, the front-end should do that while the user just selects the options needed.
EDIT: I forgot to mention that the DelimitedSplit8K function is pure SQL and can be "easily" translated into the SQL implementation that you prefer. I just did it with the hardly ANSI compliant Oracle.
September 4, 2014 at 9:07 am
I wouldn't recommend using JOIN or APPLY because it could generate duplicates. In this case, I prefer to use IN or EXISTS.
Hi Luis C., could you please explain a little bit on the above where a JOIN or APPLY could generate duplicates. Just trying to understand little bit more
September 4, 2014 at 9:17 am
Sowbhari (9/4/2014)
I wouldn't recommend using JOIN or APPLY because it could generate duplicates. In this case, I prefer to use IN or EXISTS.
Hi Luis C., could you please explain a little bit on the above where a JOIN or APPLY could generate duplicates. Just trying to understand little bit more
Basically, it shouldn't happen if your parameter list is constructed in a proper way, but I don't like to assume. Here's an example:
DECLARE @ParameterList varchar(8000) = '1,2,2,5'
DECLARE @Sample TABLE(
myid int,
myvalue varchar(50))
INSERT @Sample VALUES
(1,'First Value'),
(2,'Second Value'),
(3,'Third Value'),
(4,'Fourth Value')
SELECT *
FROM @Sample s
JOIN DelimitedSplit8K(@ParameterList, ',') split ON s.myid = split.Item
September 4, 2014 at 9:21 am
Luis Cazares (9/4/2014)
Sowbhari (9/4/2014)
I wouldn't recommend using JOIN or APPLY because it could generate duplicates. In this case, I prefer to use IN or EXISTS.
Hi Luis C., could you please explain a little bit on the above where a JOIN or APPLY could generate duplicates. Just trying to understand little bit more
Basically, it shouldn't happen if your parameter list is constructed in a proper way, but I don't like to assume. Here's an example:
DECLARE @ParameterList varchar(8000) = '1,2,2,5'
DECLARE @Sample TABLE(
myid int,
myvalue varchar(50))
INSERT @Sample VALUES
(1,'First Value'),
(2,'Second Value'),
(3,'Third Value'),
(4,'Fourth Value')
SELECT *
FROM @Sample s
JOIN DelimitedSplit8K(@ParameterList, ',') split ON s.myid = split.Item
Got it!!! Thank you very much for putting this up quickly.
September 5, 2014 at 7:14 am
Looks like I'm kind of late to the party, but wrote some blog posts a few years back that walk through a few possible options.
http://tavislovell.com/how-to-use-a-table-valued-parameter-in-sql-server-2008/
Technical conquests for the nerd in all of us
www.tavislovell.com
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply