# Insert a page definition in the right order

I have a table SlideList that defines all available slides that can be put into a PowerPoint presentation. I have a table ReportList that defines which slides go into a presentation for a company and the order they're to be inserted.

I've been maintaining ReportList by hand (yes, that's a nightmare and needs to be fixed, but it's not the point of this post), so upon initial creation of a report, I've left gaps of 10 in the SlideOrder so it's easy to insert additional slides without having to renumber everything by hand.

I've written the following T-SQL to identify the SlideOrder value to use when inserting a new slide immediately after an existing slide for every report that has that slide in it.

For example: I have existing slide Foo, and I've created a new slide Bar. I need to insert Bar as the next slide after Foo in every report that contains Foo. Some reports may have Foo as slide #50, others as slide #302 - this code will figure that out, split the gap to the next slide, and return that value as the SlideOrder for Bar. If there is no gap (Foo is SlideOrder #50, and Baz exists at SlideOrder #51) it will simply print a message and I'll have to adjust that one by hand.

Here is my T-SQL code. While it's functional, I'm looking for improvements to this code and to learn more T-SQL. If you're feeling really generous and want to rewrite it to update all the SlideOrder values > Foo so that Baz can be inserted and there are no gaps in SlideOrder, go for it! I'll be very grateful, but that's not what I'm after at the moment. (Note that this code simply prints out the SlideOrder of Foo, the SlideOrder that Bar will go in, and the SlideOrder of what follows - it doesn't do the actual insert.)

USE DevDB
GO

DECLARE @SlideToFollowID as int
SELECT @SlideToFollowID = SlideID
FROM ReportList RL inner join SlideList SL on RL.SlideID = SL.ID
WHERE SlideName = @SlideToFollow

DECLARE @CurrentClinicID as int
SELECT @CurrentClinicID = min(ClinicID)
FROM ReportList
WHERE SlideID = @SlideToFollowID

WHILE @CurrentClinicID is not null
BEGIN
DECLARE @Slide1QOrder as int
SELECT @Slide1QOrder = SlideOrder
FROM ReportList
WHERE SlideID = @SlideToFollowID
and ClinicID = @CurrentClinicID

DECLARE @NextSlideOrder as int
SELECT @NextSlideOrder = min(SlideOrder)
FROM ReportList
WHERE ClinicID = @CurrentClinicID
AND SlideOrder > @Slide1QOrder

IF @NextSlideOrder = @Slide1QOrder + 1
PRINT N'ClinicID: ' + Cast(@currentClinicID as nvarchar) + ' does not have room to insert a new slide after order: ' + cast(@Slide1QOrder as nvarchar);
ELSE
BEGIN
DECLARE @NewSlideOrder as int
SELECT @NewSlideOrder = @Slide1QOrder + (@NextSlideOrder - @Slide1QOrder)/2
PRINT N'ClinicID: ' + Cast(@CurrentClinicID as nvarchar) + '  ' + @SlideToFollow + ' slide order: ' + Cast(@Slide1QOrder as nvarchar) + ' New Slide Order: ' + Cast(@NewSlideOrder as nvarchar) + '  Next Slide Order: ' + Cast(@NextSlideOrder as nvarchar);
END

SELECT @CurrentClinicID = min(ClinicID)
FROM ReportList
WHERE SlideID = @SlideToFollowID
AND ClinicID > @CurrentClinicID
END


SlideList:

CREATE TABLE [dbo].[SlideList](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SlideName] [nvarchar](50) NULL,
[SlideDesc] [nvarchar](255) NULL,
[ExcelWksht] [nvarchar](255) NULL
GO


ReportList:

CREATE TABLE [dbo].[ReportList](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClinicID] [int] NULL,
[SlideID] [int] NULL,
[SlideOrder] [int] NULL
GO

• One thing I just discovered - I should probably be using SET @Local = (SELECT...) instead of just SELECT @Local = ... – FreeMan Jun 12 '18 at 14:49
• So confusing. I bet row_number() could be your friend. Or a recursive CTE. You should post a better explanation of what you need to do with some data and desired output on dba.stackexchange – paparazzo Jun 12 '18 at 15:05
• @paparazzo - can you be a bit more explicit? What's confusing you? In what way would Row_number be my friend? – FreeMan Jun 12 '18 at 15:07
• Follow my suggestion to post on dba or not. – paparazzo Jun 12 '18 at 15:10
• Ah. Somehow, your edit hadn't appeared when I posted my response. If I don't get satisfaction here in the next couple of days, I'll certainly move to Database Administrators – FreeMan Jun 12 '18 at 15:14