Setting multiple values

Question

Setting multiple values

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Rags, I've got one more "by the way"... you are using this just for a GUI passing parameters, aren't you?

If you're trying to split a whole column of CSV's, then we need to "talk" some more about the Tally Table and how to normalize the data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rags-180338 SSC-Addicted Points: 434 More actions · Answer 2

J-thanks! i'm always up for learning new ways to do things.

Jeff - Like I just said to J, I'm always up for learning new ways of doing things. I need to understand how things work before I use them. I'm a novice when it comes to T-SQL and want to absorb as much as I can. I have found that SQLServerCentral.com has the most knowledgealble and helpful community. Anyways, to answer your question, yes I plan to use this for passing values from a gui into a stored procedure. You mentioned a bunch of other things that a tally table can do, can you suggest any links to articles I can read up on them? One other question I have is about using "Proper Case Function". I have used the case function before but I highly doubt it was "Proper". How do you write a proper case function?

Thanks for your help!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Rags (4/1/2008)
J-thanks! i'm always up for learning new ways to do things.
Jeff - Like I just said to J, I'm always up for learning new ways of doing things. I need to understand how things work before I use them. I'm a novice when it comes to T-SQL and want to absorb as much as I can. I have found that SQLServerCentral.com has the most knowledgealble and helpful community. Anyways, to answer your question, yes I plan to use this for passing values from a gui into a stored procedure. You mentioned a bunch of other things that a tally table can do, can you suggest any links to articles I can read up on them? One other question I have is about using "Proper Case Function". I have used the case function before but I highly doubt it was "Proper". How do you write a proper case function?
Thanks for your help!

I have only one link for what a Tally table can do, it's very old, and certainly doesn't contain everything I've used a Tally table for...

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

There are a couple of sites that have pages dedicated to using a Tally or "Numbers" table, as most call them. Just be careful because some of the code isn't quite right. Even the URL above has an "oversight" in it in the first code snippet under "Parsing a string"... it won't find NULL/missing entries and it only finds numbers that are in the Tally table.

The best bet, so far, is to search SQLServerCentral.com for the words "Tally Table" or "Numbers Table".

So far as a "Proper Case" function goes, you're correct... most are not what I'd call "Proper Case". They're not even good for "Title Case" because they capitalize things like the word "is" and "the" when they shouldn't be, and they don't work properly on things like "McDonald" or "O'hare" or non-English words such as "de la".

I've not had to write a Proper Case function, yet, but perhaps I should. The way to do it properly is to capitalize the first letter of anything following a non-letter character, and then replace the exceptions from an exception table. Best way to do that in 2k5 is to use a CLR (the only one that I think should be used, so far) is to use a RegEx CLR written by Matt Miller. Here's a thread where Matt explains a lot of great things about proper casing and there's a good link for Acronymns.

http://www.sqlservercentral.com/Forums/Topic475800-149-1.aspx

As a side bar and since you're kinda of a newbie on the forum, let me just say that when Matt posts something, you can bet it's worth the read. There are some other outstanding individuals who fall into that same hallowed category... it's actually a pretty big list and you'll see who they are.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

before you go singing my praises -

Sadly - the definition of Proper case IS "every word starts with upper case letter, and then lower case". So - it's not at all perfect. It should be called "Proper Name Case", since that is what it's for.

I don't know of any function that adequately "cases" everything. You could probably whip up something pretty fast that will handle making things capitalized after periods, and not elsewhere, but once you start getting "thorough" about it, there are just a LOT of permutations to deal with.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

J-440512 SSCertifiable Points: 6285 More actions · Answer 5

Mr. Moden,

I was talking about a GUI. Working off a DevExpress grid in Delphi, the user can multi-select several lines. It is then a simple matter of looping through the Grid.SelectedNodes and for each such node, enter the corresponding primary key value in myTempSelection table.

But there are cases where all the records in a grid are selected. Rather than selecting all the records in the grid, I have two options:

- "print product sheets for all SELECTED items" (loop through the selected nodes)

- "all product sheets for all items" (in the dataset).

In the seond case, the user has requested records meeting a specific set of requirements, such as:

- item status (new, active, discountinued...)

- item part number like '001%',

- etc.

Execution of the SQL statement with the parameters fills the dataset displayed in the grid. If I want to print the spec sheets of all the items in the data set, instead of looping through the grid's selected nodes, a simple statement such as

[font="Courier New"]INSERT INTO myTempSelectionList (OwnerID, productID)

SELECT @ls_OwnerID, C.productID

FROM Customer_PO_Item C

WHERE C.Customer_PO_ID = @pi_POID[/font]

Hope this clarifies things.

Regards

J-440512 SSCertifiable Points: 6285 More actions · Answer 6

