May 15, 2014 at 6:52 am
Hi, I'm trying to fix a stored procedure within our production db.
We have a notes table where users can enter free format details against a record via our intranet portal...
We can also add notes to this table via a lookup table - to put items on hold and provide a pre-defined reason... the text is submitted into the note...
ironically, some users decide to edit the note, appending a date... to this pre-defined note
We have a lookup table, where a user selects a reason and this gets recorded in a free format note field
1 Reason1
2 Reason2
3 Reason3
4 Reason4
Thus if Reason4 is selected and added to the notes table, the note would be Reason4
however when a user edits this it becomes Reason4 (15/05/2014 for 1 week)
We have a stored procedure that works if the reason text is as expected... it hits a match - however we now have to plug that hole as the edited record will not match.
SELECT ProjectId,
(
SELECT ReasonText FROM OnHoldReasons
WHERE (ReasonText IN
(SELECT Note FROM Notes WHERE (NoteID IN
(SELECT MAX(NoteID) FROM Notes
WHERE (notes.PackageID = dbo.Package.PackageID) AND (dbo.Package.ProcessID = 8) AND (NoteTypeID IN (1,2,6)) AND
(Note IN (SELECT ReasonText FROM dbo.OnHoldReasons))
)) ))
) AS OnHoldID
FROM dbo.Package WHERE dbo.Package.Enabled = 1
AND ProjectID = 90
The problem is centered around
(Note IN (SELECT ReasonText FROM dbo.OnHoldReasons)
In effect, I need to reverse the check so that the ReasonText is somewhere within the note... but we have up to 10 Reasons !!!
akin to something like (SELECT ReasonText FROM dbo.OnHoldReasons) IN %NOTE%...
The reason text is variable length text, and should be at the start of the note... but cannot be guaranteed.
Any advice from SQL experts?
many thanks
Brian
btw, everyone may say this as an excuse but its true, I didn't design this database or how it works. I have to add things in and update it 🙁
May 15, 2014 at 7:43 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 18, 2014 at 7:09 am
...The reason text is variable length text, and should be at the start of the note... but cannot be guaranteed...
This will force the query to perform a table scan since you'll need to perform a wildcard search LIKE %...%.
Even when changing it to use a Full-Text search performance will degrade over time.
I'd rather add the ID of the lookup table to dbo.OnHoldReasons.
A more detailed explanation would require more detailed information from your side as Sean already mentioned.
May 18, 2014 at 7:17 am
LutzM (5/18/2014)
...The reason text is variable length text, and should be at the start of the note... but cannot be guaranteed...
This will force the query to perform a table scan since you'll need to perform a wildcard search LIKE %...%.
Even when changing it to use a Full-Text search performance will degrade over time.
I'd rather add the ID of the lookup table to dbo.OnHoldReasons.
A more detailed explanation would require more detailed information from your side as Sean already mentioned.
Depending on how many rows and columns are in the lookup table, a table scan of the lookup table may not be an issue. If we are talking about the single lookup table for all lookups, that may be an issue.
May 18, 2014 at 7:38 am
Lynn Pettis (5/18/2014)
...Depending on how many rows and columns are in the lookup table, a table scan of the lookup table may not be an issue. If we are talking about the single lookup table for all lookups, that may be an issue.
To quote the OP:
...
We have a lookup table, where a user selects a reason and this gets recorded in a free format note field
1 Reason1
2 Reason2
3 Reason3
4 Reason4
...but we have up to 10 Reasons !!!...
So, based on the information provided by the OP we're talking about a table with two columns and 10 rows.
Since it's a stored procedure I'd get the lookup ID separately and exclude the lookup table and the wildcard search from the join.
May 18, 2014 at 9:33 am
LutzM (5/18/2014)
Lynn Pettis (5/18/2014)
...Depending on how many rows and columns are in the lookup table, a table scan of the lookup table may not be an issue. If we are talking about the single lookup table for all lookups, that may be an issue.
To quote the OP:
...
We have a lookup table, where a user selects a reason and this gets recorded in a free format note field
1 Reason1
2 Reason2
3 Reason3
4 Reason4
...but we have up to 10 Reasons !!!...
So, based on the information provided by the OP we're talking about a table with two columns and 10 rows.
Since it's a stored procedure I'd get the lookup ID separately and exclude the lookup table and the wildcard search from the join.
So, a table scan of a table with 10 rows and 2 columns won't be an issue. Now, if that were 10,000 rows with 2 columns possibly. It would depend on how big each of the columns are in the table.
All I am saying is it is something to consider when designing but in application it may not be an issue.
When it comes to lookup tables I prefer to have small independent ones rather than on lookup table to serve them all (which unfortunately is what we have out here).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply