sir my procedure is not returning any record. But i dont know why. I followed the example you taught on the link below
Still my procedure does not return any records.
But i got records when i did this
select e.embid from orders o inner join job_emboss e on e.id = o.posino
select c.posino from orders o inner join job_cylinder c on c.id = o.posino
select d.dieno from orders o inner join job_die d on d.id = o.posino
select j.gietzdieref from orders o inner join job j on j.id = o.productcode
CREATE TABLE [dbo].[job](
[id] [int] NULL,
[pid] [nvarchar](50) NULL,
[embid] [nvarchar](50) NULL,
[diereferneceno] [nvarchar](50) NULL,
[gietzdieref] [nvarchar](50) NULL,
[description] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_cylinder] Script Date: 8/3/2021 12:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_cylinder](
[id] [int] IDENTITY(1,1) NOT NULL,
[pid] [int] NOT NULL,
[ppid] [nvarchar](251) NULL,
[posino] [nvarchar](251) NULL,
[colour] [nvarchar](250) NULL,
[screen] [nvarchar](251) NULL,
[stylusangle] [nvarchar](121) NULL,
[depth] [nvarchar](251) NULL,
[cellwallvolume] [nvarchar](161) NULL,
[xdiagonal] [nvarchar](101) NULL,
[stretchfactor] [nvarchar](141) NULL,
[engravingtype] [nvarchar](141) NULL,
[channel] [nvarchar](100) NULL,
[cylinderdiameter] [nvarchar](100) NULL,
[circumference] [nvarchar](100) NULL,
[printwidth] [nvarchar](100) NULL,
[referencecolour] [nvarchar](100) NULL,
[cylinderfacewidth] [nvarchar](100) NULL,
[roughness] [nvarchar](100) NULL,
[chromelayerthickness] [nvarchar](100) NULL,
[unit] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_die] Script Date: 8/3/2021 12:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_die](
[id] [int] NULL,
[dieno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_emboss] Script Date: 8/3/2021 12:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_emboss](
[id] [int] NULL,
[embid] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[orders] Script Date: 8/3/2021 12:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[orders](
[orderid] [int] NULL,
[quantity] [int] NOT NULL,
[dateordered] [nvarchar](25) NOT NULL,
[dateexpected] [nvarchar](25) NULL,
[deliverydate] [nvarchar](25) NULL,
[productcode] [nvarchar](25) NOT NULL,
[producttype] [int] NOT NULL,
[posino] [nvarchar](40) NOT NULL,
[qtyaccepted] [int] NULL,
[qtyrejected] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[job] ([id], [pid], [embid], [diereferneceno], [gietzdieref], [description]) VALUES (11, N'Test33', N'453', N'570', N'Git1', N'Round1')
GO
INSERT [dbo].[job] ([id], [pid], [embid], [diereferneceno], [gietzdieref], [description]) VALUES (22, N'Test44', N'455', N'567', N'Git2', N'Round1')
GO
INSERT [dbo].[job] ([id], [pid], [embid], [diereferneceno], [gietzdieref], [description]) VALUES (33, N'Test55', N'478', N'589', N'Git3', N'Round1')
GO
SET IDENTITY_INSERT [dbo].[job_cylinder] ON
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [colour], [screen], [stylusangle], [depth], [cellwallvolume], [xdiagonal], [stretchfactor], [engravingtype], [channel], [cylinderdiameter], [circumference], [printwidth], [referencecolour], [cylinderfacewidth], [roughness], [chromelayerthickness], [unit]) VALUES (136, 16, NULL, N'4512082-02-1A', N'BLUE', N'', N'', N'', N'', N'', N'', N'', N'', N'518.623', N'786.823', N'917.71', N'BLUE', N'900', N'', N'', 6)
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [colour], [screen], [stylusangle], [depth], [cellwallvolume], [xdiagonal], [stretchfactor], [engravingtype], [channel], [cylinderdiameter], [circumference], [printwidth], [referencecolour], [cylinderfacewidth], [roughness], [chromelayerthickness], [unit]) VALUES (137, 16, NULL, N'4612082-02-2A', N'RED', N'', N'', N'', N'', N'', N'', N'', N'', N'518.623', N'786.823', N'917.71', N'', N'900', N'', N'', 7)
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [colour], [screen], [stylusangle], [depth], [cellwallvolume], [xdiagonal], [stretchfactor], [engravingtype], [channel], [cylinderdiameter], [circumference], [printwidth], [referencecolour], [cylinderfacewidth], [roughness], [chromelayerthickness], [unit]) VALUES (138, 16, NULL, N'4712082-02-3A', N'GREEN', N'', N'', N'', N'', N'', N'', N'', N'', N'518.623', N'786.823', N'917.71', N'', N'900', N'', N'', 8)
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [colour], [screen], [stylusangle], [depth], [cellwallvolume], [xdiagonal], [stretchfactor], [engravingtype], [channel], [cylinderdiameter], [circumference], [printwidth], [referencecolour], [cylinderfacewidth], [roughness], [chromelayerthickness], [unit]) VALUES (139, 16, NULL, N'4812082-02-4A', N'BLACK', N'', N'', N'', N'', N'', N'', N'', N'', N'518.623', N'786.823', N'917.71', N'', N'900', N'', N'', 9)
GO
SET IDENTITY_INSERT [dbo].[job_cylinder] OFF
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (1, N'Die101')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (2, N'Die202')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (3, N'Die303')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (4, N'Die404')
GO
INSERT [dbo].[job_emboss] ([id], [embid]) VALUES (453, N'filmno1')
GO
INSERT [dbo].[job_emboss] ([id], [embid]) VALUES (454, N'filmno2')
GO
INSERT [dbo].[job_emboss] ([id], [embid]) VALUES (455, N'filmno3')
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (1, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 3, N'136', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (2, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 3, N'137', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (3, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 3, N'138', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (4, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'33', 3, N'139', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (5, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'331', 6, N'453', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (6, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 6, N'454', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (7, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'22', 6, N'455', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (8, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'11', 4, N'1', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (9, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 4, N'2', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (10, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 4, N'3', 2, 1)
GO
INSERT [dbo].[orders] ([orderid], [quantity], [dateordered], [dateexpected], [deliverydate], [productcode], [producttype], [posino], [qtyaccepted], [qtyrejected]) VALUES (10, 1, N'09/05/2014', N'09/05/2014', N'09/05/2014', N'286', 4, N'4', 2, 1)
GO
/****** Object: StoredProcedure [dbo].[koko] Script Date: 8/3/2021 12:35:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[koko]
@PageIndex INT = 1
,@PageSize INT = 15
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY o.[orderid] desc) AS RowNumber,
o.orderid,
CASE WHEN o.producttype = '3' THEN c.posino
WHEN o.producttype = '4' THEN d.dieno
WHEN o.producttype = '6' THEN e.embid
WHEN o.producttype = '1' THEN j.gietzdieref
END 'posino_dieno_embid_gietzdieref',
o.producttype,j.pid
INTO #Results
FROM orders o
INNER JOIN job_cylinder c ON c.id = o.posino
INNER JOIN job_emboss e ON e.id = o.posino
INNER JOIN job_die d ON d.id = o.posino
INNER JOIN job j ON j.id = o.productcode
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber between (@PageIndex-1)*@PageSize + 1
AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1
DROP TABLE #Results
END
GO
Please help me sir