Pressed "Post Reply" too quickly. Forgot to add this:

I have also used the approach of comma-separated single parameter but it has limitations:

- the is a practical limit to the length of such a parameter

- the performance is worse when the IN clause contains 400 records as opposed to

. joining with a 400-record table.

For instance I need to print 400 invoices but they do not necessarily have consecutive InvoiceID's.

Still, the tally table method is something new to and I will study it.

Rags:

It is apleasure to help someone who is eager to learn. My solution is probably not the best and I invite comments on it.

Regards

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

J (4/2/2008)
Pressed "Post Reply" too quickly. Forgot to add this:
I have also used the approach of comma-separated single parameter but it has limitations:
- the is a practical limit to the length of such a parameter
- the performance is worse when the IN clause contains 400 records as opposed to
. joining with a 400-record table.
For instance I need to print 400 invoices but they do not necessarily have consecutive InvoiceID's.
Still, the tally table method is something new to and I will study it.
Rags:
It is apleasure to help someone who is eager to learn. My solution is probably not the best and I invite comments on it.
Regards

Hi J,

What I' was a little bit worried about is that the myTempSelectionList table would appear to become a hot spot and possibly a source of deadlocks unless you're very careful with what your clustered index is (if you have one) and how transactions are formed around the table in your code. If it works under load, then outstanding job and my hat's off to you for thinking outside the box on "parameters".

By the way, when and how do you cleanup the myTempSelectionList table?

I agree... In SQL Server 2000, the practical limit is 8000 bytes or multiples or 8000 bytes up to a practical limit... unless you use a TEXT datatype for the parameter. Of course, that has it's own problems. Your permanent working table method seems like a great workaround for that! In 2k5, VARCHAR(MAX) works very well for huge delimited parameters and instead of using a Tally table (lots of people don't understand the practicality of a million row Tally table), you can always create a cteTally based on the number of characters received in the paramater.

Still, the way you do the permanent working table method sure does seem like an easy thing to do... after all, why concatenate a bunch of stuff to send as a single parameter and then split once received. I think you've hit on a very nice solution. Still interested on your "cleanup" method and how you keep it from becoming a hot spot...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J-440512 SSCertifiable Points: 6285 More actions · Answer 8

Clearing myTempSelectionList.

Let's say there is a button on the GUI to print a bunch of specs sheets.

After Crystal Report has shown on screen the printout, returning control to the application, it is at that point that I issue the command, since the selection lifespan is over.

[font="Courier New"]DELETE FROM myTempSelectionList

WHERE OwnerID = same one used to fill myTempSelectionList[/font].

Since the table is cleared, I use a a field

[font="Courier New"]TempID bigint NOT NULL PRIMARY KEY IDENTITY[/font]

which is the clustered index.

Also, this is not an on-line transaction processing system. And I also avoid to involve the myTempSelectionList table in a transaction.

And thanks for your observations.

Regards

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Sounds like a winner, J. Thanks for sharing the tips.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J-440512 SSCertifiable Points: 6285 More actions · Answer 10

What I' was a little bit worried about is that the myTempSelectionList table would appear to become a hot spot and possibly a source of deadlocks unless you're very careful with what your clustered index is ...

.

Could you be more specific about "careful" ? (By the way, this is not under a full load just yet).

Tried to send you a private message but your allowed limit for such is exceeded.

Regards

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

J (4/3/2008)
What I' was a little bit worried about is that the myTempSelectionList table would appear to become a hot spot and possibly a source of deadlocks unless you're very careful with what your clustered index is ...
.
Could you be more specific about "careful" ? (By the way, this is not under a full load just yet).
Tried to send you a private message but your allowed limit for such is exceeded.
Regards

You can send an email if you want. I need the PM's I have but there's no way to download 100 enmasse and they won't give me more space.

Being careful with the clustered index means to understand that it does force a physical order to the rows. If your inserts end up in the middle of the table, they can take quite some time due to reordering.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J-440512 SSCertifiable Points: 6285 More actions · Answer 12

You can send an email if you want. I need the PM's I have but there's no way to download 100 enmasse and they won't give me more space.

Message intended for the forum coordinator:

Considering mr. Moden's stature (SSCertifiable Points: 7,940, Visits: 2,824), could you provide means to resolve the situation ?

And, mr. Moden, I have no idea on how to get your private e-mail.

Regards all.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

J -

Click on his name on the left - you should get an option to "send an e-mail" to him.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

In the at the very top of the "left window" for each post (just above where you got my posting stats from), just click on my bolded name... new menu will appear where you can send email and other things.

So far as the request for being able to download the PM's to my local so I can delete them from the PM area, I've put that request in previously. Seems that it would be more difficult than either of us would think.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 15

I see that the not so uncommon thing has occurred... Matt beat me to it 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)