MS SQL 2005存储过程 [英] MS SQL 2005 Stored Procedure

问题描述

我需要搜索一个名为"Diopter"的Float变量，并寻找一个小数点.如果小数点不存在，我需要附加一个".0".这是一个代码段
开始

I need to search a Float variable named ''Diopter'' and look for a decimal point. If the decimal point does not exist I need to append a ''.0'' Here is a code snippet
Begin

    SELECT TOP 1
	WaferID,
	TrayID,
	    CASE UPPER(FixedOrPower)
		WHEN ''POWER'' THEN RTRIM(STR(Diopter)) + ''D''
		ELSE ''#'' + FixedPinNumber
	    END AS DioptorOrFixed,
	    RTRIM(REPLACE(CONVERT(VARCHAR(8), ProcessDateTime, 2),''.'','''')AS          ProcessDate,
	    RTRIM(CONVERT(VARCHAR(5), ProcessDateTime, 108)) AS ProcessTime,
	    EquipmentID AS EquipmentNumber,
	    CavityNumber,
	    PolyCNumber1,
	    PolyCNumber2,
	    PinSerialNumber,
	    HolderID,
	    OperatorEmployeeNumber AS OperatorID,
	    SetupTechEmployeeNumber + ''     .'' AS SetupTechID, 
            PinRadius1,
	    PinRadius2,
	    WaferRadius1,
	    WaferRadius2
	FROM dbo.TBL_ProcessedWaferTray
	WHERE TrayID = @TrayID
	ORDER BY TrayID, ProcessDateTime DESC
end

[edit]添加了代码块-OriginalGriff [/edit]

[edit]Code block added - OriginalGriff[/edit]

推荐答案

我不是SQL专家，但我认为这可能有效(或者至少让您使用正确的轨道):

I''m no sql expert, but I think this might work (or at least set you on the right track):

CASE UPPER(FixedOrPower)
    WHEN 'POWER' THEN CASE CHARINDEX('.', RTRIM(STR(Diopter))
                          WHEN 0 THEN RTRIM(STR(Diopter)) + '.0D'
                          ELSE RTRIM(STR(Diopter)) + 'D'
                      END
    ELSE '#' + FixedPinNumber
END AS DioptorOrFixed

查看以下内容看看是否有帮助.

Look at the following and see if it helps.

DECLARE @d AS FLOAT = 72
SELECT
    @d,
    CAST(@d AS VARCHAR(10)) AS StingVal,
    CHARINDEX('.', CAST(@d AS VARCHAR(10))) AS Position,
    CASE
        WHEN CHARINDEX('.', CAST(@d AS VARCHAR(10))) = 0
            THEN CAST(@d AS VARCHAR(10)) + '.0D'
            ELSE CAST(@d AS VARCHAR(10)) + 'D'
    END AS DesiredVal

该代码在2008年有效，但在2005年也应该有效.

The code works in 2008 but should also work in 2005

那根本没有道理.
浮点值没有小数点-当您将它们转换为字符串时，它们只会得到一个小数点.
如果您的意思是如果字符串不包含小数点，则添加".0"，那么您需要的代码类似于:

That makes no sense at all.
Float values do not have a decimal point - they only get one when you convert them to a string.
If what you mean is that if a string does not contain a decimal point then add ".0", then the code you need is similar to:

IF CHARINDEX('.', @DIOPTER) <= 0
   BEGIN
   set @DIOPTER = RTRIM(@DIOPTER) + '.0'
   END

这篇关于MS SQL 2005存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！