June 12, 2003 at 5:11 am
Is there a way to set my database stored procedure parameter to accept discrete values and or Multiple values.
June 12, 2003 at 5:14 am
Nope. Not as far as I know / have seen.
The best way for multiple values is a seperated string which gets plit in the proc.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 12, 2003 at 5:15 am
Can you give me more of an idea of what you are wanting to do. Crappy is right on the Multiple values but when you say discrete what do you mean.
June 12, 2003 at 5:29 am
Hi Antares686,
quote:
Can you give me more of an idea of what you are wanting to do. Crappy is right on the Multiple values but when you say discrete what do you mean.
discrete numbers I know from math. Think of this as 1,2,3 (and nothing in between) where continous numbers are 1,2 (and anything in between these two values)
It would be interesting to read what eily really means.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 12, 2003 at 5:45 am
hi
basically i mean by discrete is
entering one value at a time.
i want to know is it possible when using a parameter in a stored proc that when i go to execute the proc that instead of entering the values one by one that i could enter "ALL" and get all possible values returned
cheers guys
June 12, 2003 at 5:52 am
Hi eily,
quote:
i want to know is it possible when using a parameter in a stored proc that when i go to execute the proc that instead of entering the values one by one that i could enter "ALL" and get all possible values returned
something like
SELECT * FROM table WHERE field LIKE '%'
where % can be % or any number?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 12, 2003 at 6:39 am
eily,
Do you mean like this
where @param = 'ALL' or (@param <> 'ALL' and charindex(cast(column as varchar),','+@param+',') > 0)
param could be 'ALL' or '1' or '1,2,3' etc
Far away is close at hand in the images of elsewhere.
Anon.
June 12, 2003 at 6:42 am
You could consider calling the procedure from a loop, and then using a variable to determine the values being used. Lots of overhead for the server, since loads of results can be generated. If the results pane of QA is 'in text', you messages can appear before the result set of each iteration.
Here is a sample procedure and a loop to use it.
create procedure pChecker @val int as select * from [order details] where quantity = @val
declare @x int
set @x = 1
while @x < 10
begin
print 'where @x is : ' + cast(@x as varchar(5))
exec pchecker @val = @x
set @x =@x +1
end
Not sure this is the best approach, hope it helps.
June 12, 2003 at 6:42 am
You could consider calling the procedure from a loop, and then using a variable to determine the values being used. Lots of overhead for the server, since loads of results can be generated. If the results pane of QA is 'in text', you messages can appear before the result set of each iteration.
Here is a sample procedure and a loop to use it.
create procedure pChecker @val int as select * from [order details] where quantity = @val
declare @x int
set @x = 1
while @x < 10
begin
print 'where @x is : ' + cast(@x as varchar(5))
exec pchecker @val = @x
set @x =@x +1
end
Not sure this is the best approach, hope it helps.
June 12, 2003 at 9:19 am
thanks guys it works !!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply