SQL Server find largest number for a data type

DECLARE @int int = 0

WHILE 1 = 1
BEGIN
BEGIN TRY
SET @int = @int + 1000
END TRY
BEGIN CATCH
BEGIN TRY
SET @int = @int + 1
END TRY
BEGIN CATCH
SELECT @int
BREAK;
END CATCH
END CATCH
END

SQL Server – methods to prevent repeating rows on a parent table

Every so often I have to bring in some information to a parent table from child tables. The foreign key in the child tables would cause the parent table to repeat rows in the resultset. The following sql sets up two tables as an example. tblTestCase being the parent table:

CREATE TABLE [dbo].[tblTestCase](
[CaseId] [int] IDENTITY(1,1) NOT NULL,
[Description] varchar(50) NULL,
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblTestInvoice](
[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
[CaseId] [int] NULL,
[Fee] [money] NULL
) ON [PRIMARY]

GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case1')
GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case2')
GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case3')
GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case4')
GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case5')
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(1
,10.00)
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(1
,20.00)
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(1
,30.00)
GO
INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(2
,40.00)
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(2
,60.00)
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(3
,100.00)
GO

SELECT * FROM tblTestCase
SELECT * FROM tblTestInvoice

The easiest way to prevent repeating in the resultset is by using subselects:

SELECT *, (SELECT TOP 1 FeeId FROM tblTestInvoice WHERE CaseId = CaseId) FROM tblTestCase

However subselects are not efficient. A better way that I would use is a pseudo table created from an sql query which you can join to
–cleaner method to left join on pseudo table
SELECT * FROM tblTestCase
LEFT OUTER JOIN (SELECT DISTINCT CaseId As InvoiceCaseID FROM tblTestInvoice)
As tblTestCaseInvoice ON tblTestCaseInvoice.InvoiceCaseID = tblTestCase.CaseId