Solved

Sorting table field with sproc

Posted on 2008-10-06
6
148 Views
Last Modified: 2012-05-05
Hi there!

In the sproc below:

---------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[insProperPosition_sproc]
@itemPosition int,
@Field1 int,
@Field2 int
AS
IF @ItemPosition > 0
      
    UPDATE testTable
      SET ItemPosition = ItemPosition + 1
      WHERE ItemPosition >= @ItemPosition
---------------------------------------------------------------------

I would like to insert a restriction, for instance, I want to re-numerate the itemPositions of all the testTable records in with the Field2 equals the number 3 (and only these records, sure), and the renumeration should be ORDERed (ASC) by the (PK) Field1...

Could you please show to me how to adapt the procedure given to satisfy this restriction?  Thanks!
0
Comment
Question by:fskilnik
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22650282
Can you give some data example of what you have and what you want to achieve?

What is with the other two parameters of the proc?
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22650441
Hi fskilnik,

your execution not like a good idea. What not seach other solution to your problem? (what is your problem? really)

Nevertheless, use a intermediate table, like this...

Good luck!

DECLARE @start int -- start incremental value

               -- you must be sure all future f1 not are in use!!!
 

-- I suppuse f1 are your PK

-- Then
 

BEGIN TRANSACTION -- is needed
 

DECLARE @vt TABLE (

   ii int identity(0,1),

   f1 int

)

INSERT INTO @vt ( f1 )

SELECT f1

FROM YourTable

WHERE Your_Condition

ORDER BY Your_Order
 

UPDATE YourTable

SET f1 = @start + ii

FROM YourTable a

JOIN @vt b

ON a.f1 = b.f1
 

COMMIT TRANSACTION

Open in new window

0
 

Author Comment

by:fskilnik
ID: 22690242
Hi Zberteoc and jose_juan,

Sorry for the (week-long) delay.

>Can you give some data example of what you have and what you want to achieve?
>What is with the other two parameters of the proc?

>>your execution not like a good idea.
>> what is your problem? really

You are both right, my question was poorly created. The 2 parameters included (and not used) are there just to ilustrate the ones that would be needed to satisfy the condition explained after the code, that is:
 
... I want to re-numerate the itemPositions of all the testTable records in with the Field2 equals the number 3 (and only these records, sure), and the renumeration should be ORDERed (ASC) by the (PK) Field1...

I guess jose_juan´s code may be what I need, but I have no experience dealing with temporary (intermediate) tables, therefore I will give you (both) the REAL fields and ask my question in an understandable fashion...

01. My  TestTable is the created by the snippet attached.
02. Let us consider the following records already present at the TestTable:

tbTestID //  Material ID  // PageNumber   // PagContent     <----- Name of Fields

Value of the corresponding fields:

1      1      1      abc
2      1      2      def
3      2      1      ghi
4      2      2      jkl
5      1      3      mno
6      1      5      stu
7      1      4      pqr
9      2      3      vxz

Now what I need?  A sproc ("based" on the sproc given, or not...)  say   "insProperPosition(i, j)" such that
i is the (integer) that asks for the (PageNumber) position to be included (more below) and j is the MaterialID that should be considered. The sproc should do as below:

Example: if I run  insProperPosition(2, 1) that means that I want the store procedure to "open" the PageNumber = 2 of the MaterialID = 1 in the sense that the table above given should now be:

           MatID      PageNumb
1      1      1      abc
2      1      THREE      def
3      2      1      ghi
4      2      2      jkl
5      1      FOUR      mno
6      1      SIX      stu
7      1      FIVE      pqr
9      2      3      vxz

In other words, the MaterialID= 1 and PageNumber =2 has already no records, because I´ll be able to insert it now without interrupting the enumeration. Please note that the MaterialID =2 records were not altered and that the MaterialID = 1 records where NOT altered when int < 2 was considered (sure, just the PageNumber = 1 therefore) and ALL MaterialID = 1 records with PageNumber > = 2 where 1 units ahead added, I mean, PageNumber 2 is now PageNumber 3 , PageNumber 3 (previous) is now PageNumber 4, etc...

I hope I got understood!
Thanks a lot for your time and expertise, fskilnik.







SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[testTable](

	[tbTestID] [int] IDENTITY(1,1) NOT NULL,

	[MaterialID] [int] NOT NULL,

	[PageNumber] [int] NOT NULL,

	[PgContent] [nvarchar](200) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL

) ON [PRIMARY]

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:fskilnik
ID: 22690290
Of course I put THREE where should be 3 etc, it´s just for the sake of emphasizing where and how the table was altered.

(Of course both parameters i and j are always "reasonable"... this sproc is for myself only, therefore I will not need error-routines... for instance, if I put i = 0, I would like the sproc not to run... just that...)

0
 
LVL 5

Accepted Solution

by:
jose_juan earned 500 total points
ID: 22709181
Hi fskilnik,

uhm... I think your problem have trivial solution, if your initial data is ordered then only updating PageNumbers to get displacement is needed.

See code (complete example code).

Regards.

GO

CREATE TABLE testTable (

        tbTestID int IDENTITY(1,1) NOT NULL primary key, -- Use PK always!

        MaterialID int NOT NULL,

        PageNumber int NOT NULL,

        PgContent nvarchar(200)

)

GO

-- Use unique keys to set restriction! prevent errors!

CREATE UNIQUE INDEX UK_testTable ON testTable ( MaterialID ASC, PageNumber ASC )

GO
 

-- Example data

INSERT INTO testTable ( MaterialID, PageNumber, PgContent ) VALUES ( 1, 1, 'abc' )

INSERT INTO testTable ( MaterialID, PageNumber, PgContent ) VALUES ( 1, 2, 'def' )

INSERT INTO testTable ( MaterialID, PageNumber, PgContent ) VALUES ( 2, 1, 'ghi' )

INSERT INTO testTable ( MaterialID, PageNumber, PgContent ) VALUES ( 2, 2, 'jkl' )

INSERT INTO testTable ( MaterialID, PageNumber, PgContent ) VALUES ( 1, 3, 'mno' )

INSERT INTO testTable ( MaterialID, PageNumber, PgContent ) VALUES ( 1, 5, 'stu' )

INSERT INTO testTable ( MaterialID, PageNumber, PgContent ) VALUES ( 1, 4, 'pqr' )

INSERT INTO testTable ( MaterialID, PageNumber, PgContent ) VALUES ( 2, 3, 'vxz' )
 

GO

SELECT * FROM testTable ORDER BY MaterialID, PageNumber
 

GO

CREATE PROCEDURE sp_insProperPosition

	@pN int,

	@mID int

AS

	SET NOCOUNT ON
 

	UPDATE	testTable

	SET	PageNumber	= PageNumber + 1

	WHERE	MaterialID	= @mID

	AND	PageNumber	>= @pN

GO
 

EXEC sp_insProperPosition 2, 1
 

GO

SELECT * FROM testTable ORDER BY MaterialID, PageNumber
 

GO
 

DROP PROCEDURE sp_insProperPosition

DROP TABLE testTable

Open in new window

0
 

Author Comment

by:fskilnik
ID: 22709888
Hi jose_juan,

Thanks for the really complete solution. It´s perfect and, yes, pretty simple (after you made it, for sure)!!

I hope to see you in my future difficulties.    :)

Best Regards,
fskilnik.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now