July 9, 2013 at 10:29 am
Hi,
I have one requirement and need help.
I have a table:
CREATE TABLE MiscItems(ID INT, Description VARCHAR(50), DisplayOrder SMALLINT)
INSERT INTO MiscItems(ID,Description,DisplayOrder)
SELECT 100, 'A1',1
UNION ALL
SELECT 101,'A2',2
UNION ALL
SELECT 102,'A3',3
UNION ALL
SELECT 104,'A4',4
UNION ALL
SELECT 105,'A5', 5
UNION ALL
SELECT 106,'A6',6
SELECT * FROM MiscItems
DROP TABLE MiscItems
I will display in UI: Description & Displayorder and both are editable in UI.
My requirement is User can modify DisplayOrder of his interest and clicks on save, then accordingly the display order should be saved in table.
User can change multiple items at once and accordingly data should be re arranged and saved in table.
Display order will not be a zero & non negative value.
1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,6
2) Now, Change the existing Order of A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6
I have below proc for updatimg display order:
CREATE PROCEDURE proc_Disporder
@ID INT,
@OldDisplayOrder INT,
@NewDisplayOrder INT
AS
BEGIN
IF(@OldDisplayOrder >= @NewDisplayOrder) OR (@OldDisplayOrder = 0)
BEGIN
UPDATE miscitem
SET DisplayOrder = DisplayOrder + (CASE WHEN @OldDisplayOrder = @NewDisplayOrder THEN 1 ELSE 2 END)
FROM MiscItems miscitem
WHERE ID <> @ID
AND miscitem.DisplayOrder >= @NewDisplayOrder
AND miscitem.DisplayOrder > 0
END
IF(@OldDisplayOrder < @NewDisplayOrder) AND(@OldDisplayOrder <> 0)
BEGIN
UPDATE miscitem
SET DisplayOrder = (CASE (DisplayOrder - 1) WHEN 0 THEN DisplayOrder ELSE (DisplayOrder - 1) END)
FROM MiscItems miscitem
WHERE ID <> @ID
AND miscitem.DisplayOrder <= @NewDisplayOrder AND miscitem.DisplayOrder >= @NewDisplayOrder - 1
AND miscitem.DisplayOrder > 0
END
DECLARE @MiscItems TABLE(DisplayOrder INT IDENTITY(1,1),ID INT)
INSERT @MiscItems
SELECT ID FROM MiscItems WHERE DisplayOrder > 0ORDER BY DisplayOrder
UPDATE src
SET src.DisplayOrder = mi.DisplayOrder
FROM MiscItems src
INNER JOIN @MiscItems mi ON src.ID = mi.ID
END
Its a logic error..,sorry for the confusion..
My data in table is: A1 - 1, A2 - 2, A3 - 3, A4 - 4, A5- 5, A6- 6
1. User updates : A3 to 1, A4 to 2, A5 to 3, A6 to 4
Expected : A3-1,A4-2,A5-3,A6-4,A1-5,A2-6
2. Existing : A3-1,A4-2,A5-3,A6-4,A1-5,A2-6
User updates : A3 to 3, A4 to 4, A5 to 5, A6 to 6
Expected : A1 - 1, A2 - 2, A3 - 3, A4 - 4, A5- 5, A6- 6
July 9, 2013 at 10:40 am
deepkt (7/9/2013)
Hi,I have one requirement and need help.
I have a table:
CREATE TABLE MiscItems(ID INT, Description VARCHAR(50), DisplayOrder SMALLINT)
INSERT INTO MiscItems(ID,Description,DisplayOrder)
SELECT 100, 'A1',1
UNION ALL
SELECT 101,'A2',2
UNION ALL
SELECT 102,'A3',3
UNION ALL
SELECT 104,'A4',4
UNION ALL
SELECT 105,'A5', 5
UNION ALL
SELECT 106,'A6',6
SELECT * FROM MiscItems
DROP TABLE MiscItems
I will display in UI: Description & Displayorder and both are editable in UI.
My requirement is User can modify DisplayOrder of his interest and clicks on save, then accordingly the display order should be saved in table.
User can change multiple items at once and accordingly data should be re arranged and saved in table.
Display order will not be a zero & non negative value.
please help..
Not entirely sure what you are asking here. Part of your question sounds like you want to use a parameter as your sort order. This is certainly feasible. The other part makes it sound like you want store this sort order or something??? Keep in mind that the ONLY way to ensure order of rows from a table is to add an ORDER BY to your query.
_______________________________________________________________
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/
July 9, 2013 at 10:40 am
to guarantee a specific order, you must have an explicit ORDER BY as part of your command.
SELECT
* FROM MiscItems ORDER BY DisplayOrder
Lowell
July 9, 2013 at 10:48 am
1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,6
2) Now, Change the existing Order of A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6
July 9, 2013 at 10:53 am
deepkt (7/9/2013)
1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,62) Now, Change the existing Order of A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6
yep, the answer didn't change, i think.
ORDER BY DisplayOrder
Lowell
July 9, 2013 at 10:55 am
So what are you after here? Copying and pasting a few lines from the original post isn't of much help, since we've already read it.
If you're trying to control the order it appears in your application GUI, do what Lowell said in your query to pull the data and add an ORDER BY clause.
If you're after something else, you'll need to explain what you want to accomplish.
July 9, 2013 at 11:20 am
I have below proc: for updating display order:
CREATE PROCEDURE proc_Disporder
@ID INT,
@OldDisplayOrder INT,
@NewDisplayOrder INT
AS
BEGIN
IF(@OldDisplayOrder >= @NewDisplayOrder) OR (@OldDisplayOrder = 0)
BEGIN
UPDATE miscitem
SET DisplayOrder = DisplayOrder + (CASE WHEN @OldDisplayOrder = @NewDisplayOrder THEN 1 ELSE 2 END)
FROM MiscItems miscitem
WHERE ID <> @ID
AND miscitem.DisplayOrder >= @NewDisplayOrder
AND miscitem.DisplayOrder > 0
END
IF(@OldDisplayOrder < @NewDisplayOrder) AND(@OldDisplayOrder <> 0)
BEGIN
UPDATE miscitem
SET DisplayOrder = (CASE (DisplayOrder - 1) WHEN 0 THEN DisplayOrder ELSE (DisplayOrder - 1) END)
FROM MiscItems miscitem
WHERE ID <> @ID
AND miscitem.DisplayOrder <= @NewDisplayOrder AND miscitem.DisplayOrder >= @NewDisplayOrder - 1
AND miscitem.DisplayOrder > 0
END
DECLARE @MiscItems TABLE(DisplayOrder INT IDENTITY(1,1),ID INT)
INSERT @MiscItems
SELECT ID FROM MiscItems WHERE DisplayOrder > 0ORDER BY DisplayOrder
UPDATE src
SET src.DisplayOrder = mi.DisplayOrder
FROM MiscItems src
INNER JOIN @MiscItems mi ON src.ID = mi.ID
END
one of other scenario is failing.. plz help..
July 9, 2013 at 12:03 pm
deepkt (7/9/2013)
one of other scenario is failing.. plz help..
What is failing? Are you getting an error message? Or is it a logic error? I am still confused as to what you are trying to do here.
_______________________________________________________________
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/
July 9, 2013 at 12:09 pm
Sean Lange (7/9/2013)
deepkt (7/9/2013)
one of other scenario is failing.. plz help..What is failing? Are you getting an error message? Or is it a logic error? I am still confused as to what you are trying to do here.
Whew! I'm glad I'm not the only one. 😉
July 9, 2013 at 12:14 pm
1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,6
2) Now, Change the existing Order of A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6
Your expected results do not make sense. In your example after making changes, you have A5 and A6 repeating with two different order values. What happened to A1 and A4?
Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
If you are trying to allow users to customize their sort order, you should add a user id field to your table.
Your proc to update the table should simply be an update statement setting the new order values that the user provided from the GUI. The front end application should do presentation layer validation to ensure the user input a number in correct format. Let them enter the numbers for their sort order and then save that list. It looks like you are over complicating this.
July 10, 2013 at 3:13 am
Its a logic error..,sorry for the confusion..
My data in table is: A1 - 1, A2 - 2, A3 - 3, A4 - 4, A5- 5, A6- 6
1. User updates : A3 to 1, A4 to 2, A5 to 3, A6 to 4
Expected : A3-1,A4-2,A5-3,A6-4,A1-5,A2-6
2. Existing : A3-1,A4-2,A5-3,A6-4,A1-5,A2-6
User updates : A3 to 3, A4 to 4, A5 to 5, A6 to 6
Expected : A1 - 1, A2 - 2, A3 - 3, A4 - 4, A5- 5, A6- 6
July 10, 2013 at 4:01 am
Change your stored procedure to work with input in a table-valued parameter which holds the new sort order for all values. Or at least all values for which the sort order change. The stored procedure then becomes a straight update. The actual logic should be in the UI.
I don't know exactly how your UI works, but I assume that you present the data in a grid or list box, and the user can then move rows up and down in the control. When the user presses save, you compute the new numbers and then populate the TVP with the data. If you saved the old sort numbers, you can gain some efficiency by comparing with the old numbers and only populate the TVP with the change. But if the table only has some 50 rows, that is not really worth the complexity.
If you have never worked with table-valued parameters before, see this article on my web site: http://www.sommarskog.se/arrays-in-sql-2008.html.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 10, 2013 at 4:30 am
Thanks for the reply.
In UI I am binding data to a grid.. all the logic will be processed using stored proc and from back end.
In UI user will modify Displayorder of his interest and it will be passed to SP and need to process it.
If multiple updates are there then multiple times proc will be called. I am fine with Multiple calls to database.
Thanks..
July 10, 2013 at 5:33 am
deepkt (7/10/2013)
Thanks for the reply.In UI I am binding data to a grid.. all the logic will be processed using stored proc and from back end.
In UI user will modify Displayorder of his interest and it will be passed to SP and need to process it.
If multiple updates are there then multiple times proc will be called. I am fine with Multiple calls to database.
And that's where your problems start. I suggested using a TVP for two reasons:
With the solution I suggest, the stored procedure is dirt simple: it's a single update from the TVP. Well, maybe a single MERGE statement as the user may add or delete rows as well. You need to some more work in the client - but not a lot. I don't know anything about grid-binding, but I assume that you can bind a DataTable to the grid, and you can pass a DataTable to a TVP.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 10, 2013 at 5:55 am
Erland is right - you only need one round-trip to the database instead of one for each row.
In .NET, when you bind data to a grid, it's usually done in the code-behind and is very straightforward. This is where you use a SQL statement and include the ORDER BY clause. The user is then free to edit the fields. When the user clicks save, do the client-side validation then do your validation in the code-behind. Create and populate your table-value parameter and then call your procedure.
I don't know which development environment you're using, but I'd imagine that there would be something similar.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply