-3

My code works fine if I pick a column where the values are doubles but now I want to uwe another column where the values are not double, can I convert them to doubles inside the select statement?

the values are like 00:17,15

I already have a method that makes this string to a double but don't know how to use is inside the select statement, suggestions? 

  SqlCommand command = new SqlCommand("SELECT min(Score) FROM "+ table +" WHERE [" + sportEvent + "] < (@result);", connect);
        command.Parameters.AddWithValue("@result", result);
user2323240
  • 1,607
  • 2
  • 13
  • 15
  • You can't use C# methods in a SQL query (alright, you [can](http://stackoverflow.com/questions/11441369/php-string-concatenation), but usually don't want to), because the query gets sent to the server and is executed there. It depends in what format the data is stored and what you want to with it. – CodeCaster May 30 '13 at 13:18
  • 1
    `string` and `double` are not SQL types. – Jodrell May 30 '13 at 13:19
  • Which one of those is your string that you want to convert? Score? Result? It's not very clear from your question. – valverij May 30 '13 at 13:20
  • I want to compare it with a double (< or >) but can't do that if I can't change for example 00:17,15 to 17,15 – user2323240 May 30 '13 at 13:21
  • sportEvent is a string, result is a double I give in using a textbox – user2323240 May 30 '13 at 13:22

1 Answers1

1

Basically create a new SQL Server project to host your function. Create a class and add your function to that class as a static function. Decorate your function with the Microsoft.SqlServer.Server.SqlFunctionAttribute Compile your project, and register the assembly in SQL server

Take a look at http://tech.avivo.si/tag/create-custom-sql-function-from-c/

Gives a pretty easy example of how to create a function in C# that you can call from SQL Server

Also look at http://msdn.microsoft.com/en-us/library/w2kae45k%28VS.80%29.aspx

Martin Ernst
  • 5,629
  • 2
  • 17
  • 14
  • You really don't want this when it can simply be solved with a REPLACE or whatever. OP should explain what "00:17,5" and "17,5" have to do with each other. – CodeCaster May 30 '13 at 15:19
  • Quoting from MSDN: "CLR functions benefit from a quicker invocation path than that of Transact-SQL user-defined functions." (http://msdn.microsoft.com/en-us/library/ms131075.aspx) - also the logic to convert from TimeStamp to double is much easier to understand and maintain in C# than in T-SQL – Martin Ernst May 31 '13 at 12:35
  • `REPLACE` is not an UDF. Anyway the underlying problem is that OP is comparing the wrong datatypes. Yes, it is perfectly possible to write a CLR converter, put it in SQL server and let your function run thousands of times for every query. OR: OP stops now, rethinks his data format and solves it without the need for conversion. I'm not saying CLR functions in T-SQL don't have their place, I'm just saying in this case it absolutely doesn't look like the right solution for the problem at hand. – CodeCaster May 31 '13 at 12:38