Quotename and Parameters in CLR VB.NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Quotename and Parameters in CLR VB.NET

Author

Topic

javaguy5
Starting Member

9 Posts

Posted - 2008-07-17 : 18:32:50

I am unable to get my code below to work. I have include a snipet of how it would work from SQL but because sp_execute sql cannot be used in functions so I need to use it in a CLR userdefined function so the return value can be used in stored procedures, other functions, and VB code. I am trying to have the user enter a column name and ultimatly return the min value of that column. I am pretty sure that I am getting a error in the SELECT statment near the quotename. I know I could drop the @min and use Eexecutescalar but in the future I may need to be able to use this value, and I also hear this was a better practice to use parameterized valus. Any help would be great. thanks in advance.

I know the connection string is good because it will execute simpler statements.
I am getting a cannot convert from varchar to float error, and do not know how to fix it.

<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=False)> _
Public Shared Function getMin(ByVal column As SqlString) As Double
Dim minValue As Double

Dim conn As SqlConnection
Dim connectionString As String
connectionString = "context connection=true"

Dim cmdMin As SqlCommand

cmdMin = New SqlCommand
cmdMin.CommandType = CommandType.Text
cmdMin.CommandText = "Select @min = min(' + quotename(" +
column + ") + ') from dbo.tempRawDataForTest"

Dim minSQLPara As SqlParameter
minSQLPara = New SqlParameter
minSQLPara = cmdMin.Parameters.Add("@min", SqlDbType.Float)
minSQLPara.Direction = ParameterDirection.Output

Try
conn = New SqlConnection()
conn.ConnectionString = connectionString
conn.Open()
cmdMin.Connection = conn

cmdMin.ExecuteNonQuery()
minValue = CDbl(minSQLPara.Value())

Catch exc As SqlException

minValue = -1

Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If

End Try

Return minValue

End Function

Below is how it works in SQL

declare @columnInput nvarchar(max)
declare @sqlSt as nvarchar(max)
Declare @min as decimal (6,4)

set @columnINput = 'leftHorizRaw'

set @sqlSt = ('Select @min = min(' + quotename(@columnInput) + ') from dbo.tempRawDataForTest')

exec sp_executesql @sqlSt, N'@min decimal(6,4) OUTPUT', @min = @min OUTPUT

Select @min

I need to to be done in a CLR because SQL Function will not allow the use of sp_executeSQL

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-18 : 13:46:04

This doesn't make any sense. Why not just write a single stored proc that selects the MIN() of all columns in the table, and then just use which column from the result that you need? Why are you doing this?

- Jeff
http://weblogs.sqlteam.com/JeffS

javaguy5
Starting Member

9 Posts

Posted - 2008-07-18 : 14:10:17

Ah, I can do what you have mentioned. Although is there is way to pass column names into a CLR function like you can in a regular sql Procedure?

quote:
Originally posted by jsmith8858

This doesn't make any sense. Why not just write a single stored proc that selects the MIN() of all columns in the table, and then just use which column from the result that you need? Why are you doing this?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources