MS SQL 2005 Stored Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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]Code block added - OriginalGriff[/edit]

Posted 7-Jul-11 2:17am

ivancyr

Updated 7-Jul-11 2:26am

OriginalGriff

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2011-07-07T02:40:00

Solution 1

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

SQL

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

Posted 7-Jul-11 2:40am

#realJSOP

Updated 7-Jul-11 2:41am

v2

Comments

OriginalGriff 7-Jul-11 8:50am

Sorry JSOP, I didn't realize you had responded - I was busy testing it since I always but the parameters in the wrong order in CHARINDEX...:laugh:

#realJSOP 7-Jul-11 8:55am

No worries. I honestly expected to see your answer before I got mine posted, because I had to google charindex. :)

OriginalGriff 7-Jul-11 9:16am

Don't give away all our secrets! Some of these people have never heard of google...:laugh:

Corporal Agarn 7-Jul-11 10:18am

Wish I could 5 you.

Corporal Agarn · Answer 2 · 2011-07-07T04:29:00

Look at the following and see if it helps.

SQL

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

The code works in 2008 but should also work in 2005

OriginalGriff · Answer 3 · 2011-07-07T02:48:00

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:

SQL

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

MS SQL 2005 Stored Procedure

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0