----------------------------------------------------------------------------
--Purpose: Checks that the input string is really an integer of the specified type.  
-- To be used in place of the ISNUMERIC function, as it can't be trusted.
-- See http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10194
--Inspired by: http://www.aspfaq.com/show.asp?id=2390 
--Created: 10/20/05 by Oskar Austegard.
--Updated: 11/16/05 by Oskar Austegard - fixed bugs
----------------------------------------------------------------------------
ALTER FUNCTION dbo.IsReallyInteger
(  
  @Num varchar(64), --Input string to be checked
  @Type varchar(8) --Type of integer: bigint, int, smallint, tinyint
)  
RETURNS BIT  
BEGIN  
  --Get the absolute value of the number by removing a leading - or +
  DECLARE @AbsNum varchar(64), @Length tinyint
  SET @AbsNum = CASE WHEN LEFT(@Num, 1) IN ('-', '+') THEN SUBSTRING(@Num, 2, LEN(@Num)) ELSE @Num END

  --Remove leading zeros
  WHILE LEN(@AbsNum) > 1 AND LEFT(@AbsNum, 1) = '0'
    SET @AbsNum = SUBSTRING(@AbsNum, 2, LEN(@AbsNum))

  SET @Length = LEN(@AbsNum)  
  --Reinsert the - in negative numbers
  SET @Num = CASE WHEN LEFT(@Num, 1) = '-' THEN '-' + @AbsNum ELSE @AbsNum END

  --Check for empty string or non-digits
  IF @AbsNum = '' OR PATINDEX('%[^0-9]%', @AbsNum) > 0
    RETURN 0

  --Check limits by type
  IF (@Type = 'bigint' AND (@Length < 19 OR (@Length = 19 AND (@AbsNum < '9223372036854775807' OR @Num = '-9223372036854775808'))))
    OR (@Type = 'int' AND (@Length < 10 OR (@Length = 10 AND (@AbsNum < '2147483648' OR @Num = '-2147483648'))))
    OR (@Type = 'smallint' AND (@Length < 5 OR (@Length = 5 AND (@AbsNum < '32768' OR @Num = '-32768'))))
    OR (@Type = 'tinyint' AND LEFT(@Num, 1) <> '-' AND (@Length < 3 OR (@Length = 3 AND @AbsNum < '256')))
    RETURN 1 --Success
  --Else
  RETURN 0 --Failure
END  

--Creates a table of sequential numbers, useful for all sorts of things
--Created 08/26/05 by Oskar Austegard from article at 
--http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp
--Limits: @Min and @Max must be between -2147483647 and 2147483647, including.
--If @Max <= @Min, only a single record with @Min is created
ALTER FUNCTION dbo.NumberTable (@Min int, @Max int)
RETURNS @T TABLE (Number int NOT NULL PRIMARY KEY)
AS
BEGIN
  -- Seed the table with the min value
  INSERT @T VALUES (@Min)
  --Loop until all the rows are created, inserting ever more records for each iteration (1, 2, 4, etc)
  WHILE @@ROWCOUNT > 0
	BEGIN
	  INSERT @T 
	  --Get the next values by adding the current max - start value + 1 to each existing number
	  --need to calculate increment value first to avoid arithmetic overflow near limits of int
	  SELECT t.Number + (x.MaxNumber - @Min + 1)
	  FROM @T t
	    CROSS JOIN (SELECT MaxNumber = MAX(Number) FROM @T) x --Current max
	  WHERE
	    --Do not exceed the Max - shift the increment to the right side to take advantage of index
	    t.Number <= @Max - (x.MaxNumber - @Min + 1)
	END
  RETURN
END