April 17, 2012 at 8:44 am
Hi,
I have a SP that returns a list of records according to users permissions.
ALTER PROCEDURE [usp_CloudSpaceMessageList]
@UserId UNIQUEIDENTIFIER,
@UserAdmin TINYINT = 0,
@CultureId UNIQUEIDENTIFIER,
@PropertyBagId UNIQUEIDENTIFIER,
@InitRow INT = 1,
@NumRows INT = 0
AS
BEGIN
WITH Lst AS (
SELECT t0.ID, ROW_NUMBER() OVER (ORDER BY Date DESC, Message) 'Row'
FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID
WHERE t0.Published = 1 AND (t0.AlwaysOnline=1 OR (GETDATE() BETWEEN t0.OnlineStartDate AND t0.OnlineEndDate))
AND (@UserAdmin = 1 OR EXISTS (SELECT 1 FROM PLT_CategorySecurity t3 WHERE t3.UserId = @UserId AND t3.AllowEdit = 1 AND t3.CategoryID IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID )))
AND t1.CultureID = @CultureId AND LEN(t1.Title) > 0
AND EXISTS (SELECT 1 FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId) tx WHERE tx.item IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ))
AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 WHERE t4.CategoryId IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ) AND t4.UserId = @UserId)
)
SELECT t0.ID, t0.Date, t2.Title, SUBSTRING(t2.BodyHtml,1, 500) BodyHtml, t1.Row FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t2 ON t0.ID = t2.MessageID AND t2.CultureID = @CultureId
INNER JOIN lst t1 ON t0.ID = t1.ID WHERE Row BETWEEN @InitRow AND @InitRow + @NumRows -1 ORDER BY Row
END
As you can see it has the query (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ) repeated 3 times... I tried to put the query on the JOIN statement:
ALTER PROCEDURE [usp_CloudSpaceMessageList]
@UserId UNIQUEIDENTIFIER,
@UserAdmin TINYINT = 0,
@CultureId UNIQUEIDENTIFIER,
@PropertyBagId UNIQUEIDENTIFIER,
@InitRow INT = 1,
@NumRows INT = 0
AS
BEGIN
WITH Lst AS (
SELECT t0.ID, ROW_NUMBER() OVER (ORDER BY Date DESC, Message) 'Row'
FROM OPR_Messages t0
INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID
INNER JOIN PLT_EntityCategories t2 ON t2.EntityID = t0.ID
WHERE t0.Published = 1 AND (t0.AlwaysOnline=1 OR (GETDATE() BETWEEN t0.OnlineStartDate AND t0.OnlineEndDate))
AND (@UserAdmin = 1 OR EXISTS (SELECT 1 FROM PLT_CategorySecurity t3 WHERE t3.UserId = @UserId AND t3.AllowEdit = 1 AND t3.CategoryID = t2.CategoryID))
AND t1.CultureID = @CultureId AND LEN(t1.Title) > 0
AND EXISTS (SELECT 1 FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId) tx WHERE tx.item = t2.CategoryID)
AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 WHERE t4.CategoryId = t2.CategoryID AND t4.UserId = @UserId)
)
SELECT t0.ID, t0.Date, t2.Title, SUBSTRING(t2.BodyHtml,1, 500) BodyHtml, t1.Row FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t2 ON t0.ID = t2.MessageID AND t2.CultureID = @CultureId
INNER JOIN lst t1 ON t0.ID = t1.ID WHERE Row BETWEEN @InitRow AND @InitRow + @NumRows -1 ORDER BY Row
END
but since the PLT_EntityCategories can have more than 1 row per EntityID, the same OPR_Message record can show multiple times....
Is there any "simple" way for solving this?
Thanks,
Pedro
April 17, 2012 at 8:47 am
It sounds like all you need is a join to that table. Without ddl and some sample data it is pretty hard to tell.
_______________________________________________________________
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/
April 17, 2012 at 8:49 am
When looking at your query again...do you have anything resembling even medium amounts of data. Your where clause has at least 2 UDFs. The performance will degrade exponentially as the amount of data increases. Scalar UDFs are an absolute killer to performance when done RBAR in the where clause.
_______________________________________________________________
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/
April 17, 2012 at 9:10 am
In addition to the information requested by Sean, please post the code for all functions used in your queries.
April 17, 2012 at 9:43 am
Sean Lange (4/17/2012)
When looking at your query again...do you have anything resembling even medium amounts of data. Your where clause has at least 2 UDFs. The performance will degrade exponentially as the amount of data increases. Scalar UDFs are an absolute killer to performance when done RBAR in the where clause.
Just one UDF, with another UDF inside for text split, a table UFD (dbo.udf_GetPropertyBagCategories). This UDF selects a column NTEXT from a table row, parses the text and makes a text split to return IDs inside the text:
ALTER FUNCTION [dbo].[udf_GetPropertyBagCategories](@Id UNIQUEIDENTIFIER)
RETURNS @temptable TABLE (item UNIQUEIDENTIFIER)
AS
BEGIN
DECLARE @x NVARCHAR(MAX)
SELECT @x = CAST(CAST(Xml AS XML) AS NVARCHAR(MAX)) FROM PLT_PropertyBags WHERE ID =@Id
DECLARE @posI INT = CHARINDEX('<Categories><guid>', @x, 1)
IF @posI > 0
BEGIN
DECLARE @posF INT = CHARINDEX('</guid></Categories>', @x, @posI)
INSERT INTO @temptable SELECT CAST(item AS UNIQUEIDENTIFIER) FROM dbo.udf_SplitString(SUBSTRING(@x, @posI+18, @posF-@posI-18), '</guid><guid>')
END
RETURN
END
This SP shows the messages that an user can see in a certain context (@PropertyBagId).
The messages are associated with categories as are the users an context (@PropertyBagId).
A user, even having associations with categories, can tell he doesn't want to see some categories.
The tables description is has follows:
* OPR_Messages (ID, ....) is the main table with the information to list;
* OPR_MessageCultures (MessageID, CultureID, ...) is the culture data table for the OPR_Messages (they are joined by ID and restricted by @CultureId);
* PLT_CategorySecurity (UserID, CategoryID, ...) has the "mapping" "UserID" vs "CategoryID" (the categories that a user can see)
* PLT_EntityCategories (ID aka MessageID, CategoryID) has the mapping "MessageID" vs "CategoryID" (the categories a message is associated with)
* dbo.udf_GetPropertyBagCategories (item aka CategoryID) gets the categories from the context;
* CSP_UserUnsubscribedCategories (UserID, CategoryID) has the categories a user want to ignore.
The SP query can be resume to:
SELECT t0.ID, ROW_NUMBER() OVER (ORDER BY Date DESC, Message) 'Row'
FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID
WHERE
t1.CultureID = @CultureId
AND EXISTS (SELECT 1 FROM PLT_CategorySecurity t3 WHERE t3.UserId = @UserId AND t3.AllowEdit = 1 AND t3.CategoryID IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ))
AND EXISTS (SELECT 1 FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId) tx WHERE tx.item IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ))
AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 WHERE t4.CategoryId IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ) AND t4.UserId = @UserId)
All the other statements are restrictions to some columns...
Probably the 2 last restrictions can be replace by:
AND EXISTS (SELECT 1 FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId) tx INNER JOIN PLT_EntityCategories t2 ON tx.item = t2.CategoryID AND t0.ID = t2.EntityID )
AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 INNER JOIN PLT_EntityCategories t2 ON t4.CategoryId = t2.CategoryID AND t0.ID = t2.EntityID AND t4.UserId = @UserId)
But still I'm joining 2 times the same table...
Thanks,
Pedro
April 17, 2012 at 11:29 am
PiMané (4/17/2012)
Sean Lange (4/17/2012)
When looking at your query again...do you have anything resembling even medium amounts of data. Your where clause has at least 2 UDFs. The performance will degrade exponentially as the amount of data increases. Scalar UDFs are an absolute killer to performance when done RBAR in the where clause.Just one UDF, with another UDF inside for text split, a table UFD (dbo.udf_GetPropertyBagCategories). This UDF selects a column NTEXT from a table row, parses the text and makes a text split to return IDs inside the text:
ALTER FUNCTION [dbo].[udf_GetPropertyBagCategories](@Id UNIQUEIDENTIFIER)
RETURNS @temptable TABLE (item UNIQUEIDENTIFIER)
AS
BEGIN
DECLARE @x NVARCHAR(MAX)
SELECT @x = CAST(CAST(Xml AS XML) AS NVARCHAR(MAX)) FROM PLT_PropertyBags WHERE ID =@Id
DECLARE @posI INT = CHARINDEX('<Categories><guid>', @x, 1)
IF @posI > 0
BEGIN
DECLARE @posF INT = CHARINDEX('</guid></Categories>', @x, @posI)
INSERT INTO @temptable SELECT CAST(item AS UNIQUEIDENTIFIER) FROM dbo.udf_SplitString(SUBSTRING(@x, @posI+18, @posF-@posI-18), '</guid><guid>')
END
RETURN
END
This SP shows the messages that an user can see in a certain context (@PropertyBagId).
The messages are associated with categories as are the users an context (@PropertyBagId).
A user, even having associations with categories, can tell he doesn't want to see some categories.
The tables description is has follows:
* OPR_Messages (ID, ....) is the main table with the information to list;
* OPR_MessageCultures (MessageID, CultureID, ...) is the culture data table for the OPR_Messages (they are joined by ID and restricted by @CultureId);
* PLT_CategorySecurity (UserID, CategoryID, ...) has the "mapping" "UserID" vs "CategoryID" (the categories that a user can see)
* PLT_EntityCategories (ID aka MessageID, CategoryID) has the mapping "MessageID" vs "CategoryID" (the categories a message is associated with)
* dbo.udf_GetPropertyBagCategories (item aka CategoryID) gets the categories from the context;
* CSP_UserUnsubscribedCategories (UserID, CategoryID) has the categories a user want to ignore.
The SP query can be resume to:
SELECT t0.ID, ROW_NUMBER() OVER (ORDER BY Date DESC, Message) 'Row'
FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID
WHERE
t1.CultureID = @CultureId
AND EXISTS (SELECT 1 FROM PLT_CategorySecurity t3 WHERE t3.UserId = @UserId AND t3.AllowEdit = 1 AND t3.CategoryID IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ))
AND EXISTS (SELECT 1 FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId) tx WHERE tx.item IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ))
AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 WHERE t4.CategoryId IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.EntityID ) AND t4.UserId = @UserId)
All the other statements are restrictions to some columns...
Probably the 2 last restrictions can be replace by:
AND EXISTS (SELECT 1 FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId) tx INNER JOIN PLT_EntityCategories t2 ON tx.item = t2.CategoryID AND t0.ID = t2.EntityID )
AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 INNER JOIN PLT_EntityCategories t2 ON t4.CategoryId = t2.CategoryID AND t0.ID = t2.EntityID AND t4.UserId = @UserId)
But still I'm joining 2 times the same table...
Thanks,
Pedro
But that udf calls another one...
udf_SplitString
What does that one look like? Looping to parse or is it set based?
And if at all possible change your ntext to varchar(max).
Now of course, we are still missing ddl and sample data. That means create table statements, insert statements and desired output based on your sample data.
_______________________________________________________________
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/
April 17, 2012 at 3:49 pm
Hi,
Sorry for the late reply but I'm not at the office. Tomorrow I'll send the tables' structure and some sample data.
Changing data types is not very easy since this is a database that runs on our customers servers and our actual minimum requirement for SQL Server is 2000.... I'm trying to convince our marketing department to change it to SQL 2008 (XML, CLR and FILESTREAM among other stuff) but some customers bought the SQL 2000 Enterprise edition and it's not easy to convince them to change to SQL 2008 due to the financial implications... But still we can have 2 database scripts, harder to manage but better performance for SQL 2005 and higher.
Thanks,
Pedro
April 18, 2012 at 7:45 am
Hi,
I attached 2 files, one with the tables structure and UDFs and the other with some sample data.
Assuming there is a Users, a Categories and a Cultures table that aren't directly used in the query, the structure is the one on the file.
I already changed the NTEXT to NVARCHAR(MAX) in this script.
We can't transform the propertybag table in "regular" columns since its a serialized XML from a class in C# but I'm thinking, since the class is ours and has categories, to create a trigger on INSERT / UPDATE of propertybag table so it inserts on a PLT_PropertyBagCategories so the UDF hasn't to be used on the query, just on the trigger (the property bag table hasn't that much inserts nor updates).
The [udf_GetPropertyBagCategories] has the CAST to XML and back to NVARCHAR to remove all the "special" characters the C# serialization inserts.
USE MessageTest
GO
DECLARE @UserId UNIQUEIDENTIFIER, @CultureId UNIQUEIDENTIFIER, @PropertyBagId UNIQUEIDENTIFIER, @InitRow INT, @NumRows INT, @UserAdmin INT = 0
SET @UserId = '9B04AB10-4EFD-49BC-9A97-E0B4DEE24695'
SET @CultureId = 'D0BB4A83-03A8-48DC-AD53-0BD66E48675D'
SET @PropertyBagId = '521FA083-E40A-4E62-AD7E-A4410185C9D4'
SET @InitRow = 1;
SET @NumRows = 10000;
SELECT t0.ID
FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID
WHERE
t0.Published = 1
AND (t0.AlwaysOnline=1 OR (GETDATE() BETWEEN t0.OnlineStartDate AND t0.OnlineEndDate))
AND t1.CultureID = @CultureId AND LEN(t1.Title) > 0
AND (@UserAdmin = 1 OR EXISTS (SELECT 1 FROM PLT_CategorySecurity t3 WHERE t3.UserId = @UserId AND t3.AllowEdit = 1 AND t3.CategoryID IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.ID )))
AND EXISTS (SELECT 1 FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId) tx WHERE tx.item IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.ID ))
AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 WHERE t4.CategoryId IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.ID ) AND t4.UserId = @UserId)
This what I need to execute, without the CTE.
This query has also a slight problem with restrictions... Like it is it returns just one ID but it should return 3 messages IDs.
There are 3 categories and the user has access to all.
There are 4 messages, 1 associated with 3 categories, 1 associated with category 1 and the other to category 0 and another with category 1 and 0.
The property bag lists messages from all 3 categories, so it should return all 4 but since the users doesn't want to see category 1 messages (table CSP_UserUnsubscribedCategories) he should see 3 (the message with category 0, the message with category 0 and 1 and the message with 3 categories). The last statement AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 WHERE t4.CategoryId IN (SELECT t2.CategoryID FROM PLT_EntityCategories t2 WHERE t0.ID = t2.ID ) AND t4.UserId = @UserId) is removing the messages that have category 1 associated regardless they have other categories (how can I solve this problem?!).
I used another query to get the desired results:
SELECT t0.ID, t2.CategoryID
FROM OPR_Messages t0
INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID
INNER JOIN PLT_EntityCategories t2 ON t2.ID = t0.ID AND t2.CategoryID IN (SELECT item FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId))
INNER JOIN PLT_CategorySecurity t3 ON t2.CategoryID = t3.CategoryID AND t3.UserID = @UserId AND t3.AllowEdit = 1
WHERE t2.CategoryID NOT IN (SELECT t4.CategoryID FROM CSP_UserUnsubscribedCategories t4 WHERE t4.UserID = @UserId)
Is it better to use this query as a sub query with DISTINCT t0.ID to get all the IDs and use it on the CTE?
The two queries I managed to build are (but the 2nd one has the restriction problem):
DECLARE @UserId UNIQUEIDENTIFIER, @CultureId UNIQUEIDENTIFIER, @PropertyBagId UNIQUEIDENTIFIER, @InitRow INT, @NumRows INT, @UserAdmin INT = 0
SET @UserId = '9B04AB10-4EFD-49BC-9A97-E0B4DEE24695'
SET @CultureId = 'D0BB4A83-03A8-48DC-AD53-0BD66E48675D'
SET @PropertyBagId = '521FA083-E40A-4E62-AD7E-A4410185C9D4'
SET @InitRow = 1;
SET @NumRows = 10000;
WITH Lst AS (
SELECT t0.ID, ROW_NUMBER() OVER (ORDER BY Date DESC, Title) 'Row'
FROM (
SELECT DISTINCT t0.ID, t0.Date
FROM OPR_Messages t0
INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID AND t1.CultureID = @CultureId AND LEN(t1.Title) > 0
INNER JOIN PLT_EntityCategories t2 ON t2.ID = t0.ID AND t2.CategoryID IN (SELECT item FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId))
INNER JOIN PLT_CategorySecurity t3 ON t2.CategoryID = t3.CategoryID AND t3.UserID = @UserId AND t3.AllowEdit = 1
WHERE t2.CategoryID NOT IN (SELECT t4.CategoryID FROM CSP_UserUnsubscribedCategories t4 WHERE t4.UserID = @UserId)
) t0
INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID AND t1.CultureID = @CultureId AND LEN(t1.Title) > 0
)
SELECT t0.ID, t0.Date, t2.Title, SUBSTRING(t2.BodyHtml,1, 500) BodyHtml, t1.Row FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t2 ON t0.ID = t2.MessageID AND t2.CultureID = @CultureId
INNER JOIN lst t1 ON t0.ID = t1.ID WHERE Row BETWEEN @InitRow AND @InitRow + @NumRows -1 ORDER BY Row;
WITH Lst AS (
SELECT t0.ID, ROW_NUMBER() OVER (ORDER BY Date DESC, Title) 'Row'
FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t1 ON t0.ID = t1.MessageID
WHERE t0.Published = 1 AND (t0.AlwaysOnline=1 OR (GETDATE() BETWEEN t0.OnlineStartDate AND t0.OnlineEndDate))
AND (@UserAdmin = 1 OR EXISTS (SELECT 1 FROM PLT_CategorySecurity t3 INNER JOIN PLT_EntityCategories t2 ON t3.CategoryID = t2.CategoryID WHERE t3.UserId = @UserId AND t3.AllowEdit = 1 AND t0.ID = t2.ID))
AND t1.CultureID = @CultureId AND LEN(t1.Title) > 0
AND EXISTS (SELECT 1 FROM dbo.udf_GetPropertyBagCategories(@PropertyBagId) tx INNER JOIN PLT_EntityCategories t2 ON tx.item = t2.CategoryID AND t0.ID = t2.ID)
AND NOT EXISTS (SELECT 1 FROM CSP_UserUnsubscribedCategories t4 INNER JOIN PLT_EntityCategories t2 ON t4.CategoryId = t2.CategoryID AND t0.ID = t2.ID AND t4.UserId = @UserId)
)
SELECT t0.ID, t0.Date, t2.Title, SUBSTRING(t2.BodyHtml,1, 500) BodyHtml, t1.Row FROM OPR_Messages t0 INNER JOIN OPR_MessageCultures t2 ON t0.ID = t2.MessageID AND t2.CultureID = @CultureId
INNER JOIN lst t1 ON t0.ID = t1.ID WHERE Row BETWEEN @InitRow AND @InitRow + @NumRows -1 ORDER BY Row
Thanks in advance and sorry for the long text...
April 18, 2012 at 8:10 am
I am kinda swamped this morning but I did take a quick look. I would HIGHLY recommend you take a look at the link in my signature about a string splitter and replace the one you have.
_______________________________________________________________
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 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply