Need stored procedure to take x number of zipcodes

I am designing a stored procedure to return results from a search.

I need all apartments returned that has a specific zipcode..

The select should do what this does:

select * from tbl_houses
where

(
  (zip_code in 开发者_如何转开发(10000,20000,30000,40000))
  or
  (zip_code > 50000 and zip_code < 60000)
)

In other words. I want a stored procedure which can both take a zip_code range and series of zip_codes.

Anyone know an elegant way of doing this?

My current solutions are ugly as xxxx.

Cheers Mathias

SQL Server 2008? You could pass a table parameter: http://msdn.microsoft.com/en-us/library/bb510489.aspx

2005+? You could pass an XML parameter with a list of values: http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

OK, a dirty 2000 thought:

Pass a parameter like this:

SET @myParam = "<1234><5678>"

then in your query use a backwards-looking like, see whether the parameter contains the number you want - < and > are used as delimiters:

SELECT x FROM y WHERE @myParam LIKE '%<' + zipCode + '>%'

Finally, I found this resource:

http://www.sommarskog.se/arrays-in-sql-2000.html#OPENXML

Which even suggests XML route is possible with 2000 with sp_xml_preparedocument etc.

You can use a Split Table-value function for this:

CREATE FUNCTION [dbo].[Split](@RowData VARCHAR(8000), @SplitOn VARCHAR(5))  
RETURNS @RtnValue TABLE 
(
    Id int identity(1,1),
    Data nvarchar(2000)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Then you can use it on your stored procedure, maybe with another parameter indicating if its a range or a list:

CREATE PROCEDURE dbo.GetHouses(@Zip VARCHAR(8000), @Type INT)
AS
/* 
  @Type:    1 - List
            2 - Range
*/

IF @Type = 1
BEGIN
    SELECT * 
    FROM tbl_houses H
    JOIN dbo.Split(@Zip, ',') S
    ON H.zip_code = S.Data
END
IF @Type = 2
BEGIN
    SELECT * 
    FROM tbl_houses
    WHERE zip_code >= (SELECT Data FROM dbo.Split(@Zip, ',') WHERE Id  = 1)
    AND zip_code <= (SELECT Data FROM dbo.Split(@Zip, ',') WHERE Id  = 2)
END