CREATE FUNCTION GetId(@TableName sysname)
RETURNS int
AS
BEGIN
	DECLARE 
		@Id int,
		@Sql nvarchar(1024) = 'SELECT IdOUT = TOP(1) ' + @TableName + 'Id FROM ' + @TableName + ' ORDER BY ' + @TableName + 'Id DESC',
		@ParmDefinition nvarchar(256) = 'IdOUT int OUTPUT'
		
	EXEC sp_executesql
			@Sql, 
			@ParmDefinition, 
			@IdOUT = @Id OUTPUT
			
	RETURN @Id
END

SELECT dbo.Getid('Contact')

Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.

USE Test

CREATE TABLE Contact(
	ContactId int not null IDENTITY(1,1),
	Name varchar(64) not null
	)

CREATE TABLE Phone(
	PhoneId int not null IDENTITY(1,1),
	Phone varchar(32) not null,
	ContactId int not null,
	SortOrder int not null
	)
GO

ALTER TABLE Contact ADD CONSTRAINT
	PK_Contact PRIMARY KEY CLUSTERED (ContactId) ON [PRIMARY]
	
ALTER TABLE Phone ADD CONSTRAINT
	PK_Phone PRIMARY KEY CLUSTERED (PhoneId) ON [PRIMARY]
	
ALTER TABLE dbo.Phone ADD CONSTRAINT
	IX_Phone UNIQUE NONCLUSTERED 
	(
	ContactId,
	SortOrder
	)
GO
	
ALTER TABLE dbo.Phone ADD CONSTRAINT
	FK_Phone_Contact FOREIGN KEY
	(ContactId) REFERENCES dbo.Contact(ContactId)
		ON UPDATE  NO ACTION 
		ON DELETE  CASCADE 

GO
	

• Strictly consistent groups - under each contact parent should be a consistent zero based chaine of sort order values.


• If there are 5 phones for this contact, there shouldn't be SortOrder = 7 (or SortOrder = 5 as it's zero based and max allowed is 4).


• I have already set up the unique key for the groups so there won't be duplicates.


• There are few more, I don't remember or I am too lazy now...

quote:

---

Originally posted by weitzhandler

Well, why don't tell you my exact problem:
.............
.............
Shimmy

---

USE [Bss]
GO
/****** Object:  StoredProcedure [dbo].[sp_sortorder]    Script Date: 04/17/2009 06:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================
-- Description:	Handles the SortOrder column logic.
-- For tables that provide it.
-- @TableName sysname, The table name you want to apply the logic on.
-- @IdColumn varchar(32) = null, Specify the ID column name if other than @TableName + 'ID'
-- @RowId, The ID of the row you want to apply the logic on.
-- @SortOrderColumn varchar(32) = 'SortOrder', The name of the column that exposes the SortOrder.
-- @SortOrderValue int = -1, The new SortOrder value - ifargument is -1, will change to max value in the SortOrder.
-- @ParentIdColumn varchar(32) = null, If you want to limit the sorting to a sub-group, pass the parent-table's name.
-- ================================================
CREATE PROCEDURE [dbo].[sp_sortorder]
(
	@TableName sysname, 
	@IdColumn varchar(32) = null,
	@RowId int,
	@SortOrderColumn varchar(32) = 'SortOrder',
    @SortOrderValue int = -1,
	@ParentIdColumn varchar(32) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE 
	@CurrentSortOrder int, -- Current SortOrder value for the specified row.
	@ParentId int, -- The parent ID to limit the group with.
	@Group varchar(256) = '', -- The sql syntax that will limit the group.
	@Direction bit
	
	DECLARE @SortOrderData TABLE (RowId int not null, SortOrder int not null)

SET @IdColumn = ISNULL(@IdColumn, @TableName + 'ID')

IF NOT ISNULL(@ParentIdColumn, '') = ''
	BEGIN
		DECLARE
			@Sql nvarchar(1024) = N'SELECT @CurrentSortOrderOUT = ' + @SortOrderColumn + ', @ParentIdOUT = ' + @ParentIdColumn + ' FROM ' + @TableName + ' WHERE ' + @IdColumn + ' = ' + ISNULL(CAST(@RowId AS varchar(10)), '0'),
			@ParmDefinition nvarchar(256) = '@CurrentSortOrderOUT int OUTPUT, @ParentIdOUT int OUTPUT'
		EXECUTE sp_executesql 
			@Sql, 
			@ParmDefinition, 
			@CurrentSortOrderOUT = @CurrentSortOrder OUTPUT,
			@ParentIdOUT = @ParentId OUTPUT
	END

IF @SortOrderValue >= 0 AND @SortOrderValue = @CurrentSortOrder PRINT '!!!!!!! R E T U R N E D !!!!!!!!' --RETURN

SET @Group = 
	CASE WHEN ISNULL(@ParentIdColumn, '') = ''
		THEN ''
		ELSE ' WHERE ' + @ParentIdColumn + ' = ' + ISNULL(CAST(@ParentId AS varchar(10)), '0')
	END

--Select the entire group including our row.
INSERT INTO @SortOrderData(RowId, SortOrder)
EXEC (N'SELECT ' + @IdColumn + ', ' + @SortOrderColumn + ' FROM ' + @TableName + @Group + ' ORDER BY ' + @SortOrderColumn)

--Set current row's data:
SELECT @CurrentSortOrder = SortOrder FROM @SortOrderData WHERE RowId = @RowId

DECLARE @Max int = (SELECT TOP(1) SortOrder FROM @SortOrderData ORDER BY SortOrder DESC)

IF @SortOrderValue < 0
	SET @SortOrderValue = @Max 
ELSE IF @SortOrderValue > @Max
	BEGIN
		SET @SortOrderValue = @Max
	END
IF @SortOrderValue = @CurrentSortOrder PRINT '!!!!!!! R E T U R N E D 2!!!!!!!!' --RETURN
	
	
SET @Direction = 
	CASE 
		WHEN @CurrentSortOrder < @SortOrderValue THEN 1 --1 means that the current is smaller and needs to go up (i.e. 5 to 7).
		ELSE 0 --Means that it wants to move from a high SortOrder value to low (i.e. 7 to 5)
	END

-----------------------------------	
print 'DIRECTION:' print @direction
print 'Current sort order:'	print @currentsortorder
PRINT 'SORT ORDER VALUE:' PRINT @SORTORDERVALUE
-----------------------------------	

--Delete from the table all the rows we don't need for the sorting including our row.
IF @Direction = 1 DELETE FROM @SortOrderData WHERE SortOrder <= @CurrentSortOrder OR SortOrder > @SortOrderValue
ELSE DELETE FROM @SortOrderData WHERE SortOrder >= @CurrentSortOrder OR SortOrder < @SortOrderValue 

	BEGIN TRY
		BEGIN TRANSACTION
			--Temporarily change this row's SortOrder to -1 to avoid conflicts.
			IF @Group <> '' SET @Group = REPLACE(@Group, 'WHERE', 'AND')
			EXEC('UPDATE ' + @TableName + ' SET ' + @SortOrderColumn + ' = - 1 WHERE ' + @IdColumn + ' = ' + @RowId + @Group)
		
			DECLARE 
				@Id int, 
				@SortOrder int, 
				@Dir CHAR(3) = CASE WHEN @Direction = 1 THEN ' - ' ELSE ' + ' END
			
			print 'FETCH============================================='
			DECLARE SortOrder_Cursor CURSOR FOR
						SELECT * FROM @SortOrderData
						ORDER BY CASE WHEN @Direction = 1 THEN SortOrder ELSE -SortOrder END
																				
			OPEN SortOrder_Cursor
			FETCH NEXT FROM SortOrder_Cursor INTO @Id, @SortOrder
			WHILE @@FETCH_STATUS = 0
				BEGIN
					PRINT 'current fetch: ' print @id print @SortOrder
					EXEC('UPDATE ' + @TableName + ' SET ' + @SortOrderColumn + ' = ' + @SortOrder + @Dir + '1 WHERE ' + @IdColumn + ' = ' + @Id)
					FETCH NEXT FROM SortOrder_Cursor INTO @Id, @SortOrder
				END
			CLOSE SortOrder_Cursor
			DEALLOCATE SortOrder_Cursor			
			
			EXEC('UPDATE ' + @TableName + ' SET ' + @SortOrderColumn + ' = ' + @SortOrderValue + ' WHERE ' + @IdColumn + ' = ' + @RowId + @Group)
		COMMIT
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
 		PRINT 'TRAN CANCELLED: ' + error_message()
	END CATCH			
END

USE [Bss]
GO

/****** Object:  StoredProcedure [dbo].[sp_getsortorder]    Script Date: 04/17/2009 06:18:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_getsortorder] 
	-- Add the parameters for the function here
	@TableName sysname, 
	@ParentIdColumn varchar(32) = null,
	@ParentId int = null,
	@SortOrderColumn varchar(32) = 'SortOrder'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE 
		@SortOrder int,
		@Group nvarchar(1024) = ''
	
	
	IF NOT ISNULL(@ParentIdColumn, '') = ''
		IF ISNULL(@ParentId, '') = '' RAISERROR('If parameter @ParentIdColumn is specified, parameter @ParentId must be specified too.', 15, 1) 
		ELSE SET @Group = ' WHERE ' + @ParentIdColumn + ' = ' + ISNULL(CAST(@ParentId AS nvarchar(16)), '0')

	DECLARE @Sql nvarchar(1024) = N'SELECT TOP(1) @SortOrderOUT = ' + @SortOrderColumn + ' FROM ' + @TableName + @Group + ' ORDER BY ' + @SortOrderColumn + ' DESC',
			@ParmDefinition nvarchar(256) = '@SortOrderOUT int OUTPUT'
			
	print @Sql
	
	EXEC sp_executesql
			@Sql, 
			@ParmDefinition, 
			@SortOrderOUT = @SortOrder OUTPUT
	
	SELECT @SortOrder 
	
END

GO

IF OBJECT_ID('_sortOrders') IS NOT NULL DROP VIEW _sortOrders
IF OBJECT_ID('_tableA') IS NOT NULL DROP TABLE _tableA
IF OBJECT_ID('_tableB') IS NOT NULL DROP TABLE _tableB
GO

CREATE TABLE _tableA (
	[cola] INT
	, [colb] INT
	)

CREATE TABLE _tableB (
	[cola] INT
	)
GO

INSERT INTO _tableA ([cola], [colb])
      SELECT 1,2
UNION SELECT 323,0
UNION SELECT 0,123

INSERT INTO _tableB(cola)
      SELECT 0
UNION SELECT 1
GO

CREATE VIEW _sortOrders AS
          SELECT TOP (1) 'tableA' AS [table], 'cola' AS [column], 'DES' AS [Direction], [cola] AS [value] FROM _tableA ORDER BY [colA] DESC
UNION ALL SELECT TOP (1) 'tableA', 'cola', 'ASC', [cola] FROM _tableA ORDER BY [colA] ASC
UNION ALL SELECT TOP (1) 'tableA', 'colb', 'DES', [colb] FROM _tableA ORDER BY [colB] DESC
UNION ALL SELECT TOP (1) 'tableB', 'cola', 'ASC', [cola] FROM _tableB ORDER BY [cola] ASC
GO

SELECT * FROM dbo._sortOrders