Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shwelch
Starting Member
33 Posts |
 Posted - 2008-08-21 : 06:07:10
|
| Hi,I am having a problem getting my WHERE IN statement to work propertly within a stored procedure. I have a table that has regionID (int) and I want to pull out regionID's that are in a series... Select facility, region where RegionID IN (@regionid)I have tried @regionID as a varchar and if I pass in one value, such as 1, I get results. If using varchar, and I pass in 1,2,3 nothing is returned. I have also tried in the stored procedure making the variable INT, but when I pass in 1,2,3 it croaks with an error that it cannot be converted to type int.Any help? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-21 : 06:16:44
|
| Select facility, region where ','+@regionid+',' like '%,'+cast(RegionID as varchar(10))+',%'MadhivananFailing to plan is Planning to fail |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-21 : 06:43:54
|
quote:
Originally posted by shwelch
I want to pull out regionID's that are in a series.
Yes.Use two parameters, @StartSeriesID and @EndSeriesIDSELECT Facility, RegionWHERE RegionID BETWEEN @StartSeriesID AND @EndSeriesID
E 12°55'05.25"N 56°04'39.16" |
|
|
|
shwelch
Starting Member
33 Posts |
Posted - 2008-08-21 : 07:53:42
|
It isn't really a series for between-- sorry I didnt explain better. The user will be selecting checkboxes for regions they want to select, and the value returned from the checked checkboxes would be something like 2,1,4 or similar. I would then want that to be the WHERE IN portion of the query -- quote:
Originally posted by Peso
quote:
Originally posted by shwelch
I want to pull out regionID's that are in a series.
Yes.Use two parameters, @StartSeriesID and @EndSeriesIDSELECT Facility, RegionWHERE RegionID BETWEEN @StartSeriesID AND @EndSeriesID
E 12°55'05.25"N 56°04'39.16"
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-21 : 08:08:08
|
quote:
Originally posted by shwelch
It isn't really a series for between-- sorry I didnt explain better. The user will be selecting checkboxes for regions they want to select, and the value returned from the checked checkboxes would be something like 2,1,4 or similar. I would then want that to be the WHERE IN portion of the query -- quote:
Originally posted by Peso
quote:
Originally posted by shwelch
I want to pull out regionID's that are in a series.
Yes.Use two parameters, @StartSeriesID and @EndSeriesIDSELECT Facility, RegionWHERE RegionID BETWEEN @StartSeriesID AND @EndSeriesID
E 12°55'05.25"N 56°04'39.16"
Try the solution I postedMadhivananFailing to plan is Planning to fail |
|
|
|
shwelch
Starting Member
33 Posts |
Posted - 2008-08-21 : 21:30:04
|
yes, i think it is working! THANK YOUquote:
Originally posted by madhivanan
quote:
Originally posted by shwelch
It isn't really a series for between-- sorry I didnt explain better. The user will be selecting checkboxes for regions they want to select, and the value returned from the checked checkboxes would be something like 2,1,4 or similar. I would then want that to be the WHERE IN portion of the query -- quote:
Originally posted by Peso
quote:
Originally posted by shwelch
I want to pull out regionID's that are in a series.
Yes.Use two parameters, @StartSeriesID and @EndSeriesIDSELECT Facility, RegionWHERE RegionID BETWEEN @StartSeriesID AND @EndSeriesID
E 12°55'05.25"N 56°04'39.16"
Try the solution I postedMadhivananFailing to plan is Planning to fail
|
|
|
|
|
|
|
|
|
WHERE IN function in a stored procedure