CLR Return Types

I would like my C# dll to return a float (value) so that I can use that
value within the SQL Server stored procedure. There seems to be a limitation
on the return types from CLR (CLR methods return either SqlInt32,
System.Int32, void.)
I would appreciate if anyone can suggest a workaround (if there is one).
Thanks.In SQL Server, procedures return either set a return code (which is INT ==
SqlInt32) or they don't (CLR return type is void). To return a value from a
stored procedure use a parameter of type OUTPUT in SQL Server (which
actually acts like ref in .NET) or use a user-defined function instead.
User-defined functions can return float (REAL in SQL Server) or double
(FLOAT in SQL Server) or other data types.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:5CBF5704-0FF7-4C58-A81D-C8837416BA34@.microsoft.com...
> I would like my C# dll to return a float (value) so that I can use that
> value within the SQL Server stored procedure. There seems to be a
> limitation
> on the return types from CLR (CLR methods return either SqlInt32,
> System.Int32, void.)
> I would appreciate if anyone can suggest a workaround (if there is one).
> Thanks.|||I guess I don't quite understand what you are saying. Could you please be
more clear? I am not sure if my question is confusing, but I want to return
a
float value from a dll.
Thanks.
"Bob Beauchemin" wrote:

> In SQL Server, procedures return either set a return code (which is INT ==
> SqlInt32) or they don't (CLR return type is void). To return a value from
a
> stored procedure use a parameter of type OUTPUT in SQL Server (which
> actually acts like ref in .NET) or use a user-defined function instead.
> User-defined functions can return float (REAL in SQL Server) or double
> (FLOAT in SQL Server) or other data types.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:5CBF5704-0FF7-4C58-A81D-C8837416BA34@.microsoft.com...
>
>|||"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:ACC4559E-3DB8-4E5A-BC3C-F6A4AA1E2477@.microsoft.com...
> I guess I don't quite understand what you are saying. Could you please be
> more clear? I am not sure if my question is confusing, but I want to
> return a
> float value from a dll.
You need to create a user-defined function rather than a stored
procedure.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

Closest match SQL query

I have a need to execute a query in T-SQL on a numeric field in a SQL table.

However if there is no exact match I'd likea query that will return the row that is just below my value.

As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned.

Is this possible? Can anyone help me with such a query?

Regards

Clive

If you use <= in your WHERE clause, and an Order By ... DESC, you will get the results you want. When you use ExecuteScalar(), it will only return the first value in the resultset.|||

Here is one (SQL Server 2005):

SELECT NumFROM(SELECT Num, Row_Number()over(orderby NumDESC)as rNum

FROM yourTable

WHERE num<=19) t

WHERE rNum=1

Another one (should work with 2000):

SELECTTop 1 NumFROM(SELECT Num,(SELECTCOUNT(*)FROM yourTable bWHERE a.Num<=b.Num)as rNum

FROM yourTable a

WHERE Num<=19) t

OrderBY rNum

|||

SELECT TOP 1 *

FROM MyTable

WHERE Num<=19

ORDER BY Num DESC

|||

Thanks for your help. However I'm not doign too well, i've not managed to get these to work. I shoudl clarify that I am useing SQL 2005.

The cloest seems to be:

SELECT TOP 1 *

FROM table

WHERE id <=15

That does indeed return one row however since I can't guarantee the order of the data in the table I need to sort it first I think.

I have tried :

SELECT top 1 *

from (SELECT * from table ORDER BY id)

WHERE ID <=15 but also this fails.

Any help appreciated.

Regards

Clive

|||

Use Motley's code. You will see that it includes an order by clause. You need to Order By id DESC.

SELECT TOP 1 *

FROM table

WHERE id <=15

ORDER BY id DESC

|||

Thanks Mike,

That works perfectly - I was being dumb and putting the ORDER BY clause in the wrong place.

Clive