— Declare variables to use
DECLARE @x int
DECLARE @Price money
DECLARE @LENGTH int
DECLARE @prefix1 varchar(100)
DECLARE @Match varchar (100)

— Test for supplied value
SET @Price = (select price from table1 where breakout_no LIKE @prefix )
— Was it found? If so, then print it and skip the rest of the code
IF NOT (@Price=NULL)
BEGIN
PRINT ‘FOUND!!’
PRINT ‘PRICE: ‘ + cast(@Price as varchar(100)) + ‘ for breakout_no: ‘ + CAST(@prefix AS varchar(100))
GOTO end_sp
END

— Oops, not found, so we go searching
PRINT ‘SEARCHING …’

— Lets get a temp table, and convert all the strings into numerical data
CREATE TABLE #table1
(
price money,
breakout_no int
)
INSERT #table1
SELECT price, CAST(breakout_no AS int) FROM table1 order by CAST(breakout_no AS int)

— SELECTING FROM ORIGINALTABLE
— Search for the next lowest Numerical Value
SELECT TOP 1’Next Highest Numerical Value’ AS TYPE, price, breakout_no FROM table1
WHERE CAST(breakout_no as int) > (CAST(@prefix as int)-1)

— Search for the next Highest Numerical Value
SELECT TOP 1 ‘Next Lowest Numerical Value’ AS TYPE, price, breakout_no FROM table1
WHERE CAST(breakout_no as int) < (CAST(@prefix as int)+1) order by breakout_no DESC -- Total table output SELECT 'All Values' AS TYPE, price, breakout_no FROM table1 order by CAST(breakout_no AS int) -- SELECTING FROM TEMP TABLE -- Search for the next lowest Numerical Value SELECT TOP 1'Next Highest Numerical Value' AS TYPE, price, breakout_no FROM #table1 WHERE CAST(breakout_no as int) > (CAST(@prefix as int)-1)

— Search for the next Highest Numerical Value
SELECT TOP 1 ‘Next Lowest Numerical Value’ AS TYPE, price, breakout_no FROM #table1
WHERE CAST(breakout_no as int) < (CAST(@prefix as int)+1) order by breakout_no DESC -- Total table output SELECT 'All Values' AS TYPE, price, breakout_no FROM #table1 order by CAST(breakout_no AS int) -- Lets get the length of the parameter -- so we can reduce it's size for matching loop below SET @LENGTH = LEN(@prefix) -- Debug Prints PRINT'@prefix : ' + CAST(@prefix AS varchar(100)) PRINT'LENGTH : ' + CAST(@LENGTH AS varchar(100)) -- Set the loop counter SET @x = @LENGTH WHILE(@x>0)
BEGIN
— Get the new string
SET @prefix1 = LEFT(@prefix, @x)
— Check for match
SET @Price = (select price from table1 where breakout_no LIKE @prefix1 )
— Test if match has been found
— If so, then get out of the loop
IF NOT (@Price=NULL)
BEGIN
SET @Match = (SELECT TOP 1 breakout_no FROM table1 WHERE price = @Price)
PRINT ‘FOUND!!’
PRINT ‘PRICE: ‘ + cast(@Price as varchar(100)) + ‘ breakout_no:’ + CAST(@Match AS varchar(100))
GOTO end_sp
END

PRINT’@prefix : ‘ + CAST(@prefix1 AS varchar(100))
SET @x = @x – 1
SET @prefix1 = LEFT(@prefix, @x)
END
— Loop has ended
— So has sp, so dispose of the temp objects
DROP TABLE #table1
end_sp: