SQLTeam.com | Weblogs | Forums

SELECT CASE WHEN Syntax


#1

Hi Guys,

Once again I'm struggling with syntax issues and would appreciate some assistance. What is the correct syntax for the SELECT CASE WHEN Statement in this situation
SELECT CASE LEN(@Prefix)
WHEN 1
SET @NewKey = @Prefix + FORMAT(@CurrentValue,'000')
WHEN 2
SET @NewKey = @Prefix + FORMAT(@CurrentValue,'00')
WHEN 3
SET @NewKey = @Prefix + FORMAT(@CurrentValue,'00')
WHEN 4
SET @NewKey = @Prefix
END

I am attempting to evaluate the length of @Prefix and apply the correct format based on that length. The editor simply says incorrect syntax. Any help you can provide is appreciated.


#2

I think you are thinking of it as an IF statement: IF condition THEN do this action. Not sure what the correct description is, but maybe "conditional assignment" - like the construction (condition) ? true-value : false_value in C

SELECT @NewKey = CASE LEN(@Prefix)
    WHEN 1
        THEN @Prefix + FORMAT(@CurrentValue,'000')
    WHEN 2
        THEN @Prefix + FORMAT(@CurrentValue,'00')
...

that could be shortened to

SELECT @NewKey = @Prefix + CASE LEN(@Prefix)
    WHEN 1
        THEN FORMAT(@CurrentValue,'000')
    WHEN 2
        THEN FORMAT(@CurrentValue,'00')
...

beware that FORMAT tends to be inefficient.

I don't use FORMAT so don't know what the templates do, but if you are looking to add leading zeros something like this would be faster

SELECT LEFT(@Prefix + '000', 5) + CONVERT(varchar(20), @CurrentValue)
or
SELECT @Prefix RIGHT('000' + CONVERT(varchar(20), @CurrentValue), 5)

another thought (but I have no idea what your code is doing at this point): why are these in @Variables? Just in case you are looping and doing them one-by-one? then if so a set-based approach will be much faster (100x faster than a RBAR loop would not be a surprise)


#3

Several things here...

  1. Avoid the use the FORMAT function... MS screwed the pooch when then created this function and it has performance issues.

  2. You don't need a CASE expression to do what you're trying to do... See the following...

    DECLARE
    @Prefix INT = 55,
    @CurrentValue INT = 22,
    @NewKey VARCHAR(20);

    SELECT @NewKey = CONCAT(@Prefix, REPLICATE('0', 4 - LEN(@Prefix)), @CurrentValue);

    SELECT @NewKey;

  3. Looks like Kristen beat me to the punch while I was typing and provided the proper syntax for setting variables with a CASE expression... So... I'll just stop typing...


#4

Thanks Guys,

The posted code is only the offending fragment not the entire function. This bit of code produces the final output for my key generating function. Thanks for the heads up NewKey is actually nvarchar(4) Prefix is the same. CurrentValue is an Int that acts like a sequence. Example Key would be A876. Thanks for the heads up. As for the Case Statement that's how my original function was coded and I like to be able to see things broken out so that I can clearly see what is happening when each of the possible conditions occur. Thanks for the assist


#5

I haven't followed the question closely, so not sure what sort of Sequence you are trying to create, but a question that crops up here quite often relates to needing to handle various "parts" of a sequence, separately, and the answer that comes back is usually "store the parts of the sequence in separate fields and concatenate them when you need to display them, rather than storing it as a composite"

Dunno if that is relevant to you, but you might like to consider storing the "A" in one column and the numeric "876" in a second column.


#6

Dunno if relevant, but there is also this type of set-based solution for Alpha-Numeric sequence numbers

WITH MySeq AS
(
	SELECT 
		[ID] = ROW_NUMBER() 
		OVER (
			ORDER BY A.chr + B.chr + C.chr + D.chr
		)
		, [Result] = A.chr + B.chr + C.chr + D.chr
	FROM 
	(
		VALUES 
		-- 1st position:
		-- Allow leading Digit
--N/A		('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'),
		-- Allow leading Alpha
		('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), 
		('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), 
		('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')
	) AS A(chr)
	, (
		VALUES 
		-- 2nd position:
		('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
	) AS B(chr)
	, (
		VALUES 
		-- 3rd position:
		('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
	) AS C(chr)
	, (
		VALUES 
		-- 4th position:
		('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
	) AS D(chr)
)
SELECT	Result	-- Next value
FROM	MySeq
WHERE	ID =
	(
		SELECT	ID + 1
		FROM	MySeq
		WHERE	Result = 'A876'	-- Previous value
	)

#7

Yes the key is two parts. Prefix is the 'A' and CurrentValue is the sequence. This process is customized. The prefix "rolls over" when the sequence maxes out and the prefix portion grows in size until eventually the sequence is exhausted. The sequence information is stored in a table in my database which has the following fields. CounterName nvarchar(50),Prefix nvarchar(4), CurrentValue Integer MaxValue Integer.

Here's my completed SP if your interested

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
IF OBJECT_ID('sto_NewKey') IS NOT NULL
BEGIN
DROP PROCEDURE sto_NewKey
END
GO
CREATE PROCEDURE sto_NewKey
-- Add the parameters for the stored procedure here
@InCounterName nvarchar(50),
@InCounterPrefix nvarchar(4),
@InCurrentValue integer = 0,
@InMaxValue Integer = 999,
@NewKey nvarchar(4) = NULL

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @CounterName nvarchar(50)
DECLARE @Prefix nvarchar(4)
DECLARE @CurrentValue Integer
DECLARE @MaxValue Integer

--- Check To See If Requested Counter Record Exists. If It Exists Retrieve The Current Values
--- If It Does Not Them Insert A New Counter Record Into The Counters Table With Default Values
IF (SELECT dbo.udf_CounterExists(@InCounterName)) = 0
BEGIN
--- Call Stored Procedure Insert New Counter Record Into Counter Table
EXECUTE dbo.ins_tblCounters @InCounterName,@InCounterPrefix,@InCurrentValue,@InMaxValue
END
ELSE
BEGIN
--- Call Stored Procedure To Retrieve Values For Requested Counter Record
EXECUTE dbo.sel_tblCountersByCounterName @InCounterName, @OutCounterName = @CounterName Output, @OutPrefix = @Prefix Output, @OutCurrentValue = @CurrentValue Output, @OutMaxValue = @MaxValue Output
---Increment Counter Vakue By 1
EXECUTE dbo.upd_tblCountersIncrementCounter @InCounterName, @InCurrentValue, @OutUpdatedValue = @CurrentValue Output
IF (SELECT dbo.udf_MaxCounterValueReached(@CurrentValue,@MaxValue)) = 1
IF(dbo.udf_MaxPrefixValueReached(@Prefix)) = 1
BEGIN
SET @MaxValue = (SELECT dbo.udf_UpdateMaxValue(@MaxValue))
SET @Prefix = (SELECT dbo.udf_UpdatePrefix(@Prefix))
END
ELSE
BEGIN
SET @Prefix = (SELECT dbo.udf_UpdatePrefix(@Prefix))
END
EXECUTE dbo.upd_tblCountersResetCounter @CounterName,@Prefix,@CurrentValue,@MaxValue,@OutCurrentValue = @CurrentValue
EXECUTE dbo.upd_tblCountersIncrementCounter @InCounterName, @InCurrentValue, @OutUpdatedValue = @CurrentValue Output
END
SELECT @NewKey = CASE LEN(@Prefix)
WHEN 1
THEN @Prefix + FORMAT(@CurrentValue,'000')
WHEN 2
THEN @Prefix + FORMAT(@CurrentValue,'00')
WHEN 3
THEN @Prefix + FORMAT(@CurrentValue,'00')
WHEN 4
THEN @Prefix
END
END
GO