Hi all,

I want to define the collumns name from a variable. The code bellow returns me ‘KG’ but I want the value for the field ‘KG’. Can someone please help me..?

thank you

declare @unit_a nvarchar(3)
set @unit_a='KG'

SELECT @unit_a
FROM table

KG is the value you set it to?

Sorry this makes no sense... what you do you want do?

Do you mean something like this?


declare @field varchar(10)
declare @sql varchar(50)
set @field = 'name'
set @sql = 'SELECT '+ @field + ' FROM SYS.SYSOBJECTS'
print @sql
exec(@sql)

Yes that is what i mean... Cause of my bad english, someone must be a diviner to see what i want to do...

Thank you

I am back again..:o

Unfortunately the exec command can't be executed into a function. All i want to do is to convert an ms access function to sql function. I will post the MsAccess function so any advice is welcomed. The area marked with red color is the area that i am trying to convert but i think that it can't be done in SQL Function. I don't know if i must try to convert it to a stored procedure.. what do you think guys;


Function ChangeQuantUnit(xMaterial, xquant_a, xunit_a, xunit_b)

Static M As Recordset, Mat_SQL, quant_a, unit_a, unit_b, LB2KG, FT2MTR

LB2KG = 2.204623 'LB/kg
FT2MTR = 3.28084 'FT/MTR

Select Case Trim(UCase(xunit_a))
Case "LB" ' if input quant is LB, transform xquant_a first to KG (and then -> xunit_b)
quant_a = xquant_a / LB2KG
unit_a = "KG"
Case "FT" ' if input quant is FT, transform xquant_a first to MTR (and then -> xunit_b)
quant_a = xquant_a / FT2MTR
unit_a = "MTR"
Case "K"
quant_a = xquant_a
unit_a = "KG"
Case Else
quant_a = xquant_a
unit_a = xunit_a
End Select

Select Case Trim(UCase(xunit_b))
Case "LB" ' if output quant is LB, set first transformation to xquant_a -> KG (and then -> LB)
unit_b = "KG"
Case "FT" ' if output quant is FT, set first transformation to xquant_a -> MTR (and then -> FT)
unit_b = "MTR"
Case "K"
unit_b = "KG"
Case Else
unit_b = xunit_b
End Select

If unit_a = unit_b Then
ChangeQuantUnit = quant_a
Else

'Make the transformation where both input + output units is one of (KG, K, MTR, ST)
Mat_SQL = "SELECT SAP_Materials.Material, SAP_Materials.K, SAP_Materials.Base_K, SAP_Materials.KG, SAP_Materials.Base_KG, SAP_Materials.MTR, SAP_Materials.Base_MTR, SAP_Materials.ST, SAP_Materials.Base_ST FROM SAP_Materials WHERE (((SAP_Materials.Material)=""" + xMaterial + """));"
Set M = CurrentDb.OpenRecordset(Mat_SQL, dbOpenDynaset)

If M.RecordCount > 0 Then
If M(unit_a) * M("BASE_" + unit_b) > 0 Then
ChangeQuantUnit = quant_a * M(unit_b) * M("BASE_" + unit_a) / (M(unit_a) * M("BASE_" + unit_b))
End If

Select Case Trim(UCase(xunit_b))
Case "LB" ' if output quant is LB, make the final transform from KG - > LB
ChangeQuantUnit = ChangeQuantUnit * LB2KG
Case "FT" ' if output quant is FT, make the final transform from MTR - > FT
ChangeQuantUnit = ChangeQuantUnit * FT2MTR
End Select
End If

M.Close

End If
End Function


and this what i,ve done so far..

setANSI_NULLSON
setQUOTED_IDENTIFIERON
go

ALTERFUNCTION [dbo].[ChangeQuantUnit]
(@xMat nvarchar(20),
@xQuant_A decimal(10,2),
@xUnit_A nvarchar(2),
@xUnit_B nvarchar(2)
)
RETURNSdecimal(10,2)
AS
BEGIN

DECLARE @return decimal(10,2)
declare @quant_a decimal(10,2)
declare @unit_a nvarchar(3)
declare @unit_b nvarchar(3)
declare @LB2KG decimal(10,6)
declare @FT2MTR decimal(10,6)
declare @sql varchar(250)


if @xunit_a='LB'
begin
set @Quant_a=@xQuant_a/@LB2KG
set @unit_a='KG'
end
else if @xunit_a='FT'
begin
set @Quant_a=@xQuant_a/@FT2MTR
set @unit_a='MTR'
end
else if @xunit_a='K'
begin
set @Quant_a=@xQuant_a
set @unit_a='KG'
end
else
begin
set @Quant_a=@xQuant_a
set @unit_a=@xunit_a
end

if @xunit_b='LB'
set @unit_b='KG'

else if @xunit_b='FT'
set @unit_b='MTR'

else if @xunit_b='K'
set @unit_b='KG'

else
set @unit_b=@xunit_b

if @unit_a=@unit_b
set @return=@quant_a
RETURN @return
END