August 5, 2003 at 4:01 pm
I am trying to create a stored procedure/function that receives several parameters and returns the largest value.
It would be much easier if I could pass the parameters as values in an array or something equivalent. This would make the process of creating the code to determine the largest value much easier. Is there a way to do this using an array or perhaps a cursor?
Thanks.
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
August 5, 2003 at 4:49 pm
SQL2k doesn't support arrays. However, here's a sample of a stored procedure that will accept a comma-delimited string of numeric values and return the maximum.
CREATE PROCEDURE spMaxVal
@choices VARCHAR(8000) -- Comma-delimited string of values (eg. '231,454,64,18,43,434,543,143')
AS
DECLARE @max INT
DECLARE @nchoices NVARCHAR(4000)
--
-- Change the input comma-delimited string to a valid SQL statement
-- Eg. sample string above will be changed to:
--
-- select @max = max(val)
-- from (select [val] = 231
-- union select 454 union select 64 union select 18
-- union select 43 union select 434 union select 543 union select 143) x
--
SELECT @nchoices = 'SELECT @max = MAX(VAL) FROM (SELECT [VAL] = ' + REPLACE(@choices,',',' UNION SELECT ') + ') X'
EXEC SP_EXECUTESQL @nchoices, N'@MAX INT OUTPUT', @max OUTPUT
RETURN @max
GO
To test the code...
declare @MaxVal INT
exec @MaxVal = spMaxVal '231,454,64,18,43,434,543,143'
select @MaxVal
Cheers,
- Mark
Cheers,
- Mark
August 6, 2003 at 9:49 am
Can this be converted to a function? That would involve replacing the sp_executesql statement with an xp_ statement.
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
August 6, 2003 at 3:04 pm
Probably best to replace the sp_executesql with parsing of the string using builtin functions.
Cheers,
- Mark
Cheers,
- Mark
August 6, 2003 at 3:06 pm
ok Thanks for all your help. That code was very interesting.
spring, summer, fall now
dawn breaks on cold, white magic
Winter's chill is nigh
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
August 7, 2003 at 9:23 am
Hot summers day, not much else on:
create function dbo.MaxChoice (@Choices varchar(8000))
returns int
as
begin
declare @Pos int
declare @MaxChoice int
declare @WorkingChoice int
set @Choices = @Choices + ','
while len(@Choices) > 0
begin
set @pos = charindex(',',@Choices)
set @WorkingChoice = substring(@Choices,0,@Pos)
if @WorkingChoice > @MaxChoice or @MaxChoice is null
set @MaxChoice = @WorkingChoice
set @Choices = (right(@Choices,len(@Choices) - @Pos))
end
return(@MaxChoice)
end
go
select dbo.MaxChoice('12,54,65,78654,24,65,98')
August 7, 2003 at 12:47 pm
The best way to go about this in my opinion is to put the data you want to compare into a formatted XML string. You can then use OPENXML in the stored procedure/function to simply get the max value. This should be much faster than having to parse a delimited string although I have used that approach many times as well.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
August 7, 2003 at 3:16 pm
Sounds interesting. I'm always keen to learn something new. Can you post an example?
Cheers,
- Mark
Cheers,
- Mark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply