September 16, 2013 at 6:44 am
Hell All,
I am facing a bit of challenge:
I have a table with the following structure:
[tabid] [int] IDENTITY(1,1) NOT NULL,
[tabname] [nvarchar](50) NULL,
[description] [nvarchar](50) NULL,
[parent] [int] NULL,
[roles] [nvarchar](50) NULL,
[tabposition] [int] NULL,
[tabstatus] [int] NULL,
Some sample data is the following:
1HomeDashboarden.aspx/portalmodules/dashboardNULL1;6;11
2MaintenanceMaintenanceen.aspx/maintenance/maintenanceNULL1;71
126SiteSiteenbank.aspx/siteNULL11;21
I wrote a stored procedure that takes a string of values, seperated by semicolon as parameter. The procedure is below;
ALTER PROCEDURE [dbo].[selectUserTabsByRoles]
@var varchar(max)
AS
BEGIN
SELECT distinct * from tbl_tabs
where ( PATINDEX('%'+left(@var,1)+'%', roles) > 0
or PATINDEX('%'+right(@var,1)+'%', roles) > 0 ) AND parent is null and tabstatus =1
ORDER BY tabposition
END
My problem is, when I pass a parameter like 1; it fetches all rows with roles having 1. But I realised that the last row in the sample data does not have 1 as roles, but rather 11.
I urgently need help on this.
Thanks
September 16, 2013 at 8:10 am
Would this article help you?
September 16, 2013 at 11:05 am
In the parameters to patindex, you are creating a wildcard that looks for the first character of your delimited string, (or the last character) but what you want is to look for everything before the semicolon. You need to locate the semicolons in your delimited string.
So, to find the first value in your delimited string, you would write:
left(@var, isnull(nullif(patindex('%;%',@var),0)-1, len(@var)))
The isnull/nullif statement is to prevent an error in case your variable has no semicolons, in which case it will search for the entire string.
That will handle the first value, but what about the second, etc? If there are always only one or two values, you can use the technique you started with, but if that's the case, why bother using delimited strings in the first place?
The article Luis posted explains how to handle the parsing, but note that the techniques for parsing delimited strings have improved since that was written. See http://www.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D for more information.
Finally, your performance is going to be terrible, because your database is non-relational. You should never have multiple values in a single column! People always state that they can't change the schema, but if the schema is wrong, you're far better off fixing it than you are writing non-performant workarounds using complicated code that is susceptible to bugs.
I hope that helps.
-alex.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
September 16, 2013 at 12:21 pm
If you want more specific coding help you will need to post some details about your tables and the data. Normally I would jump on the band wagon about normalizing your data. However, I have a feeling this really might be outside your control. It seems pretty obvious you are working with a DNN module here although the structures you posted are not consistent with the structures from DNN. This leads me to believe that you are working with a custom module. If it is in house, then you need to fight hard to normalize the data. If it is third however I can understand that you stuck with crappy data.
Take a few minutes and read the first link in my signature about 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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply