Stored Proceedure to capture Array of Values

[vcujackson]

alter procedure school_lookup1
@schoolyear1 char (2) = ''
@schoolyear2 char (2) = ''
@schoolyear3 char (2) = ''
@degreeyear1 char (4) = ''
@degreeyear2 char (4) = ''
@degreeyear3 char (4) = ''
@schoolname1 char (65) = ''
@schoolname2 char (65) = ''
@schoolname3 char (65) = ''
as
select schoolid, schoolyear,schoolname,degreeyear
from school_lookup1
where schoolyear in (@schoolyear1,@schoolyear2,@schoolyear3)
and
degreeyear in (@degreeyear1,@degreeyear2,@degreeyear3)
and
schoolname in (@schoolname1,@schoolname2,@schoolname3)

Is there a way to re-work this stored procedure so that it can capture a range of the forementioned values. For example, the degree years go back to 1900 and I would not want to do parameters for all those years

 

[markros]

May be instead of IN you can use BETWEEN to include all years, e.g.

DegreeYear between @StartYear and @EndYear

 

[RiverGuy]

In SQL Server 2008, you can use Table-Valued parameters. See here:

http://msdn.microsoft.com/en-us/library/bb510489.aspx

For SQL Server 2005 and below, do a google search for "SQL Server Split Function