Format of input parameter - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Format of input parameter

Author

Topic

rachelreese
Starting Member

4 Posts

Posted - 2003-12-08 : 13:22:40

Hi,

In my code, I have a group of checkboxes with automatically determined ranges. E.g. -
<checkbox> 1-44
<checkbox> 45-98
<checkbox> 99-149
...

When I check one of them, I want to then display all the data for the ranges selected (can be multiple, non-adjacent ranges). My problem is formatting the input to the sproc so that it's useful. I was thinking of sending in a comma-delimited string (like "1,44,99,149.."), and then splitting it and looping through the values. Can I do that? How would I? Or, I could preform a part-SQL statement like " and (data between 1 and 44 or data between 99 and 149) ", but I hate sending in half SQL statements. It feels too hacky.

Any ideas on the best way to format what I pass in so that it's actually useful in the sproc?

Thanks,
Rachel

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2003-12-08 : 13:26:31

I have some examples here:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

See if they are of any use in this case.

--
HTH,
Vyas
http://vyaskn.tripod.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-08 : 13:41:19

I would do it this way:

Create a table of your ranges:

Code, Range, MinValue, MaxValue
'A','1-44',1,44
'B','45-98',45,98
'C''99-149',99,149

and have that correspond to the ranges in your checkboxes. "Code" should be the primary key, and is just there so you can easily pass in a concatentated string of all ranges the user has picked. (This should be easy to do with a script or something). i.e., the parameter passed in would be "AB" or "BC" or "A" or something like that.

Then, you can say:

SELECT A.*
FROM
YourTable A
INNER JOIN
Ranges R
ON
A.Value between R.MinValue and R.MaxValue
WHERE
@Ranges like '%' + R.Code + '%'

where @Ranges is the parameter passed in. The WHERE seems inefficient but it's not -- becauset the Ranges table has only a few rows the fact that there needs to be a scan should be no big deal.

- Jeff

rachelreese
Starting Member

4 Posts

Posted - 2003-12-08 : 15:45:13

Hi Jeff,

Thanks, but the problem with that (which I didn't make too clear) is that my ranges will vary. I'm working on a version of Vyas's (method 2) code. I'll post it once I actually get it working cuz I'm not convinced that that is really the way to go.

Thanks,
Rachel

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-08 : 16:20:07

Here is a method using a tally table which will generate a list of numbers between all given ranges
that you can join to your table for results

--create tally table
create table numbers ( n int PRIMARY KEY)
declare @n int; set @n = 0

--Make as large as your largest input param
while @n <= 1000 
begin
	insert into numbers select @n
	set @n = @n+1
end


declare @input varchar(100)
set @input = '1-44,99-149'

SELECT DISTINCT n 
FROM numbers a
JOIN 
(
	SELECT substring(Input,1,charindex('-',Input)-1) MinRange, substring(Input,charindex('-',Input)+1,LEN(Input)) MaxRange
	FROM
	(
		SELECT NullIf(SubString(',' + @input + ',' , n , CharIndex(',' , ',' + @input + ',' , n) - n) , '') Input
		FROM Numbers 
		WHERE n <= Len(',' + @input + ',') AND SubString(',' + @input + ',' , n - 1, 1) = ',' 
		AND CharIndex(',' , ',' + @input + ',' , n) - n > 0  
	) c
) b on b.minrange <= a.n and b.maxrange >= a.n
ORDER BY n

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources