Create Function fn_ExpandLotRange
(
	@BLN varchar(5),
	@ELN varchar(5)
)
Returns varchar(1000)
AS
Begin

	Declare @TLN int	--Total numbers needing to be added
	Declare @Loop Int --Looping intervals
	Declare @End  Int --Ending Loop Interval

	Declare @Str varchar(1000) --Results are built in here

	IF @BLN Like '%[a-z]%'
		RETURN ''
	ELSE IF @ELN Like  '%[a-z]%'
		RETURN ''
	Else
		Set @TLN = convert(int,@ELN) - convert(Int,@BLN) +1

	Set @Str = @Str + @BLN + ','
	Set @TLN = @TLN - 1
	
	While @TLN >0
		BEGIN
			Set @STR = @Str+Convert(varchar(5),(Convert(int,@BLN)+1))
			Print @STR
		END
	END
		
	Return @STR;
END

CREATE FUNCTION fn_ExpandLotRangeitvf( @start int,@end int)
returns table
AS
RETURN 
SELECT STUFF( (SELECT ',' + convert(varchar,MiniTally.n )
                          FROM (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
             FROM   sys.columns) MiniTally
                         WHERE MiniTally.n between @start and @end
                        FOR XML PATH('') ) ,1,1,'') As String
GO                        
                        
SELECT * from fn_ExpandLotRangeITVF(2,8)
  GO   
      
CREATE FUNCTION fn_ExpandLotRange( @start int,@end int)
RETURNS varchar(1000)
AS 
BEGIN
DECLARE @results varchar(1000)
SELECT @results =  STUFF( (SELECT ',' + convert(varchar,MiniTally.n )
                          FROM (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
             FROM   sys.columns) MiniTally
                         WHERE MiniTally.n between @start and @end
                        FOR XML PATH('') ) ,1,1,'')
return @results                        
END

GO
SELECT  dbo.fn_ExpandLotRange(3,10)

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Select R.RecordNumber, R.BeginningLotNo, R.EndingLotNo,
CASE WHEN LTRIM(RTRIM(R.BeginningLotNo)) = LTRIM(RTRIM(R.EndingLotNo)) THEN R.BeginningLotNo
	 ELSE f.String
END LOT
From Ellis.LandT_3_Raw R 
	 Cross Apply 
	 dbo.fn_ExpandLotRangeITVF(R.BeginningLotNo, R.EndingLotNo) f
Where R.BeginningLotNo Not like '%[a-z]%'
UNION
Select R.RecordNumber, R.BeginningLotNo, R.EndingLotNo, ''
From Ellis.LandT_3_Raw R 
Where R.BeginningLotNo like '%[a-z]%'

We walk in the dark places no others will enter
We stand on the bridge and no one may pass