rtdvoip,
Here's the full SProc you need.
It does all the different searches you need, read the remarks for each part, and you can decide on what type of search/result matches your needs.
Hope it helps
Logicalman
Copy and paste all the code below into a SProc, change the table/column names as required, and run it from a Query Analyzer, using a string parameter:
EXEC sp_rtdvoip_1 '12336'
The output will be both in the grid and messages areas.
CREATE PROCEDURE [dbo].[sp_rtdvoip_1]
@prefix varchar(100) = ''
AS
-- Stop irrevelent outputs
SET NOCOUNT ON
-- Get rid of any spaces
SET @prefix = RTRIM(LTRIM(@prefix))
-- 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
-- Keep looping until it's zero
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:
/*
or while @i < len(breakout_no)
begin
if substring(breakout_no, 1,@x)=prefix
@x=@x+1
@i=@i+1
else
begin @x=@x-1
select price
from table1
where substring(breakout_no, 1,@x) = prefix and index =rp.indx.
*/
SET NOCOUNT OFF
GO
