Prev: File-Permissions in DATA-Folder (Attach / Detach)
Next: Log Shipping not restoring transaction log backup
From: Muhammad Bilal on 31 Mar 2010 09:08
I have a function which returns the minimum value

CREATE FUNCTION Getname2
(@Int1 FLOAT(8),
@package1 VARCHAR(50),
@Int2 FLOAT(8),
@package2 VARCHAR(50),
@Int3 FLOAT(8),
@package3 VARCHAR(50)
)
RETURNS VARCHAR(50)
BEGIN
DECLARE @AllValues TABLE(
INPUT FLOAT(8),
package VARCHAR(50)
)

INSERT INTO @AllValues
VALUES (@Int1, @package1)

INSERT INTO @AllValues
VALUES (@Int2, @package2)

INSERT INTO @AllValues
VALUES (@Int3, @package3)

RETURN
(SELECT package
FROM @AllValues
WHERE [Input] != 0 and input = (select min(input) from @AllValues ))
END


If I call the function with variables it gives the perfect result

select Calls.dbo.Getname2(1,'Morning',2,'Evening',3,'Night')

but if I use where there are two minimum values it gives error “Subquery
returned more than 1 value”,

select Calls.dbo.Getname2(1,'Morning',1,'Evening',3,'Night')
Is this possible that the if there are two minimum values it return randomly
any single value i.e,

Morning or Evening

Secondly how to return both values of int and packag on a single call.


Regards,
Muhammad Bilal


From: Plamen Ratchev on 31 Mar 2010 09:35
You can change the last query in the function to return always a scalar value:

SELECT TOP 1 package
FROM @AllValues
WHERE input <> 0
ORDER BY input;

To return multiple columns you have to transform the function to table-valued function:

CREATE FUNCTION Getname2
(@Int1 FLOAT(8),
@package1 VARCHAR(50),
@Int2 FLOAT(8),
@package2 VARCHAR(50),
@Int3 FLOAT(8),
@package3 VARCHAR(50)
)
RETURNS TABLE
RETURN (SELECT TOP 1 input, package
FROM (SELECT @Int1, @package1
UNION ALL
SELECT @Int2, @package2
UNION ALL
SELECT @Int3, @package3) AS T(input, package)
ORDER BY input);

And then call it:

SELECT input, package
FROM Getname2(1, 'Morning', 1, 'Evening', 3, 'Night');

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: File-Permissions in DATA-Folder (Attach / Detach)
Next: Log Shipping not restoring transaction log backup