Cursor replacement help

CREATE TABLE [dbo].[#TSC_ExportedColumnIds](
[ID] [int] NOT NULL,
[ColumnType] [int] NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (1, 1)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (2, 1)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (3, 1)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (4, 1)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (5, 1)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (6, 1)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (7, 3)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (8, 3)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (9, 3)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (10, 1)
GO
INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (11, 1)

DECLARE @TEMP_EXPORT TABLE (
[A] [varchar](500) NULL,
[B] [nvarchar](500) NULL,
[C] [nvarchar](50) NULL,
[D] [nvarchar](50) NULL,
[E] [nvarchar](1000) NULL,
[F] [varchar](1000) NULL,
[G] [varchar](500) NULL
)

DECLARE @COLUMN_ID INT
DECLARE @getID CURSOR
SET @getID = CURSOR FOR
SELECT ID
FROM dbo.#TSC_ExportedColumnIds
OPEN @getID
FETCH NEXT
FROM @getID INTO @COLUMN_ID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @COLUMN_ID
DECLARE @COLUMN_TYPE INT

SELECT @COLUMN_TYPE = TSC_COLUMNS.ColumnType
FROM TSC_COLUMNS
WHERE TSC_COLUMNS.ID = @COLUMN_ID

IF @COLUMN_TYPE = 1
BEGIN
INSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL
--INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportBlockColumnParam1](@COLUMN_ID)
--INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportBlockColumnParam2](@COLUMN_ID,4)
END

IF @COLUMN_TYPE = 2
BEGIN
INSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL
--INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](@COLUMN_ID)
--INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](@COLUMN_ID,4)
END

IF @COLUMN_TYPE = 3
BEGIN
INSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL
--INSERT INTO @TEMP_EXPORT SELECT A,B,C,D,E,F,G FROM [dbo].[udf_TSC_qry_ExportEventColumnParam0](@COLUMN_ID,4)
END

FETCH NEXT
FROM @getID INTO @COLUMN_ID
END
CLOSE @getID
DEALLOCATE @getID

select * from @TEMP_EXPORT

INSERT INTO @TEMP_EXPORT
SELECT *
FROM [dbo].[#TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](@COLUMN_ID) b
WHERE a.ColumnType IN (1,2)
UNION ALL
SELECT *
FROM [dbo].[#TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](@COLUMN_ID,4) b
WHERE a.ColumnType IN (1,2)
UNION ALL
SELECT A,B,C,D,E,F,G
FROM [dbo].[#TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](@COLUMN_ID,4) b
WHERE a.ColumnType = 3

SELECT @COLUMN_TYPE = TSC_COLUMNS.ColumnType
FROM TSC_COLUMNS
WHERE TSC_COLUMNS.ID = @COLUMN_ID

INSERT INTO @TEMP_EXPORT
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](@COLUMN_ID) b
WHERE a.ColumnType = 1
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](@COLUMN_ID,4) b
WHERE a.ColumnType = 1
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](@COLUMN_ID) b
WHERE a.ColumnType = 2
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](@COLUMN_ID,4) b
WHERE a.ColumnType = 2
UNION ALL
SELECT A,B,C,D,E,F,G
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](@COLUMN_ID,4) b
WHERE a.ColumnType = 3

SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b
WHERE a.ColumnType IN (1,2)
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b
WHERE a.ColumnType IN (1,2)
UNION ALL
SELECT A,B,C,D,E,F,G
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b
WHERE a.ColumnType = 3;

SELECT A,B,C,D,E,F,G
FROM (SELECT A.ID, B.ColumnType
FROM dbo.TSC_ExportedColumnIds A
INNER JOIN TSC_COLUMNS B ON A.ID = B.ID
) a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b
WHERE a.ColumnType IN (1,2)
UNION ALL
SELECT A,B,C,D,E,F,G
FROM (SELECT A.ID, B.ColumnType
FROM dbo.TSC_ExportedColumnIds A
INNER JOIN TSC_COLUMNS B ON A.ID = B.ID
) a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b
WHERE a.ColumnType IN (1,2)
UNION ALL
SELECT A,B,C,D,E,F,G
FROM (SELECT A.ID, B.ColumnType
FROM dbo.TSC_ExportedColumnIds A
INNER JOIN TSC_COLUMNS B ON A.ID = B.ID
) a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam0](a.ID,4) b
WHERE a.ColumnType = 3;

SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b
WHERE a.ColumnType = 1
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b
WHERE a.ColumnType = 1
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](a.ID) b
WHERE a.ColumnType = 2
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](a.ID,4) b
WHERE a.ColumnType = 2
UNION ALL
SELECT A,B,C,D,E,F,G
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b
WHERE a.ColumnType = 3;

SELECT 1 as OrderME,*
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b
WHERE a.ColumnType = 1
UNION ALL
SELECT 2,*
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b
WHERE a.ColumnType = 1
UNION ALL
SELECT 3,*
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](a.ID) b
WHERE a.ColumnType = 2
UNION ALL
SELECT 4,*
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](a.ID,4) b
WHERE a.ColumnType = 2
UNION ALL
SELECT 5, A,B,C,D,E,F,G
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b
WHERE a.ColumnType = 3
order by ID,OrderMe

oliver.morris (3/7/2013)

Thanks for the help this makes sense. The query works considerably faster. However I have one issue. Each part needs to run in order e.g.

For the 1st ID - if it was type 1 for example it needs to run

udf_TSC_qry_ExportBlockColumnParam1

and then

udf_TSC_qry_ExportBlockColumnParam2

So that in the output it is organised like:

OutputParam1 (ID 1)
OutputParam2 (ID 1)
OutputParam1 (ID 2)
OutputParam2 (ID 2)

and not

OutputParam1 (ID 1)
OutputParam1 (ID 2)
OutputParam2 (ID 1)
OutputParam2 (ID 2)

I hope this makes sense. Many thanks for your help, its almost there and runs in 1 second compared to 1 minute!

Oliver

SELECT * 
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b
WHERE a.ColumnType = 1
UNION ALL 
SELECT * 
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b
WHERE a.ColumnType = 1
UNION ALL
SELECT * 
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](a.ID) b
WHERE a.ColumnType = 2
UNION ALL
SELECT * 
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](a.ID,4) b
WHERE a.ColumnType = 2
UNION ALL
SELECT A,B,C,D,E,F,G 
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b
WHERE a.ColumnType = 3;